Re: Summary Query based on First Occurrence

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;

--------------------------------------------------------------------------------------
"Jason Lepack" wrote:

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

On Jun 13, 10:38 am, Dave Schoenbrun
<DaveSchoenb...@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
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.



"Jason Lepack" wrote:
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

On Jun 12, 3:32 pm, Jason Lepack <jlep...@xxxxxxxxx> wrote:
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 -- Hide quoted text -

- Show quoted text -- Hide quoted text -

- Show quoted text -



.

Relevant Pages

  • RE: Calculations based on varying number
    ... Create a table called "Invoices" with fields and data as follows - ... Run the query ... ... Trade Details, trade date, buy price, sell price) ... I need to create weekly account statements that I am able to ...
    (microsoft.public.access.modulesdaovba)
  • Re: Summary Query based on First Occurrence
    ... AccountDirKey is the primary key on the Accounts table and is ... You tried to execute a query that does not include the specified expression ... "Jason Lepack" wrote: ... Invoices AS B ...
    (microsoft.public.access.queries)
  • RE: Display queried records with Null values (null recordcount)
    ... tables related to Assets and Transactions tables in the query, ... since AssetID from tblAssets is a foreign ... The query would return multiple rows per account, ...
    (microsoft.public.access.gettingstarted)
  • RE: How to Retrieve and diplay the details in the form
    ... query for the main form. ... "naveen prasad" wrote: ... but when i change the account number the appropriate fields are not changing. ... and get the values of d1 and display in other fields in the form. ...
    (microsoft.public.access.queries)
  • 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)
Loading