Access query that appends two tables

[Maggie Barr]

Hello, and thank you in advance if you can help,
I have a database that I am trying hard to not create physical tables in. I have two linked tables to the database, call them data 1 and data 2. I would like to create a query that will append these two tables together according to the fields in data 1. As they are linked, I cannot append one to the other, nor do I want to. I would like to be able to query these two linked tables together only in the query, so that I could then export the results as a text file for further analyses in Excel's Power Query. I am trying to do this so that I can automate as much as possible within the Access environment, and so as the dataset gets updated and grows, I will not have space limitations associated with physical tables. Does anyone know if it is possible to do this, and could you please advise on the best approach.
Thank you and Best Wishes,
Maggie

 

[Joe4]

If the two tables have similar strucutures, you can create a query that combines them called a Union query.
See here for details: https://support.office.com/en-gb/ar...e-result-1f772ec0-cc73-474d-ab10-ad0a75541c6e

 

[Maggie Barr]

Joe4,
Thank you so much! That looks very promising! I am logging off for the weekend, but I will l give it a try and let you know how it goes the first of the week.
Thank you again,
Maggie

 

[Joe4]

You are welcome!

 

[Maggie Barr]

Joe4!!!
Happy Monday to you! That worked perfectly! I really appreciate the link you provided, good explanations and nice video tutorial.
I am now doing some digging to find out why that query isn't showing up in the Excel Power Query Get and Transform. The database shows up, and all the linked tables, and one query that I created that is just a duplicate of a linked table. It appears that none of the queries with modified columns or calculated columns are available to link to a Power Query database. Not a huge deal as I can export the query to use, but if I can link it to that final query, I wouldn't have to export it and then import to excel power query, thus crating a break in the "chain" of analyses.
Anyway, thank you again, so much, for your help!
Best Wishes,
Maggie

 

[Joe4]

You are welcome!

I do not use Power Query much, so don't really have any advice to offer on that. But you could post that question to the "Power BI" forum (https://www.mrexcel.com/forum/power-bi/), and someone may be able to help you with that piece of it.

 

[Maggie Barr]

Joe4,
Thanks for the advice, I posted it on the Excel part of this forum, and I will try the Power Bi if I need too, thanks again for all your help. Have a Great Day!
Best Wishes,
Maggie

 

[Maggie Barr]

Joe4,
Just thought I would put it out there what I found to be the problem from RoryA on this forum under Excel.
It turns out that if you have any Access specific functions in a query, like Nz, the query is not readable in Excel, so it won't show the table. I replaced my Nz function with IIf functions, and all the queries were visible; However, the last query, being a union query, was not, so I had to create a simple query of that one, bringing in all the fields, to make it visible and usable in Power Query.
Thanks again,
Maggie
I changed the construction from Nz([GROUP IDENTIFIER],[SAMPLING EVENT IDENTIFIER]) to IIf([GROUP IDENTIFIER] Is Null,[SAMPLING EVENT IDENTIFIER],[GROUP IDENTIFIER])
Then I made a select query of my union query bringing in all the fields from that.