Link query expressions to tables

[marstev]

I am importing an Excel file into my database, but the columns do not match my fields. My database tables have separate "Name" and "EmployeeNumber " fields, while the excel file combines the two as "Name - EmployeeNumber" .

Example: Following a direct import from an Excel file to Access, I am splitting out the Employee Number "C1234" from "John Smith - C1234" using an InStr query expression. The difficulty I now face is establishing a relationship between the result of this expression, and my other tables in order match up with data in other tables.

At this time, it is not possible to have the Excel file created in the format I need, as it is the output of another program. I have limited capabilities in the other program, and this is why I am attempting to create a parallel database, to be updated with periodic imports through the Excel outputs.

[NeoPa]

Try this :

1. Import the Excel data into a table that has very few restrictions on the data.
2. Run an APPEND query based on that table that extracts the name and ID data into separate fields of the next table, where you need the true data.

[marstev]

Thank you for that quick reply! Seems simple enough, I will try that out in the morning when I return to work. Hopefully, this does not get too complicated. Being new to databases, I don't want to get lost in my first project.

I do already have a separate table set up within my database for this import from Excel, and I previously tried just manipulating the data prior to import using the Text-to-Columns feature in Excel. This presented problems since subsequent imports had to be identical to the previous in order to use a common table. Is it possible to set up the import to separate the data for me on the way in? That would save me from having to set up an additional query and table for the appended data.

[NeoPa]

Not really no.

You either fix it up in Excel beforehand or in Access afterwards.

As it's a database and you're already coding this there I expect it's easier to handle it there.

[marstev]

I have been using the append query approach to this, and it has been working well, producing the desired results. One issue I run into is that it creates duplicate entries every time I run it, so i delete all the old entries in the table first. Would I have been better served to crete a make table query? What is the difference between the two? How would I avoid duplicate entries in the new table?

[NeoPa]

Marstev:
I have been using the append query approach to this, and it has been working well, producing the desired results. One issue I run into is that it creates duplicate entries every time I run it, so i delete all the old entries in the table first.

That's exactly how you should be using it. Well done.

Marstev:
Would I have been better served to create a make table query?

No. Definitely not.

Marstev:
What is the difference between the two?

I think you've already seen the difference. A Make Table query uses the existing data to determine the setup of the table on the fly. An APPEND query uses whatever you've already designed as the table. Using a Make Table to start your design is a good idea. Using it without making subsequent changes to make it how you need it is not.

Marstev:
How would I avoid duplicate entries in the new table?

If "the new table" refers to what you INSERT INTO then you have already found the answer to that. The SQL for that is simply :

DELETE FROM [TableName]

Where TableName is the name of your table.