SQL Problem - Distinct, Join, Union ...???

[rt52]

Given the following:
Table
Date Name Type
01/04 John A
01/04 Ann B
01/05 John B
01/05 John B
01/06 Ann A

I use the Distinct function to get a summary report:
"Select Distinct name, type, count(type) from table GROUP By name, type;"

Name Count Type
Ann
1 A
1 B
John
1 A
2 B

I want to place a line in the header of the report which
shows the number of days and the average per day:
"Select Distinct name, type, count(type), date from table Group By name, type, date;

Number of days 3
Average per day 1.66

My problem is that when I select the date field to use
the datediff function, I have to use the date field in an
aggregate function which doesn't produce the desired result due to the date:

Ann 1 B
1 A
John 1 A
1 B
1 B
Would I use a join or union or ???

 

[lupins46]

The 'Distinct' is having no effect in your query because it is a grouped query.

I'm not totally clear what you want the average of.
But if it's just the number of records divided by the number of days between the earliest and latest date then you could use aggregate functions to get it.

Dcount ("*", "mytable")/(dmax("[Date]","Mytable")-dmin(("[Date]","Mytable"))

 

[rt52]

Thank you for your response.
Sorry I didn't ask the question more clearly.
The aggregate function DCount gave me what I needed by allowing me to not place the date field in the Select statement. I haven't used those in awhile.

 

[rt52]

Hi, again.
I find on further inspection that the aggregate function isn't going to work for me since I have to use different date ranges in the query, not necessarily all dates. I would then use a date BETWEEN statement but I don't believe I can use that in an aggregate function.

 

[JonFer]

Try your query with the date criteria again but in the query design, change the "Total" value to "Where" under the date field. That will prevent it from showing mutiple rows based on the date.


John

http://www.interautomateconsulting.com

 

[PHV]

I don't believe I can use that in an aggregate function
In the VBA help file take a look at the 3rd argument of DCount, DSum, DMin, DMax, DAvg, ... functions.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244