Hi all,
I'm not sure whether this should be posted in the MS Access forum or one of the SQL forums (which one?), so I'll start here.
I'm self taught in the syntax of SQL based queries that I have been writing in VBA in my MS Access 2003 environment, which has proved to be an issue now that I want to do something a bit more complicated than the ordinary, but have no idea how. Googling for an example has failed to find anything close to what I want.
What I'm trying to do is write a single SQL statement to read all the records that match user-selected criteria in a master table, and as part of that, read just one record from a JOIN'ed transaction table, with the record I want being the most recent record for the joined keys. Fortunately there is a date field in the transaction table.
Now I believe that I need some sort of subquery with a TOP 1 in it, with an ORDER BY the transaction date (descending), but trying to integrate the subquery with the JOIN statement has been a complete disaster.
I separated the two activities, doing the initial SELECT over the master and getting all the records that matched the user criteria, and then looping through each of those records to get the most recent transaction record, but the run time for the process went from 35 seconds without the looped read of the transaction table, to over 30 minutes with it present.
Does anyone have a simple example of a JOIN and a subquery that is something like what I'm trying to do, that I can use as the foundation for my query?
Thanks.