How to link various tables using a single common field

[Maxjoo]

I am trying to link 15 tables using a common field, OrderId, in order to create a form/report to print specific data from the tables. Thank You in advance for your help!!!!

Because the tables I was given to work with already have data on them (very incomplete data, some with 9 fields (or less), some with 11), I am trying to separate some common fields and place them in a new table linked to the 15 other ones.

This is an example of what I am trying to do:

table1; table2; table3; table4...
|
|
|
Orders table

Each table contains (should contain...) 11 columns/fields; the fields I want to link (and place in Order table) are: Building Name, Crew Leader, Work Done, Date Ordered, Done Date. I know I have to place a primary key to index the records on the tables; it is an AutoNumber. I stopped here, because I got nothing in Orders table...

[madpiet]

Fifteen tables??? What does you schema look like?

to answer your question, though, the primary key in the parent table would be Autonumber, and all the child tables with foreign keys to relate back to that would have a Long Integer column as the foreign key. Then you can join them (Make sure the index on the child tables is NOT unique on the foreign/join key.)

[kd2017]

Keep in mind there is a maximum of 16 joins in a query, sometimes less.

As mentioned you may need to reconsider your db design, or you may have to use temporary tables.

[madpiet]

Could you post your table structure or a copy of your database with only a few (literally 2-3!) records per table? Something here doesn’t sound right… and it could be a “fix in now for cheap or fix it for a lot later” thing

[orange]

Max,

More info required. It would help if you could provide a 2 or 3 line overview (30,000 ft level) of what your database is intended to support. And, as has been requested, the tables involved and the linkage you would like with this "common field".

[madpiet]

I agree with Orange. Once I understand how the pieces should fit, I can bash out the tables. Other people here are way better at UI stuff than I am. But a solid design will make your life infinitely easier later!

[Maxjoo]

Because the tables I was given to work with already have data on them (very incomplete data, some with 9 fields (or less), some with 11), I am trying to separate some common fields and place them in a new table linked to the 15 other ones.

This is an example of what I am trying to do:

table1; table2; table3; table4...
|
|
Orders table
OrderID (Field linked to the source table)
Building Name
Crew Leader
Work Done
Date Finished
ID (Field linked to the source table)

Each table contains (should contain...) 11 columns/fields. I know I have to place a primary key to index the records on the tables; it is an AutoNumber. I stopped here, because I got nothing in Orders table...

[Maxjoo]

Because the tables I was given to work with already have data on them (very incomplete data, some with 9 fields (or less), some with 11), I am trying to separate some common fields and place them in a new table linked to the 15 other ones.

This is an example of what I am trying to do:

table1; table2; table3; table4...
|
|
Orders table
OrderID (Field linked to the source table)
Building Name
Crew Leader
Work Done
Date Finished
ID (Field linked to the source table)

Each table contains (should contain...) 11 columns/fields. I know I have to place a primary key to index the records on the tables; it is an AutoNumber. I stopped here, because I got nothing in Orders table...

I cannot upload the file, is too big for the requirements...

[davegri]

Are table1; table2; table3; table4... constructed identically? Is there any logical relationship between those tables?
Are all the records in table1 for the same building? (and all in table2 for another same building, etc. for keeping track of 15 buildings)
Is your end result essentially combining the 15 tables into a single new table?
Is this a one-off procedure, or will it be on-going over time?

[Maxjoo]

Thank You davegri for replying so fast!!!!!

Your questions practically resume my dilemma with this db. I took the data from a HUGE Excel file (15 tabs/building), between 1-2 megabytes; I was able to reduce the size of the file to 85kb by eliminating the format that was applied to it, colors, letter style etc. Then, I took each tab to create an access table for each excel tab/building. The problem is not all the tables/tabs have the same number of columns. I decided to work with the one that has most fields. I do not want to combine the tables; I just want to use the fields on them and to add new records on each one. How can I make them uniform, without losing data, in order to work with them. This task is for a client...

[orange]

Max,

Access is not Excel--they are built on different object models and serve different purposes. To help you, readers need to understand what you are trying to do -and by this I mean at the "business process" level NOT vba code or code level. Based on Davegri's questions/comments please expand your description and describe to us WHAT exactly you have and what you need as a result.

[madpiet]

Thank You davegri for replying so fast!!!!!

Your questions practically resume my dilemma with this db. I took the data from a HUGE Excel file (15 tabs/building), between 1-2 megabytes; I was able to reduce the size of the file to 85kb by eliminating the format that was applied to it, colors, letter style etc. Then, I took each tab to create an access table for each excel tab/building. The problem is not all the tables/tabs have the same number of columns. I decided to work with the one that has most fields. I do not want to combine the tables; I just want to use the fields on them and to add new records on each one. How can I make them uniform, without losing data, in order to work with them. This task is for a client...

Say you add a column to the table so you can specify the Building the record belongs to, then can you append all the spreadsheets to a single table?

If the data's not crazy confidential, I can do it. (I'd likely do it in PowerQuery, because the field types aren't crazy inflexible like they are in Access), and then I'd import that result. (Just Append all the results together, once you add the information about building.)

[mike60smart]

Hi Everyone
I have been in contact with Max and he does not want to accept my help with trying to sort out what he calls a databasae.

I attach his attempt and he does not want to put all 15 tables into 1 table.

All of the 15 tables are a mess anyway.

He has multiple records with finish dates but no start date etc

He has gone down the normal route of spaces in names, ID PK for every table, Lookup fields in tabes etc..

He is expecting to be paid for this database


See attached.

[Micron]

I hope you had his permission to post a db given to you in a private message.

[Micron]

@Maxjoo; you need to understand db normalization. Suggest you save yourself a lot of headaches and review these
https://www.accessforums.net/showthr...773#post521773