connect a primary ID that represent related IDs to a main connections table

[ElcoyotldeAztlan]

Ok Guys I think I got a real hard query to create lets see if we can solve this

So I have been requested to created this database base off of one excel sheet, i'm in the process of adding this data to make sure the data fits in with the database ER diagram.
I have had to restructured/add other tables along the way because the original design had its errors.

To cut to the chase,
This database focuses on over 3000 entries and some have the same exact related fields while others don't. It is split into 4 sections with their own related tables. what I'm trying to do is connect a primary ID that represent related IDs to a main connections table that connects the other 3 sections IDs
(I know this may be bad in design I'm not the one created it but lets see what we can do)

I have manged to split the data into it's sections and match the IDs up simply copying and pasting the results of SQL into each ID field. So 4 sections can be done easily , however it gets tricky when I want to connect all 4 sections into one table.

Because this db focuses on the 3000 entries the connecting table needs to be base on all 3000 entries now it needs to connect with all the other sections IDs which one can do manually but to insert 3000 entries is too long.

The probably is matching the other sections because their own IDs are base off of their related tables so im having table finding a way to match the section id with the connections table, I hope i'm making sense,

Let focus on one section.
Basically One table which has 5 related tables is connected to this sections table by pure ID fields that make up the primary ID, this Primary needs to be connected to the main table and match up with the 3000 entries.

I have inserted the Excel data sheet as a table match all the column names with the field names I able to match each column with the IDs using SQL and 3000 entries matching the IDs from a table comes out great, now the issue is how can one section table that represents 5 tables that are all connect by IDs
using a Primary key ID, How can this Primary key match another ID

I'm hoping this is making sense.

Thanks for your time reading this

 

[plog]

Not a lot of sense. I suggest you post 3 things:

1. A plain english description of what your organization does. Pretend its career day and you are giving a presentation to an elementary school.

2. A plain english description of what real-world process/system this database models.
Don't use database jargon, instead tell us what data the real world produces and what you need to do with it in terms of reporting.

3. A screenshot of your Relationships Tool. Set that up and let us see all your tables, fields and how they connect.

 

[ElcoyotldeAztlan]

I think I'm explaining it to hard, it's more simple to show,
I drew a basic idea on paint

 

[ashleedawg]

It's tough to picture your setup without a better idea of the data you're dealing with, but it sounds like what you need to combine the Four ID tables into one, so you can compare it to another table. If so, that's where UNION queries come in.

I assume the reason you have 4 table with ID's is because otherwise there would be duplicate ID's for different people? (Otherwise, they should just be in one table.)

If there would be duplicate ID's then you'll need to make them unique, perhaps by adding the table name to the ID, and combining the 4 tables:

select "Table1_"&id1 as id,names from table1 union select "Table2_"&id2 as id,names from table2 union select "Table3_"&id3 as id,names from table3 union select "Table4_"&id4 as id,names from table4;

Union queries don't have a Design View. Create a new query in Design View, but don't add any tables to the new query. Click 'Union' on the toolbar, and paste in the above SQL.

If you don't need those "extra unique" identifiers then the SQL is simpler:

select id1 as id, names from table1 union select id2 as id, names from table2 union select id3 as id, names from table3 union select id4 as id, names from table4;

Save the query with a name like "qryCombinedIDtables" or something. Then you can make another query joining qryCombinedIDtables to the table that will give you the results you need.

On the other hand, it's hard to tell if that's what you need at all. Your output example doesn't include any Names... so you just need to get the matching ID_Connect for each ID#? If so, the table in the middle already has that information, and the other 2 tables are irrelevant.

Less info, but with sample data would be helpful. (Not your real data of course.)

 

[ElcoyotldeAztlan]

Thanks I think this puts me in the right direction however it looks like the SQL is only connecting the 4 tables to the "ID Connect" table, from there I would like to connect it to the main data table with the 3000 entries that, Keep in mind the ID Connect table already has all the IDs that reflect the 4 related tables.

 

[ElcoyotldeAztlan]

Here is a better example four sections of Data that all are related to 3000 entries, (please see uploaded image)

Of course someone could go one by one to match the data however, lets reduce the time by using a query what I have done is used the excel data to match the Name of the data with the IDs I have created, so for example

Car field (from Excel data i imported in as a table)
Pickup
Pickup
Sedan
Van
Sedan
etc...

is able to match with

Carfieldtable
ID CarName
1 Pickup
2 Sedan
3 Van
4 Sports

after matching the two tables I get a result like so

1
1
2
3
2
etc... (so I would get 3000 records that range from 1-4)

from here I can copy that data and place it in my section 4 table in the S4_DataID1 field I would then do the same for the other 3 tables with their match field from the excel sheet table. so all 4 fields would make up the Section_4_ID field, I realize now I can do this if I make sure every field has 3000 IDs in each Sections IDs they can all match up to the excel data and quickly finish the database, however my issue is I would still have duplicate ids rows in my sections, (I reduced duplicate rows to 600 in section 4) however honestly now I'm wondering if that would be a major issue if all my rows represent a number from there data tables maybe that could solve my problems thoughts???

Any dropdown menu from a form data would still show one specific data.. (I wont be showing any IDs just the non duplicated data from the related tables like the Carfieldtable shown above

any issues?

 

[ashleedawg]

hmm, I'm still not clear what you're dealing with... (some db's are harder than other to describe over a forum!) I made a quick Excel sheet showing what I *think* you mean. If that's what you're heading towards (I called it tblVehicles), with an attached combo box then you're pretty close to the solution!

If I'm on the wrong track, it might be easier if you update and re-post the attached Excel file. (In your drawings I wasn't sure which were the Field Names and which were the Data, and what you have versus what you need.)

 

[ElcoyotldeAztlan]

Wow I would 1st thank you for making the excel sheet,

I think we are in the right area the only thing is and this my fault for not explaining, the other sections would be like Manufactureidtable, Countryidtable, YearidModels, Carfield, those would be the 4 sections all different data and those four would have different related tables for example CountryName would have ContinentName, RegionName, countryName, State/ProvinceName, CityName all those tables would not have duplicate data, So the id and data would be like 1 =USA 2 = France etc... however those can IDs can repeat in the Countryidtable (because there could be different Car plants in many states in a country so the Coutryidname would show

Countryidtableids Countrynameid citynameid
1 1 (USA) 1 (Detroit)
2 1 (USA) 2 (Georgetown)
3 3 (Mexico) 5 (Hermosillo)

(other related field ids would be in this table also)

so i would like to match the Countryidtableids with the 3000 cars in the Main table with the other section Ids with each sections Ids, In my lastreplay I believe solved my issue, using a query I can use the Excel data table (which has all the 3000 rows) to match the 3000 entries with ids from each name field my output would be ids if I selected the ids with name fields on the excel sheet all 3000 records would show with the match Ids then I can just copy and paste those records.

so my comboboxs/dropdrow menus would not have duplicate data because they would show just the data names not the id fields only the people programming the table would see the duplicate id rows but each one represents a car in this case