Counting Date entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

rigby - 21 Mar 2006 16:57 GMT

Hi
I desperately need to know how to count how many records have an entry in
this particular date field. It gets complicated because I need to make sure
it only shows records where 2 sets of criteria's are met first. I have an
'Area' field with about 4 options, then I want to see for each of those
options, how many records have entries in a date field called 'ISPS' where
another field, 'Status', must be = "Active".

This is what I have so far:
SELECT TMain.Area, TMain.Status, TMain.ISPS
FROM TMain
WHERE (((TMain.Area)<>" ") AND ((TMain.Status)="Active"))
ORDER BY TMain.Area;

This works fine, but now I need to have it automatically add up how many
records have an entry under the 'ISPS' column, instead of showing me each
entry's date.

Has anyone got an idea of how I can do this? Please advise.

Kind Regards
Rigby

Reply to this Message

John Vinson - 21 Mar 2006 18:05 GMT

>Hi
>I desperately need to know how to count how many records have an entry in
[quoted text clipped - 15 lines]
>
>Has anyone got an idea of how I can do this? Please advise.

You need a TOTALS query. Use a criterion on ISPS of

IS NOT NULL

The same will probably be necessary for AREA as well - Access does not
store trailing blanks, so if there's nothing in AREA that field will
be NULL, not equal to " ".

Try

SELECT Area, Count(*) AS Howmany
FROM TMain
WHERE Area IS NOT NULL
And Status = "Active"
And ISPS IS NOT NULL
GROUP BY Area;

John W. Vinson[MVP]

Reply to this Message

rigby - 22 Mar 2006 10:31 GMT

Dear John

That worked perfectly. I couldnt have asked for a better SQL. Thank you
everyone else too for your time and help. As John replied first, I tried his
first and it worked exactly how i need it too.

BUT here comes the next part. Now I need to do the same for about 12 other
fields too, yet all related the same way to the 'Status' = "Active" and the 4
different 'Areas'. In other words, I have been asked to show a
spreadsheet-like view of these various fields as columns where they appear in
the 4 different Areas and they are all of the Active Status. Does that make
sense?

Will it be easy enough to combine all 13 of these queries into one? Again,
thank you all so much. you have been a big help.

Kind Regards

Rigby

> >Hi
> >I desperately need to know how to count how many records have an entry in
[quoted text clipped - 34 lines]
>
> John W. Vinson[MVP]

Reply to this Message

John Spencer - 22 Mar 2006 13:44 GMT

You probably don't need to have 13 separate queries. If you count fields,
all fields that are null will not get counted. Any field with a value will
be counted. So you could use something like the query below to count fields
that have a value by field.

John Vinson's method of Count(*) is a special case, it counts the rows
returned by the query. It is also supposed to be faster then counting
values in a field.

Example (replace generic field names with your field names)
SELECT TMain.Area, TMain.Status,
Count(TMain.ISPS) as CountISPS,
Count([Another Field]) as CountAnother,
Count([Field xx]) as CountFieldXX
FROM TMain
WHERE TMain.Area is Not Null AND TMain.Status="Active"
GROUP BY Area, Status
ORDER BY TMain.Area;

That could return something like the following (colons represent column
breaks)
Area51 : Active : 12 : 10 : 23
DWX : Active : 92 : 12 : 17

> Dear John
>
[quoted text clipped - 63 lines]
>>
>> John W. Vinson[MVP]

Reply to this Message

rigby - 29 Mar 2006 11:10 GMT

Dear John Spencer

Your suggestion worked even better. It is producing the EXACT result I am
looking for, BUT (again) it won't allow me to produce result for more than 3
fields. I get and error like so:
"The SELECT statement includes a reserved word or an argument that is
misspelled or missing, or the punctuation is incorrect."
and it highlights the 'Count' word for my 4th field. here is my SQL so far:

SELECT TMain.Area, TMain.Status,
Count(TMain.ISPS) as ISPS,
Count(TMain.SST) as SST,
Count(TMain.[Level 1 Anti-Terrorism]) as Level1
Count(TMain.[Non-Lethal Tactics]) as NonL
FROM TMain
WHERE TMain.Area is Not Null AND TMain.Status="Active"
GROUP BY Area, Status
ORDER BY TMain.Area;

'Count' for NonL gets highlighted and wont work, but without the 4th field,
it works fine. Is there a limit to how many fields I can use in this kind of
statement? I have 12 I need to produce answers for altogether.

Thank you for all your help.

Rigby

> You probably don't need to have 13 separate queries. If you count fields,
> all fields that are null will not get counted. Any field with a value will
[quoted text clipped - 87 lines]
> >>
> >> John W. Vinson[MVP]

Reply to this Message

John Spencer - 29 Mar 2006 13:50 GMT

You are missing a comma after "as Level1" in your post. I assume that is
the problem and is not a typo in your posting.

A little trick you can use is to format the query slightly different. The
problem is that Access won't keep this formatting, but it does help in
construction sometimes. That is to put the comma before the field and on a
new line. See my example below. I find it easy to scan down and see that
all the required commas are there.

SELECT TMain.Area
, TMain.Status
, Count(TMain.ISPS) as ISPS
, Count(TMain.SST) as SST
, Count(TMain.[Level 1 Anti-Terrorism]) as Level1
, Count(TMain.[Non-Lethal Tactics]) as NonL
FROM TMain
WHERE TMain.Area is Not Null AND TMain.Status="Active"
GROUP BY Area, Status
ORDER BY TMain.Area;

Access (bless its little heart) rearranges this into:
SELECT TMain.Area, TMain.Status, Count(TMain.ISPS) as ISPS, Count(TMain.SST)
as SST, Count(TMain.[Level 1 Anti-Terrorism]) as Level1,
Count(TMain.[Non-Lethal Tactics]) as NonL
FROM ...
which is a lot harder to check.

> Dear John Spencer
>
[quoted text clipped - 130 lines]
>> >>
>> >> John W. Vinson[MVP]

Reply to this Message

rigby - 29 Mar 2006 14:22 GMT

John Spencer

You are a champion. It has done exactly as I needed. Thank you so much for
all of your help.

Kind regards

Rigby

> You are missing a comma after "as Level1" in your post. I assume that is
> the problem and is not a typo in your posting.
[quoted text clipped - 157 lines]
> >> >>
> >> >> John W. Vinson[MVP]

Reply to this Message

John Spencer - 29 Mar 2006 15:14 GMT

Great, I'm glad that worked for you.

> John Spencer
>
[quoted text clipped - 182 lines]
>> >> >>
>> >> >> John W. Vinson[MVP]

Reply to this Message

John Spencer - 21 Mar 2006 18:20 GMT

SELECT TMain.Area, TMain.Status, Count(TMain.ISPS) as CountISPS
FROM TMain
WHERE (((TMain.Area)<>" ") AND ((TMain.Status)="Active"))
GROUP BY Area, Status
ORDER BY TMain.Area;

> Hi
> I desperately need to know how to count how many records have an entry in
[quoted text clipped - 19 lines]
> Kind Regards
> Rigby

Reply to this Message

KARL DEWEY - 21 Mar 2006 19:56 GMT

SELECT TMain.Area, Count(TMain.ISPS) AS CountOfISPS
FROM TMain
WHERE (((TMain.Status)="Active"))
GROUP BY TMain.Area;

> Hi
> I desperately need to know how to count how many records have an entry in
[quoted text clipped - 18 lines]
> Kind Regards
> Rigby

Reply to this Message