Complex SQL statement in MS Access

[Zwoker]

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.

[janders468]

I think that the easiest way to do this would be to split it into two queries, the first query should consist of all the records to retrieve from the master table based on whatever criteria. Join this query to the transaction table ordering by the date field descending, which will put the most recent date first and use the top keyword after the select statement just like you suggested.
So it would be

Select TOP 1 Field1, Field2...Fieldn
From TransactionTabl e Inner Join TransactionTabl e.KeyField = MasterTableQuer y.KeyField
Order By Transaction.Dat eField Desc

Hope that helps

[Zwoker]

Ok, I hadn't thought of doing it that way. I had it in my head that to make it run quickly I needed to do it as a single query.

Before I read the reply I had already compromised by doing it as a single query with the Joins, and the Where statements from the user selection criteria. I ordered the result by the join keys and the date field. Although this returned a very large number of records, I coded a simple "on change" of the keys in VBA and just kept the first record for each change. A non-clean solution, but one that still runs reasonably quickly.

I'll try doing the second query using the table from the first, as suggested, and see how it goes.

The one problem I am having is that using a connection string approach to my data, rather that just declaring external tables in MS Access, seems to stop me returning the result directly as a table - I have to keep it as a result set. This is because the SELECT INTO statement doesn't seem to like the fact that I'm reading from an external table with the connection string, but trying to write to a local table. Or am I missing something obvious?

[janders468]

That doesn't seem like it should be a problem. What error does it give you when you try to make it into a table?

[Zwoker]

That doesn't seem like it should be a problem. What error does it give you when you try to make it into a table?

It gives me an Oracle error about the table not existing. Which makes sense, as I don't want (and am not allowed) to create the table there, but want to create it in my local MS Access database where the code is running from.

It might be as simple as the method I'm using to call the SQL that I have built for the read. It looks like this:

Set InitialRst = New ADODB.Recordset
InitialRst.Open SQLstring, connForDB, adOpenStatic, adLockReadOnly

If the SQLstring contains a SELECT INTO there is no way for it to know that I want the table to be created in the local database (is there?) rather that in the remote Oracle database that the connection string points to.

Alternatively, since I don't know SQL very well, and the syntax for the Oracle version even less, it could be a simple issue with the SQL command I am building. Just how do I write the SQL to allow me to read from the external table, but write to a local (MS Access) one?
If you can give me a simple example it might solve the issue.

I can see I need to find some sort of online tutorial on this subject, which can show me the different options avaiable in using the SQL string I have built over the remote database.