Count unique in a query instead of count #

  • Thread starter Thread starter BlueWolverine
  • Start date Start date
B

BlueWolverine

Hello,
MS ACCESS 2003 on XP PRO.

I have a question on group by queries.

I have a table that for testing has 14 entries.
It's the same ID number (on purpose) but it stores records for 7 days of the
week, times 2 codes on the ID.

Consequently, my table looks like this
ID DotW Code
1 M H06
1 T H06
1 W H06
1 Th H06
1 F H06
1 S H06
1 Su H06
1 M H07
1 T H07
1 W H07
1 Th H07
1 F H07
1 S H07
1 Su H07

I want to group by this thing into a table that looks like this


ID Count of DotW Count of Code
1 7 2

Right now the results of the counting query look like this.

ID Count of DotW Count of Code
1 14 14


Ideas?

Thanks.


SQL For the query (save the where clause which is long and perfect.)

SELECT q_DOW_ML.ID, Count(q_DOW_ML.Fleet_Activity) AS CountOfFleet_Activity,
Count(q_DOW_ML.DayOfWeek) AS CountOfDayOfWeek, q_DOW_ML.BinstoOther,
Count(q_CCC_VFG_VRT.t_CCCMAP_Unique.CCC) AS CountOfCCC,
Count(q_CCC_VFG_VRT.VFG) AS CountOfVFG, Count(q_CCC_VFG_VRT.VRT) AS
CountOfVRT, q_DOW_ML.[GQRS CAT], q_DOW_ML.ACTION, q_DOW_ML.[Work Element
Description], q_DOW_ML.Frequency, q_DOW_ML.[WES#], q_DOW_ML.Source,
q_DOW_ML.Active
FROM q_CCC_VFG_VRT INNER JOIN q_DOW_ML ON q_CCC_VFG_VRT.ID = q_DOW_ML.ID

<<<WHERE CLAUSE>>>
GROUP BY q_DOW_ML.ID, q_DOW_ML.BinstoOther, q_DOW_ML.[GQRS CAT],
q_DOW_ML.ACTION, q_DOW_ML.[Work Element Description], q_DOW_ML.Frequency,
q_DOW_ML.[WES#], q_DOW_ML.Source, q_DOW_ML.Active
ORDER BY q_DOW_ML.ID;


Thank you.!
 
One at a time

SELECT Count(*)
FROM (SELECT Distinct ID FROM TestTable)

SELECT Count(*)
FROM (SELECT Distinct DotW FROM TestTable)

SELECT Count(*)
FROM (SELECT Distinct Code FROM TestTable)

As subqueries in a select statement where you are returning only one record

SELECT (SELECT Count(*)
FROM (SELECT Distinct ID FROM TestTable)) as IDCount

, (SELECT Count(*)
FROM (SELECT Distinct DotW FROM TestTable)) as DotWCount

,(SELECT Count(*)
FROM (SELECT Distinct Code FROM TestTable)) as CodeCount

FROM SomeTableWithAPK
WHERE SomeTableWithAPK =1

Or use Allen Browne's ECount function
http://allenbrowne.com/ser-66.html

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
This is causing my "System resources to be exceeded"

What the heck does that mean? I've got 4GB of RAM. The DB file is around 16
MB. How am I exceeding system resources?

What can be done to fix it?
--
BlueWolverine
MSE - Mech. Eng.
Go BLUE!


John Spencer said:
One at a time

SELECT Count(*)
FROM (SELECT Distinct ID FROM TestTable)

SELECT Count(*)
FROM (SELECT Distinct DotW FROM TestTable)

SELECT Count(*)
FROM (SELECT Distinct Code FROM TestTable)

As subqueries in a select statement where you are returning only one record

SELECT (SELECT Count(*)
FROM (SELECT Distinct ID FROM TestTable)) as IDCount

, (SELECT Count(*)
FROM (SELECT Distinct DotW FROM TestTable)) as DotWCount

,(SELECT Count(*)
FROM (SELECT Distinct Code FROM TestTable)) as CodeCount

FROM SomeTableWithAPK
WHERE SomeTableWithAPK =1

Or use Allen Browne's ECount function
http://allenbrowne.com/ser-66.html

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Hello,
MS ACCESS 2003 on XP PRO.

I have a question on group by queries.

I have a table that for testing has 14 entries.
It's the same ID number (on purpose) but it stores records for 7 days of the
week, times 2 codes on the ID.

Consequently, my table looks like this
ID DotW Code
1 M H06
1 T H06
1 W H06
1 Th H06
1 F H06
1 S H06
1 Su H06
1 M H07
1 T H07
1 W H07
1 Th H07
1 F H07
1 S H07
1 Su H07

I want to group by this thing into a table that looks like this


ID Count of DotW Count of Code
1 7 2

Right now the results of the counting query look like this.

ID Count of DotW Count of Code
1 14 14


Ideas?

Thanks.


SQL For the query (save the where clause which is long and perfect.)

SELECT q_DOW_ML.ID, Count(q_DOW_ML.Fleet_Activity) AS CountOfFleet_Activity,
Count(q_DOW_ML.DayOfWeek) AS CountOfDayOfWeek, q_DOW_ML.BinstoOther,
Count(q_CCC_VFG_VRT.t_CCCMAP_Unique.CCC) AS CountOfCCC,
Count(q_CCC_VFG_VRT.VFG) AS CountOfVFG, Count(q_CCC_VFG_VRT.VRT) AS
CountOfVRT, q_DOW_ML.[GQRS CAT], q_DOW_ML.ACTION, q_DOW_ML.[Work Element
Description], q_DOW_ML.Frequency, q_DOW_ML.[WES#], q_DOW_ML.Source,
q_DOW_ML.Active
FROM q_CCC_VFG_VRT INNER JOIN q_DOW_ML ON q_CCC_VFG_VRT.ID = q_DOW_ML.ID

<<<WHERE CLAUSE>>>
GROUP BY q_DOW_ML.ID, q_DOW_ML.BinstoOther, q_DOW_ML.[GQRS CAT],
q_DOW_ML.ACTION, q_DOW_ML.[Work Element Description], q_DOW_ML.Frequency,
q_DOW_ML.[WES#], q_DOW_ML.Source, q_DOW_ML.Active
ORDER BY q_DOW_ML.ID;


Thank you.!
Click to expand...
Click to expand...
 
Which one of the three alternatives did you try?

A) Run three separate queries to get the information

B) Run three subqueries in one query that is limited to returning one
record by the where clause.

C) Using Allen Browne's ECount function.

It helps if you post what you tried. And if you have used a query, show
us the SQL view of the query.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
I'm sorry for not posting what I tried....

I tried the subquery method (#2).

I think i've run into this before when my query-chain is too
long/complicated. So I cut it out but I am very interested in knowing how to
fix this problem.


I got your post after my re-work so I don't have SQL available.

I'm sorry to be unhelpful, but if you could answer:
Is this error causable by having long chains of queries with many fields?

I think that's what's doing it.
 
Sorry, I don't know. It is hard to diagnose why a query fails for
complexity reasons. Sometimes it is obvious, other times you just have
to find another way to accomplish your goal. VERY VERY occasionally
I've resorted to building and populating a table with the needed data as
an intermediate step.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
Back
Top