asked on

Total for first day

I have an access query that includes a row for every invoice built. Among other columns, it has an CustomerID (unique to every customer), DateInvoiceBuilt (the exact date the invoice was built), and Total Invoice Amount.

I would like to have a query that will give me the average each customer was billed for on the first day an invoice was made.

For example, if JohnDoe has three invoices: two created on Jan 1st for $200 each and one on January 10th for $500. The query would give me the average of the two created on January 1st ... which would be $200. The January 10th wasn't created on the first day, so it would not be included in the data.

Any ideas? Thanks!

Dale Fye

so, by "for the first day", do you mean the first day of the year, or the first day of each month, so you would have:

Name Mon Sum Count Avg
John Doe Jan 400 2 200
John Doe Feb 800 3 266.67

If this is correct, you might try something like:

Select CustomerName
, Month([InvoiceDate]) as Mon
, SUM([InvoiceAmt]) as Total
, Count([InvoiceAmt]) as InvCount
, Avg([InvoiceAmt]) as InvAvg
FROM yourTable
WHERE Year([InvoiceDate]) = 2013
AND Day([InvoiceDate]) = 1
GROUP BY CustomerName, Month([InvoiceDate])

cansevin

ASKER

Sorry for the confusion... the "first day" is meant the day the first invoice was made for that particular clients. So every client would have their own "first day". If a client has 20 different invoices, with the first to being March 3rd, then it would average the two on March 3rd.

Hope that clears some fog. Thanks!

ASKER CERTIFIED SOLUTION

Dale Fye

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial