Count or Sum Query to Eventually Make Chart Report

[dargo72]

I have one table with 4 "date" fields which activites are scheduled to take place. Eventually, I'd like to run a report by day or month that shows the number of activities scheduled to be completed on that day or month.



I am unsure how to structure the underlying query to pull all the dates and total them by the specific day or more likley month.

Help?

Thank you,

[jzwp11]

If you have many activities associated with something, that describes a one-to-many relationship which requires (by normalization rules) that each activity (and it's corresponding date) be a separate record in a table. For example, lets say that you have several activities related to an event. The tables would look something like this:

tblEvents
-pkEventID primary key, autonumber
-txtEventName

tblEventActivities
-pkEventActivityID primary key, autonumber
-fkEventID (foreign key to tblEvents)
-dteActivity (activity date)
-txtActivity

So if you have 4 dates as you mention, with the above structure, you would have 4 records in tblEventActivities

With this table structure, your query issue can be handled very easily.

[dargo72]

Splitting out the individual dates is not an option at this time.

Assuming I had only one date that I wanted to run a report by month for, how would I do that in a query?

Also, I can have 4 separate queries an then run a query of those to combine the reults, correct?

[jzwp11]

If you want your report to extract detail records by month, you would set up your query as follows

SELECT month(datefield) as period, other fields...
FROM tablename

Then in your report you would set up a group level using the period field. Now, if your data spans more than one year & you want the report to include more than one year, then you will have to use the year() function in the query above and include an additional group level in your report. If you want the data for only 1 year, you will need to filter the above query for the year again using the year() function but using it in the WHERE clause of the query.

If you want to do counts, you can do that in the period group level of the report.

If you want to do the counting in the query directly rather than in the report, you would need an aggregate query based on the query shown above

SELECT period, Count(somefield)
FROM queryabove
GROUP BY Period

[dargo72]

If you want your report to extract detail records by month, you would set up your query as follows

SELECT month(datefield) as period, other fields...
FROM tablename

Then in your report you would set up a group level using the period field. Now, if your data spans more than one year & you want the report to include more than one year, then you will have to use the year() function in the query above and include an additional group level in your report. If you want the data for only 1 year, you will need to filter the above query for the year again using the year() function but using it in the WHERE clause of the query.

If you want to do counts, you can do that in the period group level of the report.

If you want to do the counting in the query directly rather than in the report, you would need an aggregate query based on the query shown above

SELECT period, Count(somefield)
FROM queryabove
GROUP BY Period

I tried starting the query and put in this:

Select Year([Stagegate Plan date] From [numbered steps])

It is not working. Could you tell me what I'm missing.

[jzwp11]

When you run the query do you get an error or does it not return any data?

What data type is the Stagegate Plan Date field?

[dargo72]

When you run the query do you get an error or does it not return any data?

What data type is the Stagegate Plan Date field?

Error only.

That field is a date/time field

[jzwp11]

I see the problem. The closing parenthesis for the Year() function is in the wrong place

Select Year([Stagegate Plan date] From [numbered steps] )

It should be as follows:

Select Year([Stagegate Plan date]) From [numbered steps]