Using MS Access/Excel to Access

Advertisement


Question
QUESTION: Hi Scott,

I receive an email each morning with an 2010 Excel spreadsheet that I have been manually formatting to be imported into an 2010 Access database table.

The Access database has a tblAccountGroups containing two fields. An Account Group name and an autonumber primary key, which is the foreign key in the main table.

Upon importing the excel spreadsheet into the main Access table, I have been having to manually enter any new account groups into the account group table, then going to the main table and entering the correct autonumber to the corresponding account group fields.

How can I go about automating this process so that any new account groups in the spreadsheet are recognized and added to the tables with the correct number?

Thank you,

David

ANSWER: Create an Append query something like this:

INSERT INTO tblAccountGroups (GroupName)
SELECT DISTINCT GroupName FROM importtable;

First set a unique index on Group Name

Hope this helps,
Scott<>

---------- FOLLOW-UP ----------

QUESTION: Thanks Scott!

I created 2 append queries based on your example. One to insert unique account groups to tblAccountGroups, and then one to insert the records into the main table. I then created a macro to run both with one click.

The only problem I have now is that the new records inserted into the main table are not being matched up with the AccountGroupID field.

What I mean is that in my tblAccountGroups there are 2 fields, AccountGroup (which is the name of the account group), and an autonumber primary key field called AccountGroupID. Whatever the autonumber field is for the particular account group, I need matched in the main table's field. Can this be accomplished easily or am I just better off manually adding the correct number in the main table's AccountGroupID field?

ANSWER: First you run the Append query to add the Account Groups to your lookup table. Then your second Append query should join to the lookup table on the group name. In the Append, you append the autonumber value as a FK in your main table, rather than the name.

Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Hi again, and thank you for the response. I tried doing what you suggested (I think) and I wound up deleting all the data in my tblAccountGroup after running the lookup wizard. Luckily I created a backup before attempting this.

I import data from my excel spreadsheet into tblImport. I then run an append query to add any new account groups into tblAccountGroup using:

INSERT INTO tblAccountGroup ( AccountGroup )
SELECT DISTINCT [Account Group]
FROM tblImport;

Then the second append query runs, adding the new records to the main table as follows:

INSERT INTO tbl_Allocations ( [Allocation Page Link], URL, [Allocation Type], [Bill Group], Account, [Account Group], [Plan Election], [Effective Date], [Term Date], Carrier, [Carrier Plan ID], [Relationship Manager], [Premium Type] )
SELECT tblImport.[Allocation Page Link], tblImport.URL, tblImport.[Allocation Type], tblImport.[Bill Group], tblImport.Account, tblImport.[Account Group], tblImport.[Plan Election], tblImport.[Effective Date], tblImport.[Term Date], tblImport.Carrier, tblImport.[Carrier Plan ID], tblImport.[Relationship Manager], tblImport.[Premium Type]
FROM tblImport;

I can't seem to grasp the table joining through an append query.

Would you be so kind as to explain in simpler terms please?

Answer
Use Query Design mode. Create a SELECT query that joins the import table to accounts group table on account name as I suggested. Test the query, then turn it into an Append query.


Hope this helps,
Scott<>
Blog: http://scottgem.wordpress.com
Website: http://www.diamondassoc.com
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

Using MS Access

All Answers


Answers by Expert:


Ask Experts

Volunteer


Scottgem

Expertise

I can answer almost all types of questions relating to Microsoft Access usage and application design. My strengths are database and interface design.

Experience

I've been designing databases for over 25 years working with dBase, FoxPro, Approach and Access.

Organizations
Author of Microsoft Office Access 2007 VBA
Techncial Editor for Special Edition Using Microsoft Access 2007 and Access 2007 Forms, Reports & Queries From Que Publishing

Education/Credentials
Brooklyn College BA

©2016 About.com. All rights reserved.