Re: Summary Query based on First Occurrence

Tech-Archive recommends: Fix windows errors by optimizing your registry

Is that invoice date a text field? Why not actually use a field that
would store dates? But anyways, it will still work the same.

I used a table called invoices with your table structure.

I created a query called Query1:
SELECT
Account,
Min(InvoiceDate) AS FirstInvoiceDate
FROM
Invoices
GROUP BY
Account

I then created a second query called Query2:
SELECT
Q1.Account,
Q1.FirstInvoiceDate,
Sum(I.TotBill) AS SumFirstDate
FROM
Query1 AS Q1
INNER JOIN Invoices AS I
ON Q1.Account=I.Account
AND Q1.FirstInvoiceDate=I.InvoiceDate
GROUP BY
Q1.Account,
Q1.FirstInvoiceDate

Cheers,
Jason Lepack

On Jun 12, 2:01 pm, Dave Schoenbrun
<DaveSchoenb...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I've got a list of invoices for businesses, some which have multiple invoices
on a given day. I want to be able to return ONLY the First Day a customer
was invoiced AND the TOTAL for just that day. Here's what the data looks
like:

Account InvoiceDate TotBill
A B C Corp. 20041231 3500.00
A B C Corp. 20041231 9500.00
A B C Corp. 20050131 1500.00
A B C Corp. 20050131 2000.00
BARBERS INC. 20041431 1500.00
BARBERS INC. 20041431 12000.00
BARBERS INC. 20050131 300.00
BARBERS INC. 20050131 470.00

Ideally, my Query results would look like the following:

Account InvoiceDate TotBill
A B C Corp. 20041431 13000.00
BARBERS INC. 20041431 12150.00

Any Ideas?


.

Relevant Pages

  • Re: Three tables issue still remains
    ... In your case, as mentioned, just built a query on the main customer table ... So, if we have Customers, and Invoices tables, a left join would give us: ... REGARDLESS if the child records ...
    (microsoft.public.access.gettingstarted)
  • Re: Calculated Value will not store in Table
    ... A left join means that a query will return the "parent" records when the ... So, if we have Customers, and Invoices tables, a left join would give us: ... The tables that MUST have a child records can also clearly be seen. ...
    (microsoft.public.access.forms)
  • Re: Summary Query based on First Occurrence
    ... "Jason Lepack" wrote: ... Did you change all 4 occurences of Account? ... Invoices AS B ... You tried to execute a query that does not include the specified expression ...
    (microsoft.public.access.queries)
  • Re: Summary Query based on First Occurrence
    ... I created this query using the table structure you clarified above. ... INNER JOIN (dbo_INVX AS I ... (Many to many relationship accounts to invoices) ... AccountDirKey - relates to dbo_WACCT.AccountDirKey ...
    (microsoft.public.access.queries)
  • RE: Select Query By Month.
    ... months in order, use "ORDER BY datefield ASC," to begin your query. ... Do the ORDER BY datefield ASC above, then in Excel, select each month ... "Terry" wrote: ... >>> I am currently using the following query to determine which invoices remain ...
    (microsoft.public.access.queries)