Re: Summary Query based on First Occurrence

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Fine then :p Try this:

SELECT
Q1.Account,
Q1.FirstInvoiceDate,
Sum(I.TotBill) AS SumFirstDate
FROM
(
SELECT
Account,
Min(InvoiceDate) AS FirstInvoiceDate
FROM
Invoices
GROUP BY
Account) 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:43 pm, Dave Schoenbrun
<DaveSchoenb...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
Absolutely wonderful solution, with one problem I failed to mention....

The query has to be self contained. I cannot create objects on our LIVE
database. I can create the query in a DEVELOPMENT database and copy the
resultant SQL statement into a new query in LIVE, run it and copy the
results, but I can't save anything (such as a 2nd query) in LIVE.

Sorry for not mentioning that upfront, but I appreciate the response. It's
what I would do if I had the capabilities.



"Jason Lepack" wrote:
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?- Hide quoted text -

- Show quoted text -


.

Relevant Pages

  • Re: Summary Query based on First Occurrence
    ... Jason Lepack ... INNER JOIN Invoices AS I ... I can create the query in a DEVELOPMENT database and copy the ...
    (microsoft.public.access.queries)
  • Re: Conditional Count
    ... ElectronicVolumeMonthly WHERE ElectronicVolumeMonthly.PnL> 0) AS Winners ... "Jason Lepack" wrote: ... PnL and risk associated wtih each ... In my query, I am grouping by occ_acro and already have built in the ...
    (microsoft.public.access.queries)
  • Re: Problems with Duplicate info.
    ... "Jason Lepack" wrote: ... Enter this query in using the SQL View of the Query Designer. ... GROUP BY Container) AS C ... driversLicenseNumber - Text - PK ...
    (microsoft.public.access.queries)
  • Re: Problems with Duplicate info.
    ... SELECT ID FROM tblCMI AS B ... GROUP BY Container) AS C ... "Jason Lepack" wrote: ... Enter this query in using the SQL View of the Query Designer. ...
    (microsoft.public.access.queries)
  • 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)