Counting Unique Data

[Andrew_Wilson]

Hello, Sorry for the newbie question here. I am trying to create a query
that will count unique data from a field based on a date. Here is an example
of my table data.

Color: Date:
Red 6/18/06
Red 6/20/06
Red 6/25/06
Blue 6/28/06
Blue 7/15/06
Blue 9/20/06

In my query I have put in Criteria for date parameters that ask for the
month and year. Then what I would like to see is once I enter the month and
year, for example 6/2006 it will let me know that there are 3 reds and 1 blue
for that month. What would be really nice is to have this result in thier
own field. For example: Field:countRed = 3 Field countBlue=1. That way I
could pull that data in another query or a report.

This is actually just an example of that data Im using. Im actually creating
a Baby Delivery Logbook, just wanted to leave out the details. But for each
delivery, you would have different anesthesia types, different delivery
types, etc. I want to be able to count the delivery types based on a
specific month/year. Then also be able to use that data in like a summary
report for the month.

Sorry if Im confusing anyone, or if I didnt give enough info and thanks for
any help in advance.

Andrew

 

[David F Cox]

These sorts of questions are answered in GROUP BY by queries. You turn and
ordinary SELECT query in the QBE grid into a GROUP BY query by clicking on
the M on its side Greek symbol on the toolbar. This gives a new line in the
grid where you can specify functions like SUM, AVG, MAX, MIN and others.
Play with it. It is a very powerful tool.

 

[Andrew_Wilson]

Thanks, but I have tried that and probably wouldnt be seeking all of you
experts for help if it were that simple. I have already tried count, and
sum. The problem is if I use count with the dates it just counts the entries
in lets say, the color color. So there could be 4 blues and 4 reds, but it
returns 8. I would like to have it say there are 4 blues and 4 reds.

 

[David F Cox]

something on the lines of:-

Blues: iif([color]="blue",1,0) Reds:iif([color]="red",1,0)

Sum those columns

 

[Ken Sheridan]

Andrew:

You need to group the query on the relevant column; the COUNT function will
then return the number of rows for each value of the grouped column:

SELECT [DeliveryType], COUNT(*) As [DeliveryTypeCount]
FROM [DeliveryLog]
WHERE YEAR([DeliveryDate]) = [Enter Year:]
AND MONTH([DeliveryDate]) = [Enter Month As Number:]
GROUP BY [DeliveryType];

BTW don't use Date as a column name; it might easily be confused with the
built in Date function. Use something more descriptive as above. The above
query would prompt for the parameter values for the year and month and return
a result set of distinct delivery types within the month and the number of
each.

To get the counts per delivery type as column headings transform the query
into a Crosstab:

TRANSFORM COUNT(*) As [DeliveryTypeCount]
SELECT YEAR([DeliveryDate]) As [DeliveryYear],
MONTH([DeliveryDate]) AS [DeliveryMonth]
FROM [DeliveryLog]
WHERE YEAR([DeliveryDate]) = [Enter Year:]
AND MONTH([DeliveryDate]) = [Enter Month As Number:]
GROUP BY YEAR([DeliveryDate]),MONTH([DeliveryDate])
PIVOT [DeliveryType] IN ("Type1", "Type2", "Type3");

The value list for the IN clause ("Type1", "Type2", "Type3" in the above
example) should contain the *exact* values used for each delivery type and be
in whatever order you want the columns to be returned in. When using a
crosstab query as a report's RecordSource its important to include an IN
clause as other wise any delivery types not represented in the month in
question won't be returned as an empty column by the query. Consequently the
controls in the report bound to those columns would return an error.

Ken Sheridan
Stafford, England