Importing Spreadsheet

[Guest]

Hi,
I'm designing a database for updating price files. I'll have two main
tables: tblParts and tblXref. tblParts will be our part numbers etc that we
use in our existing system. tblXref will contain our suppliers/competitors
part numbers etc with a PartID link back to tblParts.
Here is snapshot of the spreadsheet I want to import.
Our Code Supplier's Code
C100 0001001080
C100 0579200001
C100 0579200003
C100 0579200018
C100 0579201001
C100 0579277001
C100 25686003
C100 25686004
C100 25686030
C100 25686038
C100 H093
C100 HP003
C100 SG001
C100 SP079
C100 SP079
C10162 48763
C10754 40859
C10754 55871
C10754 88320

As you can see, the C100 has many alternatives. This occurs regularly
throughout the spreadsheet. The first column here effectivetly becomes the FK
in tblXref.
How would I go about importing this data considering "Our Code" will be our
part number in tblParts and "Suppliers Code" will be SuppliersPartNo in
tblXref.

tia, Donk

 

[John Nurick]

Hi DonK,

I must be missing something. Surely tblXref needs at least the following
fields (* indicates that the field is in the primary key):

OurCode
SupplierID*
SuppliersCode*

Otherwise you'll be in a hole if two suppliers ever use the same code
(whether for the same or different products).

That said, to import the data from the worksheet:

-Create tblXref.

-Create a linked table linked to the worksheet.

-Create a Select query that returns the two fields, aliased to the names
you've used in tblXref. In query design view, open its Properties sheet
and set "Unique Values" to Yes (or use the DISTINCT keyword in SQL
view). The result should be like this, and it will omit the duplicate
records:

SELECT DISTINCT [Our Code] As OurCode,
[Supplier's Code] As SuppliersCode
FROM MyLinkedTable;

-Finally, create an append query that gets its data from the above and
appends it to tblXref.