Summary Query based on First Occurrence

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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?
 
Quick correction to the Results of the Query....ABC Corp. Invoice Date would
read 20041231.
 
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
 
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.
 
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
 
Oooh, this is nifty too... the joys of learning. I've noticed in the
past that MS Access sometimes has trouble with subqueries in the FROM
clause, so this is a nice alternative.

SELECT
A.Account,
A.InvoiceDate,
Sum(A.TotBill) AS SumOfTotBill
FROM
Invoices AS A
GROUP BY
A.Account,
A.InvoiceDate
HAVING
A.InvoiceDate <= All (
SELECT
B.InvoiceDate
FROM
Invoices AS B
WHERE
B.Account = A.Account)

Cheers,
Jason Lepack
 
In our system, AccountDirKey is the primary key on the Accounts table and is
used to link to the Invoice table. I edited your statement “WHERE B.Account
= A.Account" to use AccountDirKey. When I try to run, I get the following
error message:

You tried to execute a query that does not include the specified expression
‘AccountDirKey’ as part of an aggregate function.

AccountDirKey is not one of my selected fields, so why is Access thinking it
needs to be part of the Aggregate function.
 
Access must be seeing it as a selected function. Any time you have an
issue like this, copy the SQL and paste it back so we can see the
problem.

Did you change all 4 occurences of Account? It sounds like you missed
the one in the GROUP BY clause.

Post the SQL and I'll touch it up if this doesn't work.

SELECT
A.AccountDirKey,
A.InvoiceDate,
Sum(A.TotBill) AS SumOfTotBill
FROM
Invoices AS A
GROUP BY
A.AccountDirKey,
A.InvoiceDate
HAVING
A.InvoiceDate <= All (
SELECT
B.InvoiceDate
FROM
Invoices AS B
WHERE
B.AccountDirKey = A.AccountDirKey)

Cheers,
Jason Lepack
 
Maybe I should have mentioned that there are 4 tables (at this time) as part
of the query. It could grow to as many as 12. Imagine 12 fields returning
the same data, except for the Date and Amount Fields. I will group on the
first 10, and need to do something to select and sum on only the first date
for each Account. I don't know if this info makes a difference.

I'm still getting the error message. Here's the SQL statement modified to
fit your structure. My lettering assignment is for the 1st letter of the
table purpose (dbo_WACCT becomes A).

SELECT
A.ShortTitle,
P.InvoiceDate,
Sum(I.TotBill) AS SumOfTotBill
FROM
(dbo_WTABLE AS T INNER JOIN dbo_INVX AS I ON T.TablDKey = I.InvStatus)
INNER JOIN (dbo_WACCT AS A INNER JOIN dbo_PINVX AS P ON A.AccountDirKey =
P.AccountDirkey) ON I.InvoiceDirKey = P.InvoiceDirkey
GROUP BY A.ShortTitle, P.InvoiceDate, T.TablName
HAVING (((T.TablName)="approved")) AND
P.InvoiceDate <= All (
SELECT
B.InvoiceDate
FROM
dbo_PINVX AS B
WHERE
B.AccountDirKey = A.AccountDirKey)
ORDER BY A.ShortTitle, P.InvoiceDate;

--------------------------------------------------------------------------------------
 
So, does this hydra have any more heads?

Tables:

dbo_WTABLE: (Invoice Statuses)
TablDKey - relates to dbo_INVX.InvStatus - PK?
TablName

dbo_INVX: (Invoices)
InvStatus - relates to dbo_WTABLE.TablDKey - FK
InvoiceDirKey - relates to dbo_PINVX.InvoiceDirKey - PK?
TotBill

dbo_WACCT: (Accounts)
AccountDirKEy - relates to dbo_PINVX.AccountDirKey - PK
ShortTitle

dbo_PINVX: (Many to many relationship accounts to invoices)
InvoiceDate
AccountDirKey - relates to dbo_WACCT.AccountDirKey
InvoiceDirKey - relates to dbo_INVX.InvoiceDirKey

Please confirm that this is correct, I'm putting together a solution.
Also what is the error that you are getting at this point?
 
I guess that the field that is used in the All statement needs to be
in the group by clause as well.

SELECT
A.ShortTitle,
P.InvoiceDate,
Sum(I.TotBill) AS SumOfTotBill
FROM
dbo_WTABLE AS T
INNER JOIN (dbo_WACCT AS A
INNER JOIN (dbo_INVX AS I
INNER JOIN dbo_PINVX AS P
ON I.InvoiceDirKey = P.InvoiceDirKey)
ON A.AccountDirKey = P.AccountDirKey)
ON T.TablDKey = I.InvStatus
WHERE
T.TablName="Approved"
GROUP BY
A.ShortTitle,
P.InvoiceDate,
P.AccountDirKey
HAVING
P.InvoiceDate<=All(
SELECT
InvoiceDate
FROM
dbo_PINVX AS B
WHERE P.AccountDirKey = B.AccountDirKey)

Cheers,
Jason Lepack
 
dbo_WTABLE: (Invoice Statuses)
TablDKey (PK) --> dbo_INVX.InvStatus (FK)
Field: TablName

dbo_INVX: (Invoices)
InvStatus (FK) --> dbo_WTABLE.TablDKey (PK)
InvoiceDirKey (PK) --> dbo_PINVX.InvoiceDirKey (PK)
Field: TotBill

dbo_WACCT: (Accounts)
AccountDirKey (PK) --> dbo_PINVX.AccountDirKey (FK)
Field: ShortTitle

dbo_PINVX: (Many to many relationship accounts to invoices)
AccountDirKey (FK) - relates to dbo_WACCT.AccountDirKey (PK)
InvoiceDirKey (PK) - relates to dbo_INVX.InvoiceDirKey (PK)
Field: InvoiceDate

The error is exactly the same as before: You tried to execute a query that
does not include the specified expression 'AccountDirKey' as part of an
aggregate function.
 
I created this query using the table structure you clarified above. I
used the data that you listed in your orignal post. It works... give
it a shot.

SELECT
A.ShortTitle,
P.InvoiceDate,
Sum(I.TotBill) AS SumOfTotBill
FROM
dbo_WTABLE AS T
INNER JOIN (dbo_WACCT AS A
INNER JOIN (dbo_INVX AS I
INNER JOIN dbo_PINVX AS P
ON I.InvoiceDirKey = P.InvoiceDirKey)
ON A.AccountDirKey = P.AccountDirKey)
ON T.TablDKey = I.InvStatus
WHERE
T.TablName="Approved"
GROUP BY
A.ShortTitle,
P.InvoiceDate,
P.AccountDirKey
HAVING
P.InvoiceDate<=All(
SELECT
InvoiceDate
FROM
dbo_PINVX AS B
WHERE P.AccountDirKey = B.AccountDirKey)
 
Back
Top