sum per day between 2 dates

[hardyd44]

Hi,

I have a query that has the fields

start date (when job started)
Finish date (when job finished)
total cost (invoice value)
No of days (date diff between start and finish dates)
earnings per day (calculated from total cost/no of days)

what i would like to achieve is a table/query that shows each day of the year with the sum of all earnings per day for that date - what i am trying to do is produce a report that will show how much we earn per week, month etc


but as a job can run from 1 to 100 or more days and overlap each other - i thought if i give each day a calculated value - this would give me my result

any thoughts

 

[AlanJ10]

SELECT Sum([earnings per day])
FROM [your query name]
WHERE #yourdate# between [start date] and [finish date];

This should get you a value for a given day, but it will need a bit more work for weeks and months. I'll post back if I come up with anything new.

Edit: Sorry, I haven't really read what you asked for. That will give the daily earnings for a given day. There's probably a way to tweak it to get a list of every day, but I'm not sure how off the top of my head.

 

[hardyd44]

that works - but still need to tweak

Allan

Thank you that query did exactly what I need for 1 day - now just need to work out how to generate a table for every day, maybe a vb loop ? - will have a search round, but if you come up with something please let me know

cheers

Dean

 

[Rich]

You don't need a table at all, let alone one for each day of the week. Use a Totals query and group by the day, week month etc

 

[hardyd44]

Rich

sorry can you please explain ?

sort of lost me there - know what a totals is etc but the date value is a expression in the query

ta

Dean