DCount and getting num occurances over a date range

[iggy22]

Hi;

Summary: Using DCount in MS Access 97 to output the name of an event
and the total number of occurances per a user input date range.

e.g. the table is called EventData and it is a table of the types of car repair performed, and the fields are:

EventType Date Desciption other Time
======== ==== ============ ====
fix flat 17/01/06 comment.... 101pm
brakes 15/01/06 comment... 504pm
tuneup 02/01/06 comment... 725am
paint fender 03/01/06 comment.. 852am

My issue is to use a subquery for intermediate results and then another query.

I would like the user to enter the start and end dates, and then for output to be:

Output:


EventType Num Occurances
======== ============
fix flat 2
brakes 5


approach 1, use subquery called SummaryQuery:

SELECT EventData.EventType, EventData.Date
FROM EventData
WHERE (((EventData.Date) Between [Type the Start date:] And [Type the End Date:]));

This gives me output of all the events types in the date range and the time.

Then if I query this via:
SELECT SummaryQuery.EventType, DCount("*","SummaryQuery") AS TotalCount
FROM SummaryQuery
GROUP BY SummaryQuery.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));


Here I am using DCount to get the num of ocurrances.. but it is not working.
I get the error that "Access cannot find the name "type the start date" "

approach 2, Use only one query:


SELECT EventData.EventType, DCount("*","EventData") AS TotalCount
FROM EventData
GROUP BY EventData.EventType
HAVING (((EventData.EventType) Between [Type the Start date:] And [Type the End Date:]));

for this, I get no output, but the query runs.

Any comments or help with this is appreciated!!!!!!!!!!!!!!!

 

[EMP]

EventType Date Desciption other Time
======== ==== ============ ====
fix flat 17/01/06 comment.... 101pm
brakes 15/01/06 comment... 504pm
tuneup 02/01/06 comment... 725am
paint fender 03/01/06 comment.. 852am


Output:

EventType Num Occurances
======== ============
fix flat 2
brakes 5

It's difficult to see how you got the 2 and 5 from the sample data.

If what you want is the number of records, you can use the aggregate Count() function in the Totals Query:

SELECT [EventData].[EventType], Count([EventData].[EventType]) AS [Num Occurrences]
FROM EventData
WHERE [EventData].[Date] Between [Type the Start date:] And [Type the End Date:]
GROUP BY [EventData].[EventType];


^

 

[iggy22]

Much appreciated...I was stuck on using DCount.