INNER JOIN table to query - need to delete query!

[Haydnw]

Hi, I have a query which I use to get the MAX value from a 'strDirectory'
field in a table 'tblEvents'. It's saved as qryMaxDir. I then use an INNER
JOIN to link that to the tblEvents table again. This enables me to get some
more information from the table relating to the record found with the MAX
'strDirectory'.

SELECT tblEvents.strDirectory, tblEvents.strArtist, tblEvents.strVenue
FROM tblEvents INNER JOIN qryMaxDir ON tblEvents.strDirectory =
qryMaxDir.strNewestDir;

This works fine in Access; however, I now need to use this SQL in an ASP.NET
application. Obviously I can't save the query, so how do I do this using raw
SQL and not with the saved query??? I am tearing my hair out trying to get
this to work and I can't so any help would be GREATLY appreciated!! If I
need to explain anything more clearly, please just shout!

Thanks,
H

 

[Haydnw]

OK - my bad! Turns out I can use saved queries, but I'd still like to know
how it's done. Surely it's possible?

Cheers,
H

 

[Gary Walter]

Hi Haydn,

It sounds like you want something like:

SELECT
strDirectory,
strArtist,
strVenue
FROM tblEvents
WHERE
strDirectory = (SELECT Max(e.strDirectory) FROM tblEvents As e);

Good luck,

Gary Walter