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!
cansevinAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Dev-Soln LLCCommented:
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])
cansevinAuthor Commented:
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!
Dale FyeOwner, Dev-Soln LLCCommented:
In that case, you need to start out by identifying the first day for each client.

SELECT CustomerID, Min(InvoiceDate) as FirstDay
FROM yourTable
GROUP BY CustomerID

Then, you would make that a subquery and join it back to your table, something like:

Select CustomerName
         , Month([InvoiceDate]) as Mon
         , SUM([InvoiceAmt]) as Total
         , Count([InvoiceAmt]) as InvCount
         , Avg([InvoiceAmt]) as InvAvg
FROM yourTable
INNER JOIN (
SELECT CustomerID, Min(InvoiceDate) as FirstDay
FROM yourTable
GROUP BY CustomerID
) as CustFirstDay
ON yourTable.CustomerID = CustFirstDay.CustomerID
AND yourTable.InvoiceDate = CustFirstDay.FirstDay

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.