Combine 2 tables

[musclecarlover07]

How to I combine 2 tables with the same primary key to make one table? I have seen several thigns but none have helped. I seen inner and outer joins. All 4 of my tables have the same primary key. I just want to combine all the tables to make one table. I have a form with subforms but if I can combine all the tables and work off one rather then 4 I will be happier. Every week to 2 weeks I uplad new data from excel. How would I after I add the new data combine all tables to one super table?

 

[Anakardian]

If you need to add new records you should be able to use an append query.

If the records consists of both old and new records you need to figure out which records to add.
I solved a similar problem by using an extra field in the import table. The exta field is a yes/no type. With an update query I marked the field if the record already exists and then use an append query to get the new records into the main table.
The last action is to delete all the records in the import table to avoid cluttering the whole thing up.

 

[musclecarlover07]

Its not so much as adding new records. ts combining to records. Each table has the same Primary key and a few of the other fields are the same but they all have different data. I just want to combine them so they all are in one table/query. Well when I import the table any Primary keys that are duplicate are lost cause of the Primary key rule. If I was able to combine the records in excel I would. But one table may have a primary key that another table may not have.

 

[Anakardian]

If the primary key is the same, you should be able to use an update query to add that data to the primary record.

If there is no primary key in some of the data, how do you figure out where it belongs?
You should be able to use that logic to build an update query that put the data in right place.

 

[musclecarlover07]

What I mean for some of the data is they dont match on the tables example:
All tables have GBL (Primary Key)
3 Tables have the following:
First, Last Names, SSN
1 Table has Contact info
1 Table Has storage info
So how would I go about this update query? How does it work? Will any data be lost?

 

[Anakardian]

First you need to look at your data and figure out how it is all related.
Once you got a good mental map try to draw it on a piece of paper or a handy wall or whiteboard.

From what you have shown so far I am guessing you need to split it into several tables to get a good design. Don't worry about how you already have your data but be more concerned with only having information once in your database.
If you are coming from excel like I did it is a tough job to get your head around but well worth it in the end.

While I haven't seen all the data you need to record, it seems you have personnel information recorded in three different places. This should really go into a single table. If there is a need to distinguish between them you may need to add additional tables.
You then make a relationship between the tables to make the information connected.
I have attached an example of how it could look.

While this does not directly answer your question about the update query it is a requisite for having it work in the first place.

If you need something more specific you will need to bring up some more information.

 

[musclecarlover07]

I don't want to split into multiple tables. I just want one. When I view the form I want the form to didplay the one table. When our reports are pulled from the website it pulls three different reports. I can show you the tables (With no info) and the fields we work with. Like I said I have 4 tables. Customer_Data; Storage_Data_; TSP_Data; JPPSO_Data. THey are names of the reports that are pulled. I want to merge them all to one. I already tried relationships and tried doing a query that way but it would give me duplicate data. I was told then to use sub forms an reports. Now I just want to take the Info from Storage_Data and Combine it with the data on Customer_Data. I want it to look an say I have GBL AAAA1111111 on Storage_Data am look for it on Customer_Data. If there is the same GBL on Customer_Data then it merges the info. But I dont want it to replace info like the First & Last names an SSN. Just info that doesnt match. I hope this is more clear. Like I said for some of the things i'm doing it will be 10 times easier to work with one table as apposed to 4. Im not trying to be complicated if I am. My understanding of Access is quite low. Im stil learning the basics of a Database. So im sorry if I dont get what you are trying to say and all.

 

[Anakardian]

Don't worry about not knowing much about Access yet, that will come as you try to solve the problems you meet along the way.
To be honest I was at the exactly same place as you a year ago.

The first thing anyone getting to know Access needs to know is that the data structure and what the user sees are completely different.
However starting with the data itself is best. Don't worry about what you want to see as the final result yet.
I know this is hard to wrap the head around, at least I spent several months understaning it. I really hope you can do better than me on this.

I you can upload a copy of the tables as you mentioned I would be happy to take a look at them.