Derived Tables in Jet SQL? 1

[unifex]

Hi all,

Does MS Jet SQL support Derived tables? I'll tell you what i'm doing and see what you come up with.

I have an audit log of account changes. Fields are Id, ChangeDate, ChangeType, AccountNumber

There is one record per change on each account. What i need is the last change of a specific type for each account number. I've tried 2 methods:

Subquery

Select *
From tblAuditLog
Where id In
(
Select max(id)
From tblAuditLog
Where ChangeType like '%EXTSTATUS'
Group By accountno
)
;

This works but is hellishly slow. Note also that the Max(ID) also conveniently relates to the last change for the account (i.e the Max Date). I also tried this derived table:

Select *
From (
Select max(id) as id2
From tblAuditLog
Where ChangeType like '%EXTSTATUS'
Group By accountno
) As DervdTbl
Inner Join tblauditlog OrigTbl
On DervdTbl.id2=OrigTbl.id

This was created by me in Queryman and worked when i connected to the access database but when tried in access itself gave a syntax error.

Any suggestions?

Thanks

Unifec

 

[mpastore]

Try this to see if it speeds up:

Select *
From tblAuditLog a
Where id In
(
Select max(id)
From tblAuditLog
Where ChangeType like '*EXTSTATUS'
and accountno=a.accountno
)
;

Note: I've changed '%EXTSTATUS' to '*EXTSTATUS' because I am using MS Access tables and asume you meant to do a wild card search?

Mike Pastore

Hats off to (Roy) Harper

 

[unifex]

I see what you're trying to do here with the correlated subquery, unfortunately it appears just as slow as the original subquery. The derived table worked in a fraction of the time.

I actually wrote the query in Queryman (an NCR Teradata piece of software) just cuz it's easier. The '%' is equivalent to the access '*'.

Thanks anyway

Unifex

 

[cmmrfrds]

I have done derived tables in Access, but Access likes to change the syntax and uses [ ]. instead of parens. Try.

Select *
From [
Select max(id) as id2
From tblAuditLog
Where ChangeType like '%EXTSTATUS'
Group By accountno
]. As DervdTbl
Inner Join tblauditlog OrigTbl
On DervdTbl.id2=OrigTbl.id

The way this is typically done in Access is to make the derived query a separate query and then join to that query instead of doing it in one query.

 

[unifex]

Oh yes. That's the one! I knew access wouldn't let me down.

Thanks for all your help.

Unifex