I have a table that shows allocated services based on a start and end date.
I want a query to show a row for each month that the service is going to be provided or not.
The query also has another table with the start and end dates of every month for the next 10 years.
The problem is that when a service has a start/end date in the middle of a month, that month won't be captured in this query.
Here's that I tried but this is not working:
Between DateSerial(Year[tblservice.startdate]), Month([tblservice.startdate]), 1) and DateSerial(Year[tblservice.enddate]), Month([tblservice.enddate]), 1)
If you want the service to show in all months the service is provided (not just the starting month say) then I think you need an OR ie.
Between DateSerial(Year[tblservice.startdate]), Month([tblservice.startdate]), 1) OR DateSerial(Year[tblservice.enddate]), Month([tblservice.enddate]), 1)
Dose that help?
If it was me, based on the given information, I would only be comparing the Year() and Month() of each start/end date with the Year/Month in question (ie I would not have a table of Month Start and End Dates, just years and months!)
If fact I probable would have a Year table and a Month tabel (ie one column of 1 to 12) and generate the Month/Year tabe using a query with a Cartesian join (ie. SELECT tblYear.*, tblMonth.* FROM tblYear, tblMonth)
I'm a little new to this so your method is a little unclear to me.
If I have a service that is allocated from 7/5/11- 6/27/12, I would want to see 12 records in my query for that client [7/1/11-7/31/11, 8/1/11-8/31/11...)
So how would the cartesian join help me do that?
There are no doubt other ways of doing this but this seem to work.
Create a table tblYear with a field named ThisYear (for rexample) and enter the years you want to list services for.
Create a table tblMonth with a field named ThisMonth (or similar)
Create a stored query called qryYearMonth with an sql =
SELECT tblYear.ThisYear, tblMonth.ThisMonth FROM tblMonth, tblYear ORDER BY tblYear.ThisYear, tblMonth.ThisMonth;
Then using this qery and your 'Service' table create a query like this
SELECT qryYearMonth.ThisYear, qryYearMonth.ThisMonth, tblService.StartDate, tblService.EndDate
FROM tblService, qryYearMonth
WHERE (((Year([StartDate]))=[qryYearMonth].[ThisYear]) AND ((Month([StartDate]))<=[ThisMonth]))
OR (((Year([EndDate]))=[qryYearMonth].[ThisYear]) AND ((Month([EndDate]))>=[ThisMonth]))
ORDER BY qryYearMonth.ThisYear, qryYearMonth.ThisMonth;
This will result in a line for each month between the the start month and the end month inclusive.
If anyone has any less complex/more conventional solutions then I would be very interested.