Combining three tables? (1 Viewer)

[jeff3457]

I believe the answer to my question may be a union query but I am not sure how to create.

I have three groups of data that all need to be pulled together into either a master table or query since it needs to all reflect the same data.

How can I take table A, B, and C and pull together the difference pieces into one query but still maintain the tables. I want to be able to update the table and have the group query follow and vise versa.

Does this make sense?

If I use a union query do all my headers need to match?

 

[Gasman]

I believe you can get away with just making sure they are of the same type/length?

https://www.oreilly.com/library/view/access-cookbook/0596000847/ch01s11.html

HTH

 

[theDBguy]

Hi. Can you show us some sample data from each table? If you use a UNION query, then the data types for each field and the number of columns have to match for all three tables to get one big result. Is there any relation among the tables that you can just JOIN them in a query?

 

[jeff3457]

Hi. Can you show us some sample data from each table? If you use a UNION query, then the data types for each field and the number of columns have to match for all three tables to get one big result. Is there any relation among the tables that you can just JOIN them in a query?

This would be similar to my data.

 

[theDBguy]

This would be similar to my data.

Hi. Thanks. Since all your fields are the same in all the tables, you should be able to use a UNION query. How come you have three tables with the same structure? Just curious...

 

[jeff3457]

Hi. Thanks. Since all your fields are the same in all the tables, you should be able to use a UNION query. How come you have three tables with the same structure? Just curious...

It took a few attempts but I did manage to work through the union query.

Slightly difficult to explain but, essentially I have a group of areas that have a total plan.

I then need to take some of those areas and break out their plans.

So area 1 has 5 people in total. I am taking 3 people and moving them to area 10.

So table A show me area 1 with 2 people. Table B then shows me 3 people in area 10.

To complicate it further I have some areas that do not get broken out. Thus becoming Table C.

I have the 3 tables feeding different pieces of a form and subform, which is why I need them separate. However, I then need to pull them all together in a report view, hence the union query.

 

[theDBguy]

It took a few attempts but I did manage to work through the union query.

Slightly difficult to explain but, essentially I have a group of areas that have a total plan.

I then need to take some of those areas and break out their plans.

So area 1 has 5 people in total. I am taking 3 people and moving them to area 10.

So table A show me area 1 with 2 people. Table B then shows me 3 people in area 10.

To complicate it further I have some areas that do not get broken out. Thus becoming Table C.

I have the 3 tables feeding different pieces of a form and subform, which is why I need them separate. However, I then need to pull them all together in a report view, hence the union query.

Hi. Glad to hear you managed to create your UNION query. However, I wonder if a simple additional field to identify the "grouping" for your data could eliminate splitting the data into multiple tables. For example, if you had all the data in one table and added a field to designate if a record belongs to A or B or C group, then you can use a query with a criteria to filter the records for each form you're using for each group. Just a thought...

 

[jeff3457]

Hi. Glad to hear you managed to create your UNION query. However, I wonder if a simple additional field to identify the "grouping" for your data could eliminate splitting the data into multiple tables. For example, if you had all the data in one table and added a field to designate if a record belongs to A or B or C group, then you can use a query with a criteria to filter the records for each form you're using for each group. Just a thought...

I had attempted that at the beginning but could not conceptualize how to add the people quantity by area break out as well.

Part of my issue as well is that the original table is the main data for the entire database which predates me. So I am concerned to make to many edits to it as it could break the entire database beyond my repair.

 

[theDBguy]

I had attempted that at the beginning but could not conceptualize how to add the people quantity by area break out as well.

Part of my issue as well is that the original table is the main data for the entire database which predates me. So I am concerned to make to many edits to it as it could break the entire database beyond my repair.

No worries. It may be a problem to tackle for another day. Good luck with your project.