Access Append Query In VBA From Multiple Sources Into One Table, Access 2010 (1 Viewer)

[agr1487]

I hardly ever post for help and try to figure it out on my own, but now I’m stuck. I’m just trying to append data from multiple tables to one table. The source tables are data sets for each American State and the append query is the same for each State, except for a nested select script to pull from each State table. So I want to create a VBA script that references a smaller script for each state, rather than an entire append script for each state. I’m not sure if I should do a SELECT CASE, or FOR TO NEXT or FOR EACH NEXT or DO LOOP or something else.

Here’s what I have so far:

tblLicenses is a table that has the field LicenseState from which I could pull a list of the states.

______________________


Function StateScripts()

Dim rst As DAO.Recordset
Dim qryState As String
Dim StateCode As String
Set rst = CurrentDb.OpenRecordset("SELECT LicenseState FROM tblLicenses GROUP BY LicenseState;")
' qryState = DLookup("LicenseState", "tblLicenses")
' qryState = "SELECT LicenseState INTO Temp FROM tblLicenses GROUP BY LicenseState;"
' DoCmd.RunSQL qryState

Select Case qryState
Case "CT"
StateCode = "CT"
StateScripts = " SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT "
Case "AK"
StateCode = "AK"
StateScripts = " SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK "
Case "KS"
StateCode = "KS"
StateScripts = " SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS "
End Select

CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( " & StateScripts & " ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like '" & StateCode & "') "

End Function

_____________________________


When combining StateCode and StateScripts into the Append query, it will look like this:


CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'CT') "

CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'AK') "

CurrentDb.Execute " INSERT INTO TEST ( StLicense, OldExpDate, NewExpDate ) " _
& " SELECT State.StateLicense as StLicense, DateExpire AS OldExpDate, State.dateexpired AS NewExpDate " _
& " FROM ( SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS ) AS State " _
& " RIGHT JOIN tblLicenses ON (State.StateLicense = tblLicenses.LicenseNum) " _
& " GROUP BY State.StateLicense, DateExpire, State.dateexpired " _
& " HAVING (((LicenseNum) Like 'KS') "

 

[plog]

... the append query is the same for each State, except for a nested select script to pull from each State table

Huh? Do all the tables have the same field names or not? If they do, then the APPEND query is simple--you use the same query and a variable in the FROM to signify which table you are working with.

If they don't have the same field names then I would make an APPEND query object for each state then use VBA to loop through them all and execute them.

Further, your SQL is kinda off so I didn't really dig too deep into it. Your HAVING should be a WHERE and there's no point in having an ORDER BY in a INSERT INTO. Perhaps you can post a sample database so I can see exactly what's happening.

 

[agr1487]

StateScripts is different for each state. Each state has different fields and different tables. I tweaked my original post to better reflect that.

Where my script starts at "CurrentDb.Execute" is where my APPEND query starts. So how do I create an APPEND query object and use VBA to loop?

My SQL maybe off and I can change it, but it works without variables and whatnot. My issue is how to Append the data from each State table.

 

[jdraw]

agr1487,

Can you tell us in plain English --no jargon and no database terminology -- what exactly you are trying to do?
Perhaps you can give us some sample data showing the before and after of what your intended result would be.

I agree with plog --the SQL is puzzling.

 

[plog]

So how do I create an APPEND query object and use VBA to loop?

Create a new query, bring in Alabama's table. Use the design view to make an APPEND query to move Alabama's data into the main table. Save it. Test it.

Do the same for Alaska, Arizona, etc. Once you have an append for every state's data you can either use a macro to run each query (OpenQuery) or you could make a list of your query names (or get it from MsysObjects) and loop through them using DoCmnd.OpenQuery.

The benefit of this approach is that your individual SQL for each state isn't in a huge module. So when a state changes their data format you can simply go to its query, update it and you don't have to touch the macro/vba.

 

[agr1487]

I'm trying to append data from each state table into one table and I don't want to write the same append query 50 times for each state. I want to write one append query with a nested query inside said append query referencing 50 select queries for each state.

I've updated my original post with an example of how the SQL would look with each state script included.

 

[agr1487]

The benefit of this approach is that your individual SQL for each state isn't in a huge module. So when a state changes their data format you can simply go to its query, update it and you don't have to touch the macro/vba.

