Combine 4 tables with shared keys but different data elements

[Number1CatMom]

Hello! Thanks in advance for reading this.

I am trying to combine 4 tables with shared keys but different info in each table. The tables are:

B-Contract Info VALUES
F-Contract Attributes VALUES


E-Review VALUES
I-Inventory Notes VALUES

These all share unique ID #s. I created a new table called Master Table. Is there an easier way to do this than copy and pasting every cell and manually updating that will pull together the data as well as the input masks/calculated fields/etc?

THANK YOU for any help you give.

[ssanfu]

What are "shared keys"?
What are the fields in the tables?
Are the tables linked (related) by PK/FK fields?

Is there an easier way to do this than copy and pasting every cell and manually updating that will pull together the data as well as the input masks/calculated fields/etc?

Depending WHY you want to duplicate data, I would use append/update queries.

[Number1CatMom]

Thanks for the reply!

1 - shared keys = primary key
2 - see below
3 - yes, they are linked by a primary key

I don't want to duplicate data. The data in the 4 tables is all about contracts but was broken out into different tables by the creator (not me) and it's made things more difficult than necessary. I am trying to combine the like-data into one table to simplify things.

The current 4 tables:

B-Contract Info VALUES
ID (Primary Key)
NW Key
List
Contract Source
Concatenated Number (calculated field that combines task order and contract numbers)
Original Contract #
Prior Task Order #
...on and on with contract information for 37 columns total

F-Contract Attributes VALUES
ID (Primary Key)
Review Key
Contract Key
Attribute Type
Record Date
Current Period of Performance Start Date
Current Period of Performance End Date
...15 columns total

E-Review VALUES
ID (Primary Key)
Contract Key
Review Start Date
Review End Date
Fiscal Year of Completion
...36 columns total

I-Inventory Notes VALUES
ID (Primary Key)
Contract Key
Review Key
Note Date
Note Type
Internal Note
External Note
Note Length

All of this data pertains to contract data. I would like it in one table beginning with the items in contract info followed by the other tables to the right of that. Like this:

Contract Info Contract Attributes Contract Review Contract Notes

Thanks again!

[ssanfu]

I am trying to combine the like-data into one table to simplify things.

All of this data pertains to contract data. I would like it in one table beginning with the items in contract info followed by the other tables to the right of that.

That means you will end up with a table that has 96 or more fields.

I am having a hard time understanding your table structures.
Q) All of the tables have a field "ID (Primary Key)". So one record (Contract) is linked by "ID" across 4 tables?
Q) How do you get (what format:CSV, txt, xlsx,?) the data in the 4 tables?
Q) You have to import the data to your dB? Or you delete last week/month dB each time?

You can add the fields from the 4 tables to a query to generate a view of each contract - its like creating a table with all of the fields. But I think the links will be a 1-to-1 relationship instead of 1-to-many.

Why not attach a copy of your db - just enough records (5 - 10) to show the issue? That way we can see exactly what you are dealing with, your data structures , ... (Can be dummy data)




BTW, I recommend you adopt a naming convention that does not allow embedded spaces in field or object names.
And no special characters (#). Use only alpha numerics. This will save you from many syntax errors/problems.


PS: Welcome to the forum.....

[Number1CatMom]

That means you will end up with a table that has 96 or more fields.

I am having a hard time understanding your table structures.
Q) All of the tables have a field "ID (Primary Key)". So one record (Contract) is linked by "ID" across 4 tables? - Yes
Q) How do you get (what format:CSV, txt, xlsx,?) the data in the 4 tables? - Data Input forms in Access
Q) You have to import the data to your dB? Or you delete last week/month dB each time? - Data is added using forms and is stays in there forever

You can add the fields from the 4 tables to a query to generate a view of each contract - its like creating a table with all of the fields. But I think the links will be a 1-to-1 relationship instead of 1-to-many.

Why not attach a copy of your db - just enough records (5 - 10) to show the issue? That way we can see exactly what you are dealing with, your data structures , ... (Can be dummy data)




BTW, I recommend you adopt a naming convention that does not allow embedded spaces in field or object names.
And no special characters (#). Use only alpha numerics. This will save you from many syntax errors/problems.


PS: Welcome to the forum.....

Thanks for the welcome! And I really appreciate your time and help. Attached is a dummy database Dummy_Data3.accdb

I was limited in what I could include due to size constraints. You'll see 3 of the tables with contract/contract review information. There is a 4th table (Contract Attributes) that I deleted out to make the file smaller but it is the least involved of the tables.

The data is entered into these 4 tables through forms (not shown). The database also has many lookup tables (not shown). The reason I want to consolidate these tables into 1 is to make reporting easier and it's a multi-user database so for ease of use.

There are a lot of input masks (not shown) and such included that I don't want to lose and manually fix by joining these manually.

The JOIN LEFT and JOIN RIGHT codes seem like they may do the trick but unfortunately I am not a SQL guru (YET!).

I could like these 4 tables joined into one. I hope things are more clear now. Any ideas?

[ssanfu]

How do you tell/how do you know which record in TableBContractInfo is related to which record in TableEReview and which record in TableIInventoryNotes?

The data is entered into these 4 tables through forms (not shown).

Do you get contract info on paper and someone enters the data into the dB?




----------------
FYI, if you do a "Compact and Repair", then Zip it, the dB can be a bigger.... 2MB

[Number1CatMom]

Yes that's correct. If a contract meets the criteria for a special review (cost, type of work, etc) then it is entered in. Thanks!

[ssanfu]

I been looking at the dB you posted. I cannot tell which records in the tables are related.

Normally, there is a "master/main" table that is the basic info for your project with a primary key field - usually an autonumber type field. Other tables that are related have foreign key fields that are long integer type.

So in the 3 tables you provided, lets say the "master/main" is "B-Contract Info" (I would name this table "B_ContractInfo")
The PK field would be "ContractInfoID_PK". In any other table that has related records, there would be a field (the foreign key) named something like "ContractInfoID_FK".
Again this is my naming convention.

PK -- links to ---> FK

table "B-Contract Info" ("B_ContractInfo") (this is the "main" table)
---------------------
ContractInfoID_PK (autonumber)
other fields


So table "F-Contract Attributes" ("F_ContractAttributes") would have
----------------------
AttributesID_PK (autonumber)
ContractInfoID_FK (Long) link to table "B_ContractInfo"
other fields

table "E-Review" ("E_Review") would have
----------------------
ReviewID_PK (autonumber)
ContractInfoID_FK (Long) link to table "B_ContractInfo"
other fields

table "I-Inventory Notes" ("I_InventoryNotes") would have
----------------------
InvNotesID_PK (autonumber)
ContractInfoID_FK (Long) link to table "B_ContractInfo"
other fields



Try and do a "Compact and Repair", then Zip the dB. Max size of a Zip is 2MB.
Or if you don't want to post the whole db, put it in a drop box and PM me with the access info.