Help with SQL Query - newest records 1

[RobSchultz]

I have an accounts table and a balance history table. For discussion lets say the tables are set up as follows:

tblAccounts
accountID (autonumber)
isActive (yes/no account is active)

tblBalances
balanceID (autonumber)
accountID (FK to tblAccounts.accountID)
balance (currency)
lastUpdated (date/time)

Every day the latest and greatest balances are pulled in and appended to the tblBalances table.

How would a query be constructed to show only the most recent balance for all accounts (whether a balance existed or not) without resorting to a (ugh) mostRecent checkbox? I cannot get my brain around what seems to be a solvable problem.

The output query would ideally have accountID, balanceID, balance and lastUpdated.

TIA,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[lespaul]

Try this:

select B.AccountID, B.Balance, B.LastUpdated from tblBalances As B left join tblAccounts As A on A.AccountID = B.AccountID WHERE B.LastUpdated = (SELECT Max(C.LastUpdated) FROM tblBalances as C WHere C.AccountID = B.AccountID) AND isActive

Leslie

 

[RobSchultz]

Leslie,

Thank you very much (especially for the quick response)! I tried something very similar but (obviously) didn't have all my ducks in a row.

Thanks again,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[lespaul]

Did that work right? Cool beans, must be my lucky day! Usually takes 3 or 4 tries to get it all good!

les

 

[RobSchultz]

OK, a small fly in the ointment. In a test environment I have the following data:

tblAccounts:
accountID isActive
1 Yes
2 Yes

tblBalances:
balanceID accountID balance lastUpdated
1 1 $5.00 1/1/2004
2 1 $10.00 2/1/2004
3 1 $15.00 3/1/2004

Using this query:

SELECT A.accountID, B.balance, B.lastUpdated
FROM tblBalances AS B RIGHT JOIN tblAccounts AS A ON B.accountID = A.accountID
WHERE (((A.isActive)=-1)) AND b.lastUpdated=(SELECT Max(C.LastUpdated) FROM tblBalances as C WHere C.AccountID = B.AccountID)

returns 4 rows (3 for account 1, 1 for account 2)

Adding in the additional sub-query:

SELECT A.accountID, B.balance, B.lastUpdated
FROM tblBalances AS B RIGHT JOIN tblAccounts AS A ON B.accountID = A.accountID
WHERE (((A.isActive)=-1)) AND b.lastUpdated=(SELECT Max(C.LastUpdated) FROM tblBalances as C WHere C.AccountID = B.AccountID) AND B.lastUpdated=(SELECT Max(C.LastUpdated) FROM tblBalances as C WHere C.AccountID = B.AccountID)

only produces a single record for account 1.

Is there any way to modify the query so that it will return all of the latest balances as well as any records without balances or do I need to create a UNION.

Thanks again,

Rob

-Focus on the solution to the problem, not the obstacles in the way.-

 

[RobSchultz]

This works

SELECT A.accountID, B.balance, B.lastUpdated
FROM tblBalances AS B RIGHT JOIN tblAccounts AS A ON B.accountID = A.accountID
WHERE (((B.lastUpdated)=(SELECT Max(C.LastUpdated) FROM tblBalances as C WHere C.AccountID = B.AccountID) And (B.lastUpdated)=(SELECT Max(C.LastUpdated) FROM tblBalances AS C RIGHT JOIN tblAccounts AS D ON C.accountID = D.accountID WHERE C.AccountID = B.AccountID)) AND ((A.isActive)=-1)) UNION SELECT E.accountID, F.balance, F.lastUpdated
FROM tblBalances AS F RIGHT JOIN tblAccounts AS E ON F.accountID = E.accountID WHERE F.lastUpdated Is Null

The question is; Is it the most efficient method?

Rob

-Focus on the solution to the problem, not the obstacles in the way.-