mdb Structure Assistance

[Guest]

I'm not exactly sure how to phrase this question, and I'm thankful for all of
the previous help I've received on setting up my database it's really been
educational.

Recap: I collect lists of parts used by various companies, I then convert
those part numbers to a universal part number. I basically have one table
for part numbers, one table for universal part numbers, and a table to link
them because it's a many to many relationship.

tbl-Parts
PartsID - pk
PartNumber
PartDescription

tbl-Universal
UniversalID - pk
UniversalNumber
UniversalDescription

tbl-PartsUniversal
PartsID - fk
UniversalID - fk

I have recently received an Excel spreadsheet this is a type of master list
that I have imported and made a new table.

tbl-MasterList
MasterID
PartNumber
UniversalNumber
Description

I receive the lists of Parts used by the companies in various Excel
spreadsheets, I would like to be able to import a column of part numbers from
the spreadsheets to a table like the tbl-Parts and have it look to the
tbl-MasterList and then populate the tbl-Universal so I can then query the
tbl-Parts and tbl-Universal for a complete list and then base a report on
that query for a complete list of parts and their matching universal numbers
in a printed format.

Can someone give me some design layout / structure advice on this type of
setup.

Thank you.

 

[Guest]

You just need 3 append queries that use the imported table as the source and
your existing tables as the targets.
Create a new query. when it asks for a table, give it the name of the
Imported table.
Select Append as the query type. It will ask you what to append table to
append to.
Select one of the 3 tables. Then for each column in the imported table you
want to put in the target table, select the appropriate field in the target
table.
Repeat 3 times
Shake well
Serve over crushed ice.

 

[Guest]

Thank you, I'll give that a try.

You just need 3 append queries that use the imported table as the source and
your existing tables as the targets.
Create a new query. when it asks for a table, give it the name of the
Imported table.
Select Append as the query type. It will ask you what to append table to
append to.
Select one of the 3 tables. Then for each column in the imported table you
want to put in the target table, select the appropriate field in the target
table.
Repeat 3 times
Shake well
Serve over crushed ice.