UNION ALL Query

[Brook]

To All:

I am trying to joing two tables with the same data
fields using the UNION All query. My two tables are
tblCustomOrders and tblStockOrders and I would like to
combine them into one table. Can anyone give me any tips
on how to do this using the Union Query?

Thanks

Brook

 

[Ken Snell]

Assuming that the table already exists where you'll put the records...

First, create and save the Union Query (following assumes that each table
has the same number of fields and that each corresponding field pair has
same format):

SELECT tblCustomOrders.*
FROM tblCustomOrders

UNION ALL

SELECT tblStockOrders.*
FROM tblStockOrders;


Save this query using the name UnionQry.

Then use this append query to copy the results into your target table:

INSERT INTO TargetTable
SELECT UnionQry.* FROM UnionQry;

 

[Brook]

Thanks for the information, I am still a little confused
about something:

Where do I put the following code in my query?

 

[Brook]

OK, I was able to get the UNION QUERY to function
properly, except that when My tables merged, my ORDER ID
fileds for both didn't copy over correctly. for my ORDERID
on my tblStockOrders (Autonumber using a format setup "NW-
"0000) and tblCustomOrders (Autonumber using a format
setup "KI-"0000).

What is copies over is this: If I had a ORDERID of
KI-0004, it copies the 4 over as the order ID.

Any suggestions on how to copy the full ID over?

Brook

 

[Jeff Boyce]

Brook

From your description, it sounds like you've used a format to modify the
display of your autonumber fields. This doesn't modify the data stored,
just the display of it.

 

[Brook]

Jeff,

Thanks for the info, yes I did you a format for the
display of my Autonumber: for tbleStockOrders I used the
format "NW-"0000, and for tblCustomOrders I used the
format "KI-"0000, I am using an update query to send the
info from the unionquery to a tblMasterInventory. Any
suggestions on how I can pull these formats into my
tblMasterInventory?

Thanks for your help!

Brook

 

[Ken Snell]

Because I don't know all the fields that are in your tables, I will show you
how to bring over your formatted display using just the one field from each
table. You'll need to add more fields to each SELECT clause to bring over
the other fields.

SELECT "KI-" & Format(tblCustomOrders.Autonumberfield, "0000") AS
ActOrderNum
FROM tblCustomOrders

UNION ALL

SELECT "NW-" & Format(tblStockOrders.Autonumberfield, "0000") AS ActOrderNum
FROM tblStockOrders;

 

[Brook]

Thanks for the info,

When I tried this, and ran the query again, a pop up box
asking for tblCustomOrders.Autonumberfield and
tblStockOrders.Autonumberfield??

Not sure what to do?

Brook

-----Original Message-----
Because I don't know all the fields that are in your tables, I will show you
how to bring over your formatted display using just the one field from each
table. You'll need to add more fields to each SELECT clause to bring over
the other fields.

SELECT "KI-" & Format

(tblCustomOrders.Autonumberfield, "0000") AS

ActOrderNum
FROM tblCustomOrders

UNION ALL

SELECT "NW-" & Format

(tblStockOrders.Autonumberfield, "0000") AS ActOrderNum

 

[Brook]

I got it!

Thanks for your help!

I guess I have to do this for all my fields now, right?

Brook

-----Original Message-----
Because I don't know all the fields that are in your tables, I will show you
how to bring over your formatted display using just the one field from each
table. You'll need to add more fields to each SELECT clause to bring over
the other fields.

SELECT "KI-" & Format

(tblCustomOrders.Autonumberfield, "0000") AS

ActOrderNum
FROM tblCustomOrders

UNION ALL

SELECT "NW-" & Format

(tblStockOrders.Autonumberfield, "0000") AS ActOrderNum

 

[Ken Snell]

I see you identified that my "Autonumberfield" was just a generic reference
to the field name, and that you needed to replace it with the real name.

I'm not sure what you mean by needing to do this for all your fields? If you
mean you need to add the rest of the fields to the query, then yes.

 

[Brook]

Thanks,

That is what I needed to know.. You have been a great
help. Have a great day.

Brook

 

[Brook]

Final Question,

Is there a way that I can send this UnionQry to a
tblMaster?

Brook

 

[Ken Snell]

Send it to a table? Do you want to append to that table or make the table?

 

[Brook]

Ken,

I just set up a new table and created an appendquery to
populate the data from my UNION Query to my tblMaster.

Thanks for all your help!

Brook

 

[Ken Snell]

Good job!

--

Ken Snell
<MS ACCESS MVP>

Ken,

I just set up a new table and created an appendquery to
populate the data from my UNION Query to my tblMaster.

Thanks for all your help!

Brook