Query to Count records by Month

[AccessHunter]

Hi,

Please treat this as urgent.
I have a table that has the fields
AdmitNum, AdmitSeq, EntryDate, DischargeDate and values
HS01, 01, 11/30/2004, 03/07/2005.
I want to be able to count HS01 for the each of the months Nov 04, Dec 04, Jan 05, Feb 05 and Mar 05 since that person got admitted in Nov 04 and was in care till Mar 05. It will be great if this can be done thru Access Queries, but if that is complicated VBA code is the only option, I guess.

Please help.

Thanks

[FishVal]

What does it mean "... count HS01 for the each of the months ..." ?
Where do those multiple HS01's appear and what makes them to belong to a certain month?

Kind regards,
Fish.

[DonRayner]

I'm not entirely sure of what you're looking for, but it looks like you want to know the entire number of full or partial months that the person was in care. If so, use the DateDiff function to count the difference in months between admission and discharge. Then add one to the result.

DateDiff("m",[EntryDate],[DischargeDate])+1

Don

[AccessHunter]

I'm not entirely sure of what you're looking for, but it looks like you want to know the entire number of full or partial months that the person was in care. If so, use the DateDiff function to count the difference in months between admission and discharge. Then add one to the result.

DateDiff("m",[EntryDate],[DischargeDate])+1

Don

Sorry if I didn't explain properly. I am trying to get a count of how many patients (HS01, HS02...etc) were in each month from 2004 till 2006. In my sample data above, HS01 was admitted on 11/30/2004 and discharged on 03/07/205, which means that person was in the system for the months of Nov 04, Dec 04, Jan 05, Feb 05 and Mar 05. So the counts for Nov 04, Dec 04, Jan 05, Feb 05 and Mar 05 will go up by 1. This is the way every record should be processed and grouped. Hope I explained properly this time.

Thanks

[FishVal]

Sorry if I didn't explain properly. I am trying to get a count of how many patients (HS01, HS02...etc) were in each month from 2004 till 2006. In my sample data above, HS01 was admitted on 11/30/2004 and discharged on 03/07/205, which means that person was in the system for the months of Nov 04, Dec 04, Jan 05, Feb 05 and Mar 05. So the counts for Nov 04, Dec 04, Jan 05, Feb 05 and Mar 05 will go up by 1. This is the way every record should be processed and grouped. Hope I explained properly this time.

Thanks

Ok.

There is an opportunity to design it in SQL only.
It could be something like described here though much simpler.

• It requires two additional tables:
  • One with list of months (1-12).
  • Second with list of years. The list could be created manually, cached from available years in your table or even retrieved from your table dynamically (thus avoiding this second table at all).
• Cartesian join of these two datasets give a full list of months in available year range.
• This Cartesian join is being joined with your table on year/month combination falling into a given date range from your table record.
• Final query aggregates thus obtained dataset to give count of records for certain month/year combination.

Regards,
Fish.