Solved

Total for first day

Posted on 2014-01-03
3
364 Views
Last Modified: 2014-01-04
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!
0
Comment
Question by:cansevin
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39755234
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])
0
 

Author Comment

by:cansevin
ID: 39755241
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!
0
 
LVL 47

Accepted Solution

by:
Dale Fye (Access MVP) earned 500 total points
ID: 39755398
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
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question