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

[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.

 

[DrSql]

Try to change the dbo with yout table name
[tt]
SELECT TableName.Invoices.InvoiceID,
TableName.Actions.ActionDT
FROM TableName.Invoices INNER JOIN
TableName.Actions ON TableName.Invoices.InvoiceID = TableName.Actions.InvoiceID
WHERE (TableName.Actions.ActionDT =
(select max(t1.ActionDT)
from TableName.Actions t1
where t1.InvoiceID = Actions.InvoicesID))
[/tt]

Dr.Sql
Good Luck.

 

[eb24]

What table name? In dbo.Invoices.InvoiceID, 'Invoices' is the table name. In my code, there are only two tables: Invoices and Actions.

 

[SQLBill]

Since you are trying to convert that script to an Access (Jet-SQL) script, I suggest you post your query in one of the MS Access forums on this site.

-SQLBill

Posting advice: FAQ481-4875

 

[DrSql]

Try this way.
The best method to avoid this is why dont you link or import the the SQL table into ACCESS and create a query.

[tt]
SELECT Invoices.InvoiceID,
Actions.ActionDT
FROM Invoices INNER JOIN
Actions ON Invoices.InvoiceID = Actions.InvoiceID
WHERE (Actions.ActionDT =
(select max(t1.ActionDT)
from Actions t1
where t1.InvoiceID = Actions.InvoicesID))
[/tt]
Dont worry what others say.. You are looking for an answer..

Dr.Sql
Good Luck.

 

[eb24]

Dr. Sql:

Thank you for your advice - I went ahead a created a separate query and refernced it from the main query and it worked perfectly!