Subquery Syntax error

[dhstein]

I'm trying to figure out how to do something - so I'm playing around with
this subquery and getting a syntax error.

SELECT tblInvoice.CustomerName
FROM tblInvoice INNER JOIN tblLines ON tblInvoice.InvoiceNumber =
tblLines.InvoiceNumber
(SELECT TOP 1 Dupe.CustomerName
FROM tblInvoice AS Dupe
WHERE Dupe.InvoiceNumber = tblInvoice.InvoiceNumber
ORDER BY Dupe.InvoiceDate DESC)
FROM tblInvoice;

Any help is appreciated

 

[Sylvain Lafontaine]

Not telling us what you are trying to do doesn't make the thing easier to
understand for us. Also, your subquery seems to be misplaced as it is
presently in the FROM part instead of beeing in the SELECT or the WHERE part
and the way it is, I absolutely don't understand what you are trying to
achieve.

The subquery is also missing an alias name; something which is always
mandatory for a subquery.

Finally, using a totally different name such as Dupe for renaming the table
Invoice in the subquery is calling for trouble for later when you'll try to
read back your code. You should use something easier to understand such as
Invoice2 or I2.

--
Sylvain Lafontaine, ing.
MVP - Windows Live Platform
Email: sylvain2009 sylvainlafontaine com (fill the blanks, no spam please)
Independent consultant and remote programming for Access and SQL-Server
(French)