I have a table with bookings in it.
Each booking has a Date Out, a Date In and the name of the person involved.
I want to produce a report of the number of days each person has a vehicle booked per month. Where I'm running into a problem where a booking crosses from one month to the next.
For example,
John Smith has bookings:
from April 18th to April 25th
from April 25th to May 2nd
from May 2nd to May 9th
The following query
Each booking has a Date Out, a Date In and the name of the person involved.
I want to produce a report of the number of days each person has a vehicle booked per month. Where I'm running into a problem where a booking crosses from one month to the next.
For example,
John Smith has bookings:
from April 18th to April 25th
from April 25th to May 2nd
from May 2nd to May 9th
The following query
Code:
SELECT tblvehiclelog.Driver, tblvehiclelog.[Date Out], tblvehiclelog.[Date In], DateDiff("d",[date out],[date in]) AS DaysBooked
FROM tblvehiclelog
GROUP BY tblvehiclelog.Driver, tblvehiclelog.[Date Out], tblvehiclelog.[Date In], DateDiff("d",[date out],[date in])
HAVING tblvehiclelog.[Date Out]>DateAdd("yyyy",-1,Date()) AND tblvehiclelog.[Date In]<=Now()
[Code]
Gives me three records for John, each of seven days.
However, summarizing by month on 'Date In' gives me:
April - 7 days
May - 14 days
summarizing by month on 'Date Out' gives me:
April - 14 days
May - 7 days
what I want is
April - 12 days
May - 9 days
For the life of me, I can't think of a way to write this as a query?
Any suggestions gratefully received, as always.