By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,222 Members | 1,132 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,222 IT Pros & Developers. It's quick & easy.

Mass Import w/Multiple Tables

blyxx86
100+
P: 256
Firstly, I apologize for any spelling errors being made. I am using a new keyboard right now and I'm not sure if I like it very much.

That being said, let's continue.

I am trying to import an excel spreadsheet that has information like "Serial, Model, Store, Analyst, Ticket" Into my database. The problem is this... Of the fields that would be imported only certain ones get imported into one table, and others are related to the other table in a many-to-one relationship.

For example Ticket, Store, and Analyst get imported into a table called "tblRequest" and the other fields are related to this table.

So there is also a second table, "tblRequestDetail" that contains "Model" and "Serial"

How can I import this properly and in a single action that would import the information and correctly relate the data. Anyone have any ideas?

Thanks!
Sep 4 '07 #1
Share this Question
Share on Google+
3 Replies


Jim Doherty
Expert 100+
P: 897
Firstly, I apologize for any spelling errors being made. I am using a new keyboard right now and I'm not sure if I like it very much.

That being said, let's continue.

I am trying to import an excel spreadsheet that has information like "Serial, Model, Store, Analyst, Ticket" Into my database. The problem is this... Of the fields that would be imported only certain ones get imported into one table, and others are related to the other table in a many-to-one relationship.

For example Ticket, Store, and Analyst get imported into a table called "tblRequest" and the other fields are related to this table.

So there is also a second table, "tblRequestDetail" that contains "Model" and "Serial"

How can I import this properly and in a single action that would import the information and correctly relate the data. Anyone have any ideas?

Thanks!
If we assume that the excel spreadsheet itself is a LINKED from a physical disk and not imported (Tools... Get external data...Link Tables...Files Of Type... Microsoft Escel (*.xls)) then your spreadsheet is going to be visible in the Access window AS a table in others words it has not been 'actually' imported.

I don't see necessarily a reason to actually import it per se, from what I am understanding here. The purpose is to retrieve from that xls file normalised data from specific columns and to populate a table with that data and then send the remaining data wherever else you want it to go. Let us assume the linked spreadsheet is called lt_xls_tickets (you can rename linked tables by the way without affecting the physical name of the file on disk)

The area you are looking for surrounds the word 'DISTINCT' in SQL syntax
so if we wanted to retrieve a single reference to Ticket,Store,Analyst from the 3 spreadsheet columns potentially repeated over thousands of rows the query syntax in the SQL window of the query would be

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT lt_xls_tickets.Ticket, lt_xls_tickets.Store,lt_xls_tickets.Analyst
  2. FROM lt_xls_tickets;
if you then wanted to insert these distinct values into a physical table of the same name lets say tblRequest then the same command would be amended to this

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblRequest (Ticket, Store,Analyst)
  2. SELECT DISTINCT lt_xls_tickets.Ticket, lt_xls_tickets.Store,lt_xls_tickets.Analyst
  3. FROM lt_xls_tickets;
The receiving table for inserts remember has to only accept UNIQUE records in order for it to be a ONE table on the ONE to MANY side of any join, so you have to have a primary key set on the receiving table to control this.

