Creating a UNION Query in Microsoft Access - Combine multiple datasets into one view

[@K5MackMarchant]

Short and to the point, well done!

[@TLDW_Tutorials]

Thanks so much! I really appreciate the comment and you checking out the video.

[@DarmaSaputra-y8b]

This is what I have been looking. Thank you!

[@TLDW_Tutorials]

No problem, I am glad it was helpful. Thanks for watching!

[@RabiReels]

Thank You!. I have two Access tables. How do I merge these tables which have identical structures and then get only unique records in a new access table?

[@TLDW_Tutorials]

So just to be clear, so if you want to merge two tables with the same structure, you want to do a join. I have a video for how to do that. Based on your comment though, it sounds like you want to do a UNION query like in this video and then create a new table. In your UNION query, I would make a new field to denote D1 (or dataset1) + your row ID and D2 (or dataset2) + your row ID. This will guarantee each row will have a unique identifier. Once you get the UNION query to work with this, you can save it. Open a new query, use the UNION query you just made as your data source, and then generate a Table query (click on "Make Table" under Query Design). Once you run it, this should create a new table with the two tables you made in your union query. Here's the SQL example of this based on the fields and tables from the video: controlc.com/d8237a33

[@Jojosmith342]

thumbs up for another great tutorials. How to extract data from one table for the other table (different fields). I have table with hundreds of thoussands records. One table has all the names, the other table has thousands of missing names. Is there anyway to copy data from one table to the other? thanks a lot

[@TLDW_Tutorials]

Responded to the other message. Great question. Let me know if you still have any issues. Like I said, I may try to make a quick video about this for next week.

[@threehanded8853]

My query does not like one of my first fields with spaces in it, and that column is not in the other tables I am selecting. Perhaps I should be doing a join instead since columns and rows/records are mostly different?

[@TLDW_Tutorials]

Hey! So for your field with spaces in it, just surround the name with brackets and you should be good. As for the join versus the union, think if you want stacked (1, 2, 3 in one table and add 3, 4, 5 from another table). If you do and many of your fields align, a union should be fine. For the fields that are missing, you can make a placeholder for them. For a join you would typically want to merge if there is a bunch of different, distinct data you want to bring together. Like race in one table and gender in another, etc. I guess it just depends on your two datasets. Feel free to copy and paste your query here if it would help. I’d be happy to look.

[@threehanded8853]

@@TLDW_Tutorials thank you! I fixed my copy paste and text errors in the sql editor, but now I realize in a union the number of fields must match, but that is not my scenario. I have 20x10 and 4x6, and so on, that need to come together in one matrix. Not sure of the best approach, or whether I should just “form” it in Excel!?

[@TLDW_Tutorials]

@@threehanded8853 Sounds like a join may be a better fit. Do you have some kind of unique identifier you can merge the tables together on? Think like a social security number or something that's unique that can bring the tables together. Also for a union query, you can have uneven number of fields and just add the placeholder fields to make it match up, though it sounds like that's not what you want to do. I know it's complicated to work this out in the comments. Another thing you can try is adding a snippet of both datasets, sending it to ChatGPT and asking the best approach.

[@threehanded8853]

@@TLDW_Tutorials thanks again. Yes, I will try the join as I have one unique field (sequential numbering) in all tables.

[@thearter5480]

Can I use the union query and then insert the results into a new table?

[@TLDW_Tutorials]

Yep, absolutely. Create the union query and save it. Next, open a new query and add your union query as your data source. You should be able to append or create a new table with the results. What I mean is create a “create” query or “append” query. If you have any trouble, let me know and I can give you more detailed instructions.

[@HarveyPVP]

I wasn't able to create the variable

[@TLDW_Tutorials]

Check every little character to make sure nothing got missed… if you want to paste your code into a controlc.com link and send it to me, I can take a look. You can also send it to ChatGPT or Bard and ask “why isn’t my code working?” Which might solve it.

[@RomuVoice]

I have 29 tables how i get all in one form plzzz help me

[@TLDW_Tutorials]

Wow. That’s a lot of tables! Is there some pattern to the names of the tables (example: table1, table2, etc)? And I assume that they have some of the same fields?

[@RomuVoice]

@@TLDW_Tutorials its actually my office 29 departments different data but now i. am stuck

[@RomuVoice]

@@TLDW_Tutorials is there any way ????

[@TLDW_Tutorials]

@@RomuVoice yep, there’s likely a way. If you can describe how to do it to another person, then you can make code to do it. So are the 29 tables the only tables you have on your database? Also you said different data, but does this mean nothing consistent between all 29 tables?

[@RomuVoice]

@@TLDW_Tutorials the id no numbers are marched but departments are diffrents