Solved

Access 2003 VBA Alternative to Union Query

Posted on 2009-06-29
9
369 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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 
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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Most if not all databases provide tools to filter data; even simple mail-merge programs might offer basic filtering capabilities. This is so important that, although Access has many built-in features to help the user in this task, developers often n…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

758 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

22 Experts available now in Live!

Get 1:1 Help Now