Sales Report by Day-of-Week

Can you be more specific? Where do I add this new field? How do I group it? What do I write to show me the sum of sales on each day? Anything else I need to do to make it work?

So long as you two dates are never more than a week apart, you need a small table tblWeekdays, with the integer field Weekday containing the values 0 thru 6.

In you table Sales in which you have recorded sales by day, assuming the table looks something like:

tblSales
====
SalesID - pk
SalesDate - datetime
SalesPerson - fk from tblSalesPersons
CustomerID - fk from tblCustomers
SalesItem - fk - from tbl
Amount - Currency

Now you create a query:
PARAMETERS [EnterStartDate] DateTime;
SELECT DateAdd("d", b.WeekDay, a.SalesDate) AS DateInWeek, Sum(a.Amount) FROM tblSales a, tblWeekDays b WHERE DateAdd("d", b.WeekDay, a.SalesDate) >= [EnterStartDate] GROUP BY a.SalesDate;

This should generate a recordset of all the sales made from the date entered to one week later.  

Because you are using just the start date, you can ignore the first part of the first sentence: "So long as you two dates are never more than a week apart".

Thanks, but I need to generate the report over a course of months so I can see how each day of the week stacks up (as I mentioned in the initial question - I gave example dates over three and a half months).

Any ideas?

ok, let me try that...

I took 'for each day of the week' to mean you were only interested in a week's worth of data if you need it for up to a max of x number of days, the tblWeekDays now becomes tblDays containing the interger values 0 thru to x-1. Change the query to:

PARAMETERS [EnterStartDate] DateTime, [EnterEndDate] DateTime;
SELECT DateAdd("d", b.WeekDay, a.SalesDate) AS DateOfSale, Sum(a.Amount) AS DailySum
FROM tblSales a, tblWeekDays b
WHERE DateAdd("d", b.WeekDay, a.SalesDate)
BETWEEN [EnterStartDate] AND [EnterEndDate]
GROUP BY a.SalesDate;

Sorry, lets just call it tblNums, containing the integer field Num:

PARAMETERS [EnterStartDate] DateTime, [EnterEndDate] DateTime;
SELECT DateAdd("d", b.Num, a.SalesDate) AS DateOfSale, Sum(a.Amount) AS DailySum
FROM tblSales a, tblNums b
WHERE DateAdd("d", b.Num, a.SalesDate)
BETWEEN [EnterStartDate] AND [EnterEndDate]
GROUP BY  DateAdd("d", b.Num, a.SalesDate);

hmmm...I tried it and it's giving me a strange result. Here's a partial result:

  DateOfSale                          DailySum
1/7/2009 12:00:00 PM       17
1/8/2009 12:00:00 PM       17
1/8/2009 12:40:00 PM       5
1/9/2009 12:00:00 PM       17
1/9/2009 12:40:00 PM       5
1/10/2009 12:00:00 PM    17

Sorry, I must be doing something wrong. Here is a partial result again.

DateOfSale    DailySum
5/21/2009    147
5/22/2009    151
5/23/2009    151
5/24/2009    151
5/25/2009    151
5/26/2009    118
5/27/2009    101
5/28/2009    57
5/29/2009    53


These numbers aren't accurately representing the actual sales either for some reason. Here are actual numbers for those dates:
5/21/2009    45
5/22/2009    4
5/23/2009    0
5/24/2009    0
5/25/2009    18
5/26/2009    2
5/27/2009    25
5/28/2009    1
5/29/2009    0

Can you reduce the mdb/table (use dummy data if appropriate) down to the min required to support the question and upload it here using Attach File below?  We probably only need a few hundred records.  

Before I do that, I tried this and it seems to work. It's showing me a number instead of the day of the week, but the data seems correct,

PARAMETERS [EnterStartDate] DateTime, [EnterEndDate] DateTime;
SELECT Weekday(a.TimeIn) AS DateOfSale, Sum(a.Apps) AS DailySum
FROM Apps a, tblNums b
WHERE DateValue(DateAdd("d", b.Num, a.TimeIn))
BETWEEN [EnterStartDate] AND [EnterEndDate]+1
GROUP BY  Weekday(TimeIn);

                                              

1:
2:
3:
4:
5:
6:

Now I getcha.  Change Weekday(a.TimeIn) with WeekDayName(WeekDay(a.TimeIn,True)) and add

ORDER BY WeekDay(a.TimeIn)

Like this?

PARAMETERS [EnterStartDate] DateTime, [EnterEndDate] DateTime;
SELECT WeekdayName(WeekDay(a.TimeIn)) AS DateOfSale, Sum(a.Apps) AS DailySum
FROM Apps a, tblNums b
WHERE DateValue(DateAdd("d", b.Num, a.TimeIn))
BETWEEN [EnterStartDate] AND [EnterEndDate]+1
GROUP BY  Weekday(TimeIn)
Order BY Weekday(a.TimeIn);

                                              

1:
2:
3:
4:
5:
6:
7:

I haven't gotten it to work yet, but I had to leave town on business. I'll come back to the issue when I return next week.

When I tried your code snippet, I received the following error: "You tried to execute a query that does not include the speciied expression 'Weekday(a.TimeIn)' as part of an agregate function.

The closest I've come to the actual result I'm looking for is the last snippet I posted above, but for some reason it's pulling the wrong data. The results are nowhere near accurate.