now i have a db for car renting company
i store for every customer two dates "begin date" & "end date" , mean when it begin to rent and when it finish the renting
now i want to make a report how many days such cust rent a car in a certain month?
for example begin date 1/4/2007 to end date 1/7/2007 OK
how many days in month 5 he rent the car ??
if i query by begin or end day by month 5 ,i will have no result
since the month 5 "May" lay between the begin & end day
did any one understand me
how to make it in a smart automatic way
or i will need a complex query and VBA to did that !! !
I'm afraid that the easiest way to do this is the hard way. In VBA, build a temp table record by record, for each date the customer rented the car. In your example, the customer's data will occupy 90 or so records, between April 1 and July 1. After you build this table, run a query on the temp table any which way you want with the confidence that it won't miss any dates, because it's querying the temp table, not the original table. Just be sure you have enough info, structure-wise, in the temp table, so you can make a meaningful JOIN to the main table in your query.