Question Import data to multiple related tables

[Cobo]

Hello,

I plainly don't know how to import data to an Access database while maintaining all relationships intact. Of the data imported, parts of it go to different related tables.
The database structure is as follows:


I have a program that extracts all the info to input, so I can really tweak at pleasure, either to a text file, excel, or whatever.

My main problem is that as you can see, I have 1:N relationships... and I don't know how to maintain that good while importing the data.

For example, I retrieve the data for a match which can have 0 or lots of goals. The I have to import the match data (each match is identified by an autonumber field "MatchID") and the goals data...

1. How could I correctly send each data to its proper table and more important...
2. How could I set each row on the Goals table relate to the correct MatchID number?
Of course, the same happens to related Players, Cards, etc...

I've look through Internet without success because most of them concentrate on importing data to just one table or in any case, import data to a new blank database that you go on creating the tables as you import the data. I need to continuously add data to the database, so any link or help you can provide would be much appreciated.

Thanks for you help! Cheers!

 

[vbaInet]

You're talking about a rather complicated issue here. It would require a complex code to get this working because you will be trying to match autonumber fields. The only way to match these is to use a Dictionary or Collection object and logically add data to your tables.

By the way, your Matches tables has quite alot of columns. Looks like it could do with some normalisation.

 

[Cobo]

You're talking about a rather complicated issue here. It would require a complex code to get this working because you will be trying to match autonumber fields. The only way to match these is to use a Dictionary or Collection object and logically add data to your tables.

By the way, your Matches tables has quite alot of columns. Looks like it could do with some normalisation.

Thanks a lot for answering,

If I have to program anything I think I'll just go and make a little C# then. I just thought of Access because I was given the database in that format. But if automating input is going to be a programmatical task, I think I prefer other options then.

Thanks for everything anyway!

 

[vbaInet]

Yes, that's right. There's no automation for maintaining autonumber fields relationships whilst importing. It would need some programming. Like you said, best to do it in the language you're most comfortable with.

Have fun with that!!

You're welcome.