Importing data to tables using IDs

[marvo]

Hi
I have a .csv file to import - it's correctly formatted and contains all the data VALUES I need.
However, like a good boy, I have designed my access tables IDs - primary/foreign keys to other tables.

So for example, I have a database that manages sports fixtures
I want to import a csv of all the new fixtures for this season
My csv contains Values like:

Date, Home Team, Away Team, Time, Pitch
23/09/2019, Cambridge 1st XI, Oxford 1st XI, 13:00, The Sportsground

but my Access Table has
Date
HomeTeamID (which then looks up another 'Teams' table based on the ID)
OppoID (which then looks up another 'Clubs' table based on the ID)
Time
PitchID (which then looks up another 'Pitches' table based on the ID)

So in short...I can't import my data, because the csv does not have these IDs


Anyway to solve this?
Thanks

 

[theDBguy]

Hi. You would either fix the CSV or fix the data after you import them into your tables. If you have Referential Integrity enforced, you won't be able to assign a Foreign Key that doesn't exists. However, you should be able to leave the field blank. If so, you should be able to fix it with a query to enter the correct ID.

 

[pbaldy]

First either link to the csv or import it into a temp table. Then create an append query that joins that data to the teams and pitches tables, enabling you to return the ID fields instead of the names.

 

[marvo]

SOLVED

Thank you. All your ideas gave me inspiration.
A very elegant solution in the end that I'm really happy with.

Imported the csv into a Temptable that included fields for all the values and all the IDs - even though IDs did not exist in the csv

Built a query that joined this Temptable to the various 'lookup' tables

But here's the kicker -
The Query Designer automatically joins the tables based on ID when you add them to the query
so -
1. Delete the joins that the query designer created
2. create a join between fieldnames e.g. join ClubName to ClubName (Not IDs)
3. On the 'Join Properties', select option 2 - Include all records from Temptable and only those records 'lookuptable' where the joined fields are equal
4. For your query, select 'Values' from the Temptable and IDs from the LookupTables

And hey presto!!

Once the query was working, change it to an Append Query and ta da!

Thank you all!

Editors Note: Entirely dependant on PERFECT data quality - i.e. if the ClubName in the temptable is not spelt exactly the same as in the lookup table, then the join can't work

 

[pbaldy]

Glad you got it sorted!

 

[theDBguy]

SOLVED

Hi. Congratulations! Glad to hear you got it sorted out. Good luck with your project.