If TICKET is unique ie: a number uniquely identifying the ticket, where ticket might be a transaction reference number or something like that (Idon't know your data) then you would set the TICKET to be the primary key.

Now when you perform the 'insert' Access will automatically prevent you from inserting duplicates into the ONE table by returning an error but we know this is the obviously 'default' behaviour but we do not want the error message we simply want it to carry on and insert in there as many unique records it can so to speak.

We turn OFF the error message in code with this prior to inserting

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Setwarnings FALSE
and then turn it back on again after the insert transaction like this

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Setwarnings TRUE
So overall the linear commands would look like this in VBA code where we are assigning firstly the SQL syntax to a string variable to hold the value of the SQL statement and using the inbuilt DoCmd.RunSQL to actually run that syntax and do the job.

Expand|Select|Wrap|Line Numbers
  1. 'declare a variable of the string type
  2. Dim strSQL as String
  3.  
  4. strSQL="INSERT INTO tblRequest (Ticket, Store,Analyst) "
  5. strSQL = strSQL & "SELECT DISTINCT lt_xls_tickets.Ticket, " strSQL=strSQL & "lt_xls_tickets.Store,lt_xls_tickets.Analyst "
  6. strSQL=strSQL & "FROM lt_xls_tickets;
  7.  
  8. DoCmd.Setwarnings FALSE
  9. DoCmd.RunSQL strSQL
  10. DoCmd.Setwarnings TRUE
You can turn the hourglass on and off at appropriate points to give the user an understanding that something is still happening

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Hourglass TRUE
  2. Docmd.Hourglass FALSE

So to cap it off you reduce this whole importation process that I describe thus far to a command button where the VBA code for the command button looks something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDoInsert_Click()
  2. On Error GoTo Err_cmdDoInsert_Click
  3. Dim strSQL as String
  4.  
  5. strSQL="INSERT INTO tblRequest (Ticket, Store,Analyst) "
  6. strSQL = strSQL & "SELECT DISTINCT lt_xls_tickets.Ticket, " strSQL=strSQL & "lt_xls_tickets.Store,lt_xls_tickets.Analyst "
  7. strSQL=strSQL & "FROM lt_xls_tickets;
  8.  
  9. DoCmd.Setwarnings FALSE
  10. DoCmd.RunSQL strSQL
  11.  
  12. Exit_cmdDoInsert_Click:
  13.     Exit Sub
  14.  
  15. Err_cmdDoInsert_Click:
  16. DoCmd.Hourglass FALSE
  17. Do.CmdSetWarnings TRUE
  18.     MsgBox Err.Description, vbExclamation, "System Message"
  19.     Resume Exit_cmdDoInsert_Click
  20.  
  21. End Sub
The rest of your data is denormalised data (ie many occurrences of the same data values) simplistic explanation I know, but I guess to the average Joe its probably the best way to describe it?

I assume having explained the above, that you will have no problem with the rest of your data logically speaking. All you have to remember is that whatever you insert into the MANY table must carry with it the column data value (in this case TICKET) in order for the relationship to succeed between the two tables.

The insert on the 'many' side essentially follows the same pattern but you do not mention the DISTINCT keyword in any insert command on the many side

If your tables are set up to receive the data and referential integrity is set already
then this should get the job done for you, without worrying about the relationship particularly.

The code I present to you is an example to follow its not the only way to get the job done and you can supe it up using constant replacements for command arguments like FALSE maps out to vbFalse and so on.

Additonally you can do it without using the DoCmd.RunSQL command using ADO .execute methods and so on. There are all sorts of ways to do this.

You have to 'keep your eye on the eight ball' so to speak as to whether certain commands might be dropped ie: DoCmd.RunSQL in future versions of Access and so on but then we're all subject to that one.

Hope this helps you

Jim
Sep 4 '07 #2

blyxx86
100+
P: 256
If we assume that the excel spreadsheet itself is a LINKED from a physical disk and not imported (Tools... Get external data...Link Tables...Files Of Type... Microsoft Escel (*.xls)) then your spreadsheet is going to be visible in the Access window AS a table in others words it has not been 'actually' imported.

I don't see necessarily a reason to actually import it per se, from what I am understanding here. The purpose is to retrieve from that xls file normalised data from specific columns and to populate a table with that data and then send the remaining data wherever else you want it to go. Let us assume the linked spreadsheet is called lt_xls_tickets (you can rename linked tables by the way without affecting the physical name of the file on disk)

The area you are looking for surrounds the word 'DISTINCT' in SQL syntax
so if we wanted to retrieve a single reference to Ticket,Store,Analyst from the 3 spreadsheet columns potentially repeated over thousands of rows the query syntax in the SQL window of the query would be

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCT lt_xls_tickets.Ticket, lt_xls_tickets.Store,lt_xls_tickets.Analyst
  2. FROM lt_xls_tickets;
if you then wanted to insert these distinct values into a physical table of the same name lets say tblRequest then the same command would be amended to this

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO tblRequest (Ticket, Store,Analyst)
  2. SELECT DISTINCT lt_xls_tickets.Ticket, lt_xls_tickets.Store,lt_xls_tickets.Analyst
  3. FROM lt_xls_tickets;
The receiving table for inserts remember has to only accept UNIQUE records in order for it to be a ONE table on the ONE to MANY side of any join, so you have to have a primary key set on the receiving table to control this.

If TICKET is unique ie: a number uniquely identifying the ticket, where ticket might be a transaction reference number or something like that (Idon't know your data) then you would set the TICKET to be the primary key.

Now when you perform the 'insert' Access will automatically prevent you from inserting duplicates into the ONE table by returning an error but we know this is the obviously 'default' behaviour but we do not want the error message we simply want it to carry on and insert in there as many unique records it can so to speak.

We turn OFF the error message in code with this prior to inserting

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Setwarnings FALSE
and then turn it back on again after the insert transaction like this

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Setwarnings TRUE
So overall the linear commands would look like this in VBA code where we are assigning firstly the SQL syntax to a string variable to hold the value of the SQL statement and using the inbuilt DoCmd.RunSQL to actually run that syntax and do the job.

Expand|Select|Wrap|Line Numbers
  1. 'declare a variable of the string type
  2. Dim strSQL as String
  3.  
  4. strSQL="INSERT INTO tblRequest (Ticket, Store,Analyst) "
  5. strSQL = strSQL & "SELECT DISTINCT lt_xls_tickets.Ticket, " strSQL=strSQL & "lt_xls_tickets.Store,lt_xls_tickets.Analyst "
  6. strSQL=strSQL & "FROM lt_xls_tickets;
  7.  
  8. DoCmd.Setwarnings FALSE
  9. DoCmd.RunSQL strSQL
  10. DoCmd.Setwarnings TRUE
You can turn the hourglass on and off at appropriate points to give the user an understanding that something is still happening

Expand|Select|Wrap|Line Numbers
  1. DoCmd.Hourglass TRUE
  2. Docmd.Hourglass FALSE

So to cap it off you reduce this whole importation process that I describe thus far to a command button where the VBA code for the command button looks something like this:

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdDoInsert_Click()
  2. On Error GoTo Err_cmdDoInsert_Click
  3. Dim strSQL as String
  4.  
  5. strSQL="INSERT INTO tblRequest (Ticket, Store,Analyst) "
  6. strSQL = strSQL & "SELECT DISTINCT lt_xls_tickets.Ticket, " strSQL=strSQL & "lt_xls_tickets.Store,lt_xls_tickets.Analyst "
  7. strSQL=strSQL & "FROM lt_xls_tickets;
  8.  
  9. DoCmd.Setwarnings FALSE
  10. DoCmd.RunSQL strSQL
  11.  
  12. Exit_cmdDoInsert_Click:
  13.     Exit Sub
  14.  
  15. Err_cmdDoInsert_Click:
  16. DoCmd.Hourglass FALSE
  17. Do.CmdSetWarnings TRUE
  18.     MsgBox Err.Description, vbExclamation, "System Message"
  19.     Resume Exit_cmdDoInsert_Click
  20.  
  21. End Sub
The rest of your data is denormalised data (ie many occurrences of the same data values) simplistic explanation I know, but I guess to the average Joe its probably the best way to describe it?

I assume having explained the above, that you will have no problem with the rest of your data logically speaking. All you have to remember is that whatever you insert into the MANY table must carry with it the column data value (in this case TICKET) in order for the relationship to succeed between the two tables.

The insert on the 'many' side essentially follows the same pattern but you do not mention the DISTINCT keyword in any insert command on the many side

If your tables are set up to receive the data and referential integrity is set already
then this should get the job done for you, without worrying about the relationship particularly.

The code I present to you is an example to follow its not the only way to get the job done and you can supe it up using constant replacements for command arguments like FALSE maps out to vbFalse and so on.

Additonally you can do it without using the DoCmd.RunSQL command using ADO .execute methods and so on. There are all sorts of ways to do this.

You have to 'keep your eye on the eight ball' so to speak as to whether certain commands might be dropped ie: DoCmd.RunSQL in future versions of Access and so on but then we're all subject to that one.

Hope this helps you

Jim
That helps A LOT! Thank you.

Only one problem.. In that my primary key is a combination of two fields CustID and Ticket. However, I am pretty sure I can figure it out from what you have told me. I just need to include another field on the imports. :-) Thank you!
Sep 6 '07 #3

Jim Doherty
Expert 100+
P: 897
That helps A LOT! Thank you.

Only one problem.. In that my primary key is a combination of two fields CustID and Ticket. However, I am pretty sure I can figure it out from what you have told me. I just need to include another field on the imports. :-) Thank you!
You're welcome I'm glad it helped you

Jim
Sep 6 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.