Access query that appends two tables

Maggie Barr

Board Regular
Joined
Jan 28, 2014
Messages
188
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
 

Excel Facts

What did Pito Salas invent?
Pito Salas, working for Lotus, popularized what would become to be pivot tables. It was released as Lotus Improv in 1989.
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
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,557
Messages
6,114,291
Members
448,564
Latest member
ED38

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top