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.
rigby said:
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
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
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
:
On Tue, 21 Mar 2006 07:57:30 -0800, rigby
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.
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]
