Connecting two tables from 2 different Excel data sheets (1 Viewer)

[ElcoyotldeAztlan]

Hello Everyone

I have question about filling in ID numbers to connect two tables to together using a Many to One Relation ship. I have been working on a database with data I was assigned to import into certain tables from here I'm supposed to make it all connect. I often use the query design to easily get IDs from an imported Excel sheet which I broke up into tables, with the query results I can then simply copy and paste into each Id fields which has been great However i have can across an issue. On a certain section of the data base I'm getting data from another excel table which means the same amount of rows is different from the new Excel sheet.

I have two tables
One has information about Cities and different time periods
For Example
There are over 2000 cities however a city can have too different time periods thus creating two separate identities City A in 1950 and City A in 2000 and so on with the other cities and time,
This table then has a field to connect GPS information from another table.
The other table that has the GPS (coordinates) information is also has a field to connect back to the 1st table.
the issues is there is only one set of coordinates for each city. So the amount of GPS coordinates come to around 200 (which means there are only 200 cities in reality) ,but they need to connect to the 2000 different time periods. Is there a way to connect the first table with the other table using the city name field as a way to connect the two? Or is this just bad design and needs a major redesign?

In the past I was able to get IDs because of the 1st Excel sheet was split into many tables and I was able to match those IDs because there was the same amount of rows (data was coming from one Excel sheet) now with this new excel sheet I think its more complicated,
What are your thoughts

Thanks

 

[isladogs]

I can't quite grasp the details of cities and time periods but in essence your issue appears to be that the data imported from Excel isn't normalised and needs to be split into separate tables after importing into Access.

My recommendation is that you manipulate the data into a normalised format in Excel BEFORE importing.

If you have Excel 2016 use the built in Get &Transform feature.
For Excel 2010/2013 download the Excel Power Query addin from MS.

Both of these are very powerful tools that allow you to do amazing things with data without altering the original dataset. Once you've played with it for a while, you'll find it FAR quicker.

If only Access had something similar!

The alternative would be to write a procedure in Access to handle each step systematically. Up to you to decide if its worth the development time needed.

 

[ElcoyotldeAztlan]

The Excel Spreadsheet has multiple columns, certain columns make up different tables all that data is coming from one sheet (sheet1) and that not the problem because it's coming from one sheet, however one of my tables is getting its data from sheet2 (same spreadsheet just on a different sheet, at the bottom of excel there are sheet tabs)

 

[isladogs]

OK - I think I was right in what I said previously.
Have a look at my previous suggestions

 

[ElcoyotldeAztlan]

Thanks for your help i'm gonna try this, I was busy the past couple of days so I didnt have a chance to try iy