Convert SQL Server Query Code To MS Access Code - HOW??

[eb24]

I have a query that runs as intended in SQL Server 2000 Query Analyzer. I am trying to export it to an MS Access Query, but can't seem to narrow down the syntax for this subquery. Can someone please assist me?

SELECT dbo.Invoices.InvoiceID, 
	dbo.Actions.ActionDT
FROM	dbo.Invoices INNER JOIN
       	dbo.Actions ON dbo.Invoices.InvoiceID = dbo.Actions.InvoiceID 
WHERE (dbo.Actions.ActionDT = 
		(select max(t1.ActionDT) 
		from dbo.Actions t1 
		where t1.InvoiceID = Actions.InvoicesID))

FYI, this code selects the latest action performed on an Invoice, i.e. ONE Invoice can have MANY Actions.

 

[flyover789]

like that?

SELECT max(dbo.Actions.ActionDT), Last(dbo.Invoices.InvoiceID)
FROM    dbo.Invoices INNER JOIN
           dbo.Actions ON dbo.Invoices.InvoiceID = dbo.Actions.InvoiceID
GROUP BY dbo.Invoices.InvoiceID
ORDER BY dbo.Actions.ActionDT ASC, dbo.Invoices.InvoiceID ASC;

HTH,
fly

[blue]Typos, that don't affect the functionality of code, will not be corrected.[/blue]

Martin Serra Jr.

http://www.mserrasystems.com

[blue]Database_Systems and _Applications shared across all Business_Areas[/blue]

 

[PHV]

In fact you wanted this ?

SELECT Actions.InvoiceID, Max(Actions.ActionDT) AS LastActionDT
FROM Invoices INNER JOIN Actions ON Invoices.InvoiceID = Actions.InvoiceID
GROUP BY Actions.InvoiceID

And if referential integrity is on, no join needed at all...

Anyway not saying us which problem you had is clumsy.

Perhaps this ?
SELECT I.InvoiceID, A.ActionDT
FROM Invoices I INNER JOIN Actions A ON I.InvoiceID = A.InvoiceID
WHERE A.ActionDT = (select max(ActionDT)
from Actions where InvoiceID = A.InvoicesID)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[PNC]

Why bother changing it, just use a pass-through query, and it's faster than an Access query.