Counting Number of Days That Contain Records (1 Viewer)

[Erik]

I've been searching the Queries Forum for help on this issue. I have a database that records loading records for a Taconite facility. Some days contain many records and on other days there are no records because of no loading activity. I create reports that group these loading records by each day, but I really need a quick and dirty report that just shows me the number of days in any month in which we had loading activity (records present). I have not figured out a way to create a query which counts the number of days in a month which contain records. I'm hope I'm explaining this well enough for someone to help me understand what to do.

Thanks ... Erik

 

[Dennisk]

you would need to group by on Year, group By On Month then count the number of records returned.

 

[Erik]

Thanks for the help, but are you suggesting that I group By On Month in the query I build? I have reports that group by day, but then I have to manually count the days in a month. I'm not quite understanding if this can be done in the query. I have a test query with the Totals tool in view which allows me to Group By, but I'm not sure if that is what I need to do. Can you guide me a little further?

 

[neileg]

Assuming your table is called tblMyTable, and the date field in that table is called LoadDate, the following will do the trick

SELECT DISTINCT Month([LoadDate]) & "," & Year([loadDate]) AS MthYr, Count(tblMyTable.LoadDate) AS CountOfLoadDate
FROM tblMyTable
GROUP BY Month([LoadDate]) & "," & Year([LoadDate]);

 

[Erik]

I did create a query modifying the code you gave me, but what it returns is the number of records each month. I am looking for the number of days in each month that have loading records. For example, there would be 30 or 31 days in each month, but we might have loaded only 20 days. That is the number I am looking for. Right now, I manually count those days from a report I get grouping by days. Can you guys still help? I appreciate your help so far, and have learned a bit more already. Thanks ... Erik

 

[neileg]

The query I gave you does count the days in the month that have loading data. You have proably missed out the DISTINCT part which limits the records to one per day.