Solved

Access 2003 VBA Alternative to Union Query

Posted on 2009-06-29
9
372 Views
Last Modified: 2013-11-27
Hello EE,
I am receiving many files from different sources with data in different columns.  I have created a very long union query that normalizes this data for me, but it runs slower than desired.  Is there a VB alternative to union queries that can be used to normalize data (ie. turn columns into rows?)  If so can you give me an example using the below.
Thanks,
LVBarnes

Table1
IDField  Column1  Column2
1            Red        15
2            Blue        25

VB Results
IDField  FieldName FieldValue
1          Column1    Red
1          Column2    15
2          Column1     Blue
2          Column2     25
0
Comment
Question by:Lawrence Barnes
9 Comments
 
LVL 10

Expert Comment

by:therealmongoose
ID: 24737650
a cross tab query can be used to turn columns into rows. You may want to sit an append query over the top to paste results into a separate table with indexes to speed up your final results query...
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 24737755
Hello Mongoose,

I tried that route, but I'm dealing with 50 columns...which would mean 50 crosstabs.  I went the union query route (50 deep) which seemed faster as it is one statement which is then appended into the normalized table.  But, looking to make it faster too.  I saw this method (by Cap), thinking it may be faster, but was hoping for a shell of it using the above example, which I could adjust from.
http://www.experts-exchange.com/Microsoft/Development/MS_Access/Q_23217542.html?sfQueryTermInfo=1+crosstab+normal+tabl+via
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 24743505
Typically, for the same operation performed, VBA will be slower that SQL.

So I don't know if ther will be any spped increases there.
But you are happy to try it and see.

Post Cap again in that thread and see if he is willing to help.

JeffCoachman
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 24747830
I'm probably explaining this poorly :( .  For this project I'm confined to Access 2003.
* I have a raw table with a few identify fields and 30-50 data fields.  The data for this table comes from many different external sources.
* For each source I have a data map which tells me what information is in which field.  [Address1], for example, could be in Field12 for some rows, and could be in Field15 for others (because of the thousands of different sources.)
* To match the information to my data map I currently normalize this information with a union query so that I have my identity fields, a field name and a field value on each row.  Then I match it with my data map and write all the data to the correct columns.
* The union query I use is posted below.  This query is called from a form and the where statement on each line is theoretically supposed to make it faster...I'm looking for a faster way to process the normalization (other than the union query).

Thank you,
LVBarnes
SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field00" AS Field, Field00 AS FieldValue

FROM tblImportWorkingSchemaCheckSample

UNION

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field01" AS Field, Field01 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field02" AS Field, Field02 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field03" AS Field, Field03 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field04" AS Field, Field04 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field05" AS Field, Field05 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field06" AS Field, Field06 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field07" AS Field, Field07 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field08" AS Field, Field08 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field09" AS Field, Field09 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field10" AS Field, Field10 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field11" AS Field, Field11 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field12" AS Field, Field12 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field13" AS Field, Field13 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field14" AS Field, Field14 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field15" AS Field, Field15 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field16" AS Field, Field16 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field17" AS Field, Field17 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field18" AS Field, Field18 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field19" AS Field, Field19 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field20" AS Field, Field20 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field21" AS Field, Field21 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field22" AS Field, Field22 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field23" AS Field, Field23 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field24" AS Field, Field24 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field25" AS Field, Field25 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field26" AS Field, Field26 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field27" AS Field, Field27 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field28" AS Field, Field28 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

Union

SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field29" AS Field, Field29 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName

UNION SELECT FileSection, DefaultCustNbr, FilePath, FileName, FileDate, FileSize, FileTabName, ClaimSubset, ImportLineNbr, "Field30" AS Field, Field30 AS FieldValue

FROM tblImportWorkingSchemaCheck

WHERE FilePath=Forms!frmImportSchemaCheck.FilePath and FileName=Forms!frmImportSchemaCheck.FileName and FileDate=Forms!frmImportSchemaCheck.FileDate and FileSize=Forms!frmImportSchemaCheck.FileSize and FileTabName=Forms!frmImportSchemaCheck.FileTabName;

Open in new window

0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 14

Expert Comment

by:Bill Ross
ID: 24750446
Seems to me you may want to approach this process a little differently.  Rather than so many columns why not create a table with the desired fields and load the data into it correctly - save the load specification - and reuse as needed.

You have have non-normal data as a result of the load from the many sources but the data seems to contain similar information that could be normalized at the load point.

Another alternative that comes to mund is using a VBA script to parse the files and load the individual fields.


0
 
LVL 14

Accepted Solution

by:
Bill Ross earned 500 total points
ID: 24750495
If you want to use VBA here is a way to get your result but you will need to define an output table (table2) to catch the outpput.

function ConvertColumnsToRows()
  dim db as dao.database, rsIn as dao.recordset, rsOut as dao.recordset
  set db = currentdb
  set rsIn = db.openrecordset("Select * from Table1;")
  set rsOut=db.openrecordset("Select * from Table2")
  do until rsIn.eof
    with rsOut
       .addnew
      .IDField = rsIn.IDField
      .FieldName = rsIn.Column1
      .FieldValue = rsIn.Column2
     .update
     .movenext
loop
set rsIn = nothing
set rsOut = nothing
     .
   
0
 
LVL 14

Expert Comment

by:Bill Ross
ID: 24750511
oops typo...

  do until rsIn.eof
    with rsOut
       .addnew
      .IDField = rsIn.IDField
      .FieldName = rsIn.Column1
      .FieldValue = rsIn.Column2
     .update
     **** Change .MoveNext to
      rsIn.movenext
loop
0
 
LVL 5

Author Comment

by:Lawrence Barnes
ID: 24753901
Hi Bill,
I would love to load the files directly... the thing is that I get files from thousands of users a day.  They are all "supposed" to follow a template, but the template deviates from user to user.  The changes range from no column headings, changed columns, missing columns, additional columns, columns in different orders or (my favorite) "no column headings with columns in a different order and mixed data types within each column."  It's pretty bad.  So I'm banking on the hope that the users will at least be consistent with their malfeasant data format.

So rather than review each file individually each week, I am reviewing each file from the user once, and setting up a schema for that customer.  Subsequent files will be bounced against that schema and the data channeled to its correct field.  I have the single input table (with 30-50 columns) that holds the original information as a data source and reference.

I'll be reviewing your VBA snippet today.
Thanks,
LVBarnes
0
 
LVL 5

Author Closing Comment

by:Lawrence Barnes
ID: 31598004
Thank you
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…

863 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now