Sure, but when I need to change the query (that's not a part of the state module) then I have to change it 50 times. My way, I only have to change the query once in VBA.

In either case, it's much easier to script out 50 append queries in VBA vs. created 50 append query objects in the Access GUI interface.

 

[plog]

Hate to break it to you, but you are writing 50 APPEND queries:

StateScripts = " SELECT [LICENSE NO] AS StateLicense, [EXPIRATION DATE] AS dateexpired FROM CT "
StateScripts = " SELECT [LICENSE] AS StateLicense, [EXPIRATION] AS dateexpired FROM AK "
StateScripts = " SELECT [LicenseNum] AS StateLicense, [ExpDate] AS dateexpired FROM KS "

If each state table had the exact same field names you could do it more simply. But youve got to write a unique query for each state. Do that in a query object.

 

[agr1487]

Hate to break it to you, but you are writing 50 APPEND queries

Actually, it would be 50 SELECT queries, with 1 APPEND query. It would be much easier to debug and take up less space.

I don't see how this can't be done with something like a FOR TO NEXT or FOR EACH NEXT or DO LOOP structure. Anyone else out there have any ideas?

 

[plog]

I'll bow out but I suggest you reply to jdraw's post:

Can you tell us in plain English --no jargon and no database terminology -- what exactly you are trying to do?
Perhaps you can give us some sample data showing the before and after of what your intended result would be.

 

[jdraw]

In the 50 state tables, how many have the same format? Or at least same field names?

If there are some common groups, then you may only need a query for each group. You would execute the query for each State with that structure.
How often do you have to do this process?

Also, when you post the same question on multiple forums-cross posting- please identify that you have done so and include a link to the post/forum.

Here is why.

 

[JHB]

Are the fields in same order in the tables?
Example:

• [LICENSE NO], [LICENSE], [LicenseNum], always field no 2
• [EXPIRATION DATE], [EXPIRATION], [ExpDate], always field no 5

Else you could have a table with all the different fields name per state.

 

[agr1487]

Are the fields in same order in the tables?
Example:

• [LICENSE NO], [LICENSE], [LicenseNum], always field no 2
• [EXPIRATION DATE], [EXPIRATION], [ExpDate], always field no 5

Else you could have a table with all the different fields name per state.

Go on..... ... so you have a table with each state and their respective field names? and then what?

 

[Mark_]

Do your table definitions change often?

If not, I think you've actually spent more time trying to avoid doing 50 simple queries / writing 50 SQL statements then the time you'd have used make the queries/written the SQL.

For a generic way to do this, Arnel has written a very useful function HERE that would allow you to write your insert portion once (and reuse) but then customize where you are getting the data from.

If this is because you keep being sent tables that have varied formats, I can see why you'd want to have an easier way of doing it. In that case it would often be better to simply rename/reorder the tables you get to meet what your system is looking for.

 

[JHB]

Go on..... ... so you have a table with each state and their respective field names? and then what?

Example database attached, (open form frmInsert, click the button, result is going into table test).

 

[The_Doc_Man]

Question: Are the tables always the same structure/order of fields even though the field names are different for each state? I.e. is the first field always the license number and the second field always the expiration date?

If so, try something bizarre like

SELECT STTbl.Fields(1) as StateLicense, STTbl.Fields(2) as DateExpired FROM CT as STTbl

Then all you have to do is replicate that 50 times, once for each state table.

If neither the names nor the order of the fields cann be relied upon, then I don't know of any way that wouldn't involve text parsing of the field names.

But from the syntax of the examples in the first post, all of those tables are already in the same database. Their names should have been normalized on original import. So... how do those tables come into being? Are you importing something like a spreadsheet for each state and the import preserves the column names for each table?

plog's suggestion of writing 50 queries isn't so bad since you would only write them once each. Make a query, name it QueryXX where XX is the two-letter postal abbreviation for that state. So QueryCA, QueryNY, QueryLA, QueryFL, etc. They can be static.

Now your iteration just builds the name from "Query" + a list of state postal abbreviations. You can pick up the SQL of your query from the stored query and iterate through the choices one at a time by concatenating the state code to the constant name prefix.

So you write 50 queries once, then put together one bit of code to grab the stored SQL and insert it (remembering to remove the trailing semi-colon since it is a complete stored query) into your larger expression.

I think that with the mish-mosh of state information you have, you will not be able to avoid at least writing those 50 queries as ... call them translators - that help you to convert 50 non-identical data sets to a single usable format.

 

[agr1487]

Example database attached, (open form frmInsert, click the button, result is going into table test).

JHB that was it! Awesome! Thank you!

 

[JHB]

You're welcome.