Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ms access import excel to access table

Posted on 2011-02-28
4
Medium Priority
?
255 Views
Last Modified: 2012-05-11
I get a csv file from engineering for each job batch we do.  It is in the format:

TAGS        JOB               SUFFIX        BATCH
SB207       2010-0064     2P0              802
SB208       2010-0064     2P0              802
SB210 . . . . . . . .

I have a table in my access db called TAG:

ID     BatchID    Tag    
1       1               SH219
2       1               DWC301
3       1               SB219
4       1               SG213
5 . . . . . . .

I have tables for Job, suffix and batch.  They are all linked on ID's - Suffix table has JobID, Batch table had SuffixID, Tag table has BatchID.

I need to append the tags table with non-duplicate tags from the excel file.  I linked the excel file and tried to figure out an append query.  How do I use the job, suffix and batch fields in the excel sheet to append the Tag table and have everything linked correctly.

Job -> Suffix -> Batch-> Tag

Thanks for your help.
0
Comment
Question by:johnmadigan
  • 3
4 Comments
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 1500 total points
ID: 35002952
You have to do this in steps, if you want your items to be properly related. What is the relationship between the items?

For example, does a Tag belong to a Job, Suffix or Batch? Can a Tag belong to more than one Job? Can a Job have more than one Tag/Suffix/Batch?

0
 

Author Comment

by:johnmadigan
ID: 35017878
I attached a screen shot of the relationships.  One Job can have many suffix

2010-0064  000
2010-0064  1P0
2010-0064  2P0

Different jobs may use the same suffix - once a new job is started they begin the Suffix numbers

2010-0064   000
2010-0055   1P0
2010-0079   000

For each Suffix number there can be multiple batches

2010-0064  1P0  001
2010-0064  1P0  002

The batch numbers are reused as well - on each new suffix for a job the batch numbers are reset and started again from 001.  So the Batch numbers are used over again.

Then for each batch we have a set of Tags - there are no duplicate tags for a specific batch - but you may see tags repeated on other Jobs

2010-0064  1P0  001      
TAGS
102A
102B
101A

2010-0075  2P0  002
TAGS
102A
102B
101A

I have the excel file that I need to append to my Tag table.  The Job, Suffix, Batch and Tag table are related by ID's.  My excel spreadsheet does not have ID's but values. I am tying to set up a query to find the correct ID's to determine which BatchID the excel Tags need to be related to when appending the Tag table.




Relationships.doc
0
 

Author Comment

by:johnmadigan
ID: 35018974
I was able to set up a query to get me the related infromation for the excel file:

SELECT CustomerJobSummary.ID, Suffix.ID AS SuffixID, Batch.ID AS BatchID, CustomerJobSummary.OldTelnetNo, Suffix.Suffix, Batch.Batch, DrImp.TAGS
FROM DrImp, (CustomerJobSummary INNER JOIN Suffix ON CustomerJobSummary.ID = Suffix.JobID) INNER JOIN Batch ON Suffix.ID = Batch.SuffixID
WHERE (((CustomerJobSummary.OldTelnetNo)=[DrImp].[JOB]) AND ((Suffix.Suffix)=[DrImp].[SUFFIX]) AND ((Batch.Batch)=[DrImp].[BATCH]));

I end up row having the Tag from the spreadsheet and the corresponding Table ID's.

I now need to append these tags to the tag table using the BatchID - but I need to make sure that there are no existing Tag/BatchID already in the table.

Any suggestions?
0
 

Author Closing Comment

by:johnmadigan
ID: 35690757
Project was put on the shelf
0

Featured Post

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Question has a verified solution.

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

Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
Suggested Courses

971 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