How to COUNT number of times a record shows up in a MONTH

[taimysho0]

hello i have a query that prints out these results (pic attached). I want the query to display another column that shows how many times a row appears for that month. Fro example, you can see that there are 4 rows that appear in december. so the new column should show a "4" on all of the 4 rows for that month. can this be done? thanks


here is my code:

SELECT tblHHFRequest_Main.[Channel ID], tblHHFRequest_Main.Serial, tblHHFRequest_Main.[Date Requested], tblHHFRequest_Main.[Due Date], tblCustomerMaster.SIC, tblRecorderMaster_ALL1.DeviceType
FROM tblCustomerMaster INNER JOIN (tblHHFRequest_Main INNER JOIN tblRecorderMaster_ALL1 ON tblHHFRequest_Main.[Channel ID] = tblRecorderMaster_ALL1.RecorderID) ON tblCustomerMaster.CustomerID = tblHHFRequest_Main.[Channel ID]


WHERE (((tblHHFRequest_Main.[Due Date]) Between #1/1/2011# And Date()) AND ((tblCustomerMaster.SIC) Like "*ABS*") AND ((tblRecorderMaster_ALL1.DeviceType) Like "*skytel*"));

[June7]

Need a subquery.

So from your example data January will have a count of 11?

Create and save an aggregate query that creates a field by expression that extracts the month - Mon: Month([Date Requested]) and does a count of records with GROUP BY on Month([Date Requested]). The primary query will also need a field created with a calculation to extract the month. Then join the queries on the calculated month fields. The aggregate query will need the same filter criteria.

Or use DCount in your primary query to construct a field with expression. The DCount will also need the same filter criteria in its WHERE argument.

[taimysho0]

Can you help me write the Dcount? im not too familliar with how i should write it to fit my needs...thank you

[taimysho0]

june, i tried doing it the first way, creating a secondary query to combine with the primary query. Not sure if this is correct though, its coming out weird. here is the code for the second query:

SELECT tblHHFRequest_Main.[Channel ID], Count(Month([Date Requested])) AS Mon
FROM tblCustomerMaster INNER JOIN (tblHHFRequest_Main INNER JOIN tblRecorderMaster_ALL1 ON tblHHFRequest_Main.[Channel ID] = tblRecorderMaster_ALL1.RecorderID) ON tblCustomerMaster.CustomerID = tblHHFRequest_Main.[Channel ID]
GROUP BY tblHHFRequest_Main.[Channel ID];

[June7]

This might work and hopefully the full criteria won't have to be used as I had thought.

CountMon: DCount("[Channel ID]", "name of the query shown in post", "Month([Date Requested])=" & Month([Date Requested]))

[taimysho0]

"name of the query shown in post" - would this be the name of the query i am creating all of this in? or could it be the name of the container that the "channel id" is in? If its referring to the container of channel id, its actually in a table, so then do i put the name of the table instead? also, i ran the query but its close but the number is not correct.

[taimysho0]

and just making usre, when using the dcount, i dont have to use the aggregate queries rightr? i just put this straight into the primary query?

[June7]

I intended it to be the query you showed in post, so yes it is the query you are creating all of this in. Yes, no aggregate query, the DCount does the aggregating.

What do you mean by 'container'?

I notice now that the data is multi-year. If the filtered data could have months from several years, probably need to considered year in the count. So how far off is the number? How large is dataset? Do you want to provide the project for analysis?

[taimysho0]

the data goes back 13 months. so beginning of jan last year. small dataset, only about 900 rows.

as far as the query goes when it runs, it looks like it just keeps counting non stop until it errors out and freezes. however, the column for the Dcount IS grouping the rows correctly, for example all rows in December show the same number in the dcount column. However the number is significantly wrong, it displays "92" on all 4 rows in december so im not exactly sure what its counting..

[June7]

I just did a test and confirmed that the DLookup will have to include all the filter criteria that the query uses as well as the month.

Why 13 months? Actually, with Date() as the to value it will be 14 months now. So there could be duplicated months and do you want the count by year as well? Do same for year as I show with month and include that in the DCount filter criteria.

[taimysho0]

actually, i have it so it doesnt crash now, but still same prob with the wrong numbers, but its grouped properly

[taimysho0]

ok, gotcha. i will add the year. how would i add the criteria in the dcount? i think the number that is displayed is the number of rows for that month WITHOUT the criteria

[taimysho0]

ok so just making sure for the criteria of the dcount, should be like this: Year([Date Requested])="Year[Date Requested]" ?

and yes to confirm, it should be 14 months not 13, sorry

[taimysho0]

ok so ill add a pic for visual reference

[June7]

Picture not really helpful. Full DCount something like:

CountMon: DCount("[Channel ID]", "name of the query shown in post", "Month([Date Requested])=" & Month([Date Requested]) & " AND Year([DateRequested])=" & Year([DateRequested]) & " [Due Date] Between #1/1/2011# And Date() AND tblCustomerMaster.SIC Like '*ABS*' AND tblRecorderMaster_ALL1.DeviceType Like '*skytel*')

Rats! My earlier test did not include a query with joined tables. I just did a test of that and it won't work. Create the query without the DCount then do another query that uses the first as data source and calculates with DCount.

Can't get away from it, multiple queries required.