Appending Two Tables to make a third

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have two tables which have to be kept separately as they are maintained by different people
All columns, headings and field settings are exactly the same
For a report I need to combine the two tables to make one longer table from which I can produce my report
Is there a way I can append the two tables into a third in one process
I can use a "Make Table Query" on one table and then "Append" the second table to it but this is a tw
stage process and will not automatically get refreshed when I run the report

Any help would be appreciated. Thank you.
 
Sean, you can use a UNION query to do this.

1. Create a query into each table that gives the results exactly the same
for both.

2. Switch the queries to SQL view (View menu).

3. In the first query, remove the semicolon at the and, and type:
UNION

4. Paste in the query statement from the other query.
The result will read like this:
SELECT ... FROM Table1 WHERE ...
UNION
SELECT ... FROM Table2 WHERE ... ;

5. Save the query, and use it as the source for your report.
You no longer need the 2nd query.

It may be better to combine the 2 tables into one, with an extra field to
distinguish between the 2 users, and set the RecordSource of your forms so
only the appropriate person's records are retrieved.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Sean Bishop said:
I have two tables which have to be kept separately as they are maintained by different people.
All columns, headings and field settings are exactly the same.
For a report I need to combine the two tables to make one longer table
Click to expand...
from which I can produce my report.
 
I have two tables which have to be kept separately as they are maintained by different people.
Click to expand...

ummm... what does that have to do with it?

Access is a sharable database. Provided the two people aren't editing
exactly the same record at exactly the same time, there's no reason
they shouldn't be using the same table! Are the two users on the same
network? Can they not share the database or (better) use the Database
Splitter Wizard to create a shared database containing the table (or
tables), and a frontend with the forms, queries etc.? Each user would
get a copy of the frontend.
All columns, headings and field settings are exactly the same.
For a report I need to combine the two tables to make one longer table from which I can produce my report.
Is there a way I can append the two tables into a third in one process.
I can use a "Make Table Query" on one table and then "Append" the second table to it but this is a two
stage process and will not automatically get refreshed when I run the report.
Click to expand...

A Union query (see the online help for UNION) will let you stitch the
two tables together dynamically to create your report.
 
Now that Duane Hookom taught me the basics of a union query, this post
really interested me, because I have been wanting to separate years in my
year to date tables. I did that and then tried your suggestions. It worked
like a charm without any typing. I replace in my query at the bottom of the
totem pole, with by "TblYearToDateData" with my new union query
"QryTblYearToDateData" and everything just fell into place.
Wonderful, thank you,
Annelie
 
Back
Top