I am pretty new to MS Access, so I may need a little bit more direction on this.
I understand creating the seven queries for each day of the week.
So here is what I have done so far:
I created a form that has fields for the 'From' date and the 'To' date, as well as a 'run report' button.
So once the date range is stored, I need an event that will close the form and open up the report.
As far as the record source for the fields in the report, I am confused about.
I tried to map a report text box field to one of the queries, but it just came back with an error that said '?Name'
I don't know what that means.
I wanted to have one text box available in the report for each day (M - F), and then map the source of each of those fields back to each of the queries. But that still doesn't solve the 'Where' clause for the forms date range.
Here is one of my queries I used:
SELECT Count(loads.date) AS Monday
WHERE (((Loads.Date) Between #1/1/2008# And #1/31/2008#) AND ((Format([loads].[date],"dddd"))="Monday"));
You won't need to create 7 queries, you should be able to do it all in one; here's some pseudo code for you
SELECT Avg([count]) As [average_shipments]
, DatePart(weekday, date) As [day_of_week]
, Count(*) As [count]
WHERE date BETWEEN @parameter1 AND @parameter2
) As [x]
BY DatePart(weekday, date)