Query help please

  • Thread starter Thread starter steveinbeloit via AccessMonster.com
  • Start date Start date
S

steveinbeloit via AccessMonster.com

I have two tables. Accounts and AcctDetails.
Accounts has two columns, Account and Desc.
AcctDetails has three columns, Account, Date, and Balance.
there are multiple detail lines per account, just will different dates and
balances.

For each account in Accounts, I need the most recent date row and that
balance from the details table.

I am getting hung up and can't get this.
Thanks,
steve
 
This could be done in one query if you know subqueries but here it is using
two --
Account_Last_Detail ---
SELECT AcctDetails.Account, Max(AcctDetails.YourDate) AS MaxOfYourDate
FROM AcctDetails
GROUP BY AcctDetails.Account;

SELECT Accounts.Account, Accounts.Desc, AcctDetails.YourDate,
AcctDetails.Balance
FROM (Accounts INNER JOIN AcctDetails ON Accounts.Account =
AcctDetails.Account) INNER JOIN Account_Last_Detail ON (AcctDetails.YourDate
= Account_Last_Detail.MaxOfYourDate) AND (AcctDetails.Account =
Account_Last_Detail.Account);
 
Firstly you need to join the tables in the usual way, then restrict the rows
returned to those where the value of the Date column equals the value
returned by a subquery which identifies the latest date for the account in
the outer query's current row:

SELECT Accounts.Account. Desc, [Date], Balance
FROM Accounts INNER JOIN AcctDetails AS AD1
ON AD1.Account = Accounts.Account
WHERE [Date] =
(SELECT MAX([Date])
FROM AcctDetails AS AD2
WHERE AD2.Account = AD1.Account);

Note how the aliases AD1and AD2 are used to distinguish the two instances of
the AcctDetails table so that the subquery can be correlated with the outer
query.

I'd advise changing the name of the Date column to something like
TransactionDate, however, as Date is the name of a built in function. If you
do use it as a column name be sure to wrap it in brackets, [Date].

Ken Sheridan
Stafford, England
 
Thanks! I understand your solution using the sub select, but, I don't know
how to use sub selects when using the designer in access to make a query.
How do you add another instance of the same table and give it an alias?

Ken said:
Firstly you need to join the tables in the usual way, then restrict the rows
returned to those where the value of the Date column equals the value
returned by a subquery which identifies the latest date for the account in
the outer query's current row:

SELECT Accounts.Account. Desc, [Date], Balance
FROM Accounts INNER JOIN AcctDetails AS AD1
ON AD1.Account = Accounts.Account
WHERE [Date] =
(SELECT MAX([Date])
FROM AcctDetails AS AD2
WHERE AD2.Account = AD1.Account);

Note how the aliases AD1and AD2 are used to distinguish the two instances of
the AcctDetails table so that the subquery can be correlated with the outer
query.

I'd advise changing the name of the Date column to something like
TransactionDate, however, as Date is the name of a built in function. If you
do use it as a column name be sure to wrap it in brackets, [Date].

Ken Sheridan
Stafford, England
I have two tables. Accounts and AcctDetails.
Accounts has two columns, Account and Desc.
Click to expand...
[quoted text clipped - 8 lines]
Thanks,
steve
Click to expand...
Click to expand...
 
Karl - Thanks!

KARL said:
This could be done in one query if you know subqueries but here it is using
two --
Account_Last_Detail ---
SELECT AcctDetails.Account, Max(AcctDetails.YourDate) AS MaxOfYourDate
FROM AcctDetails
GROUP BY AcctDetails.Account;

SELECT Accounts.Account, Accounts.Desc, AcctDetails.YourDate,
AcctDetails.Balance
FROM (Accounts INNER JOIN AcctDetails ON Accounts.Account =
AcctDetails.Account) INNER JOIN Account_Last_Detail ON (AcctDetails.YourDate
= Account_Last_Detail.MaxOfYourDate) AND (AcctDetails.Account =
Account_Last_Detail.Account);
I have two tables. Accounts and AcctDetails.
Accounts has two columns, Account and Desc.
Click to expand...
[quoted text clipped - 8 lines]
Thanks,
steve
Click to expand...
Click to expand...
 
When you are using a subquery there is no way to add the table. You must
type the subquery out.

You can alias a table that is displayed in the top of the design view by
right-clicking on the table title and showing the properties. One of the
table properties is ALIAS and you can type the Alias into the property.



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

steveinbeloit via AccessMonster.com said:
Thanks! I understand your solution using the sub select, but, I don't
know
how to use sub selects when using the designer in access to make a query.
How do you add another instance of the same table and give it an alias?

Ken said:
Firstly you need to join the tables in the usual way, then restrict the
rows
returned to those where the value of the Date column equals the value
returned by a subquery which identifies the latest date for the account in
the outer query's current row:

SELECT Accounts.Account. Desc, [Date], Balance
FROM Accounts INNER JOIN AcctDetails AS AD1
ON AD1.Account = Accounts.Account
WHERE [Date] =
(SELECT MAX([Date])
FROM AcctDetails AS AD2
WHERE AD2.Account = AD1.Account);

Note how the aliases AD1and AD2 are used to distinguish the two instances
of
the AcctDetails table so that the subquery can be correlated with the
outer
query.

I'd advise changing the name of the Date column to something like
TransactionDate, however, as Date is the name of a built in function. If
you
do use it as a column name be sure to wrap it in brackets, [Date].

Ken Sheridan
Stafford, England
I have two tables. Accounts and AcctDetails.
Accounts has two columns, Account and Desc.
Click to expand...
[quoted text clipped - 8 lines]
Thanks,
steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top