Union Query

[Brook]

Good Day All,

I have a union query that I need to add all my fields manually because of
some formatting.

Can anyone help me with that? Here is what I have so far:

SELECT "KI-" & format (tblCustomOrders.Autonumberfield, "0000")
As OrderNumber
From qryCustomorders;

and Here are a couple of the other fields that I need to add:


DesignName
DesignNumber
TibettradeShipmentInvoice#
TibettradeShipmentDate


Thanks,

Brook

 

[Ted Allen]

Hi Brook,

You didn't really give enough info for detailed help. I will mention a few
general things though.

It seems that you likely have an error in the SQL that you posted, because
the field in your query lists the source as tblCustomOrders, but
qryCustomOrders is listed in the FROM clause.

To add the other fields to your query you just put a comma after the first
field, then list the second, a comma, the third etc.

You said you want a union query, but there is no mention of what you want
the other part of the union to be.

By the way, usually when I am creating union queries I will create each
query separately using the query builder if there is a long list of fields.
Then, I switch those to SQL view and copy the SQL and paste into the union
query. Then you can just add the UNION before the second queries SELECT
statement and that's about it. You may need to do a little manipulation if
you have an Order By clause (it comes at the end of the last query, but uses
the field names of the first), but other than that everything should work.

Post back with more details on what you are trying to accomplish if you
would like more detailed help.

-Ted Allen

 

[Brook]

Ok,

This is what I have and what I am wanting to accomplish:

I have three tables that I use for orders for my company:
tblSampleOrder
tblInventoryOrder
tblCustomOrder

I have create 3 queries to calculate information based on the tables and
what I am wanting to do is create a master query so that I can create
reports, totals, etc on the three types of orders.

Thanks,

Brook

 

[Ted Allen]

OK, well it sounds like you want the union query to pull together the fields
from your individual select queries. There are two ways that you could do
this.

If the fields from each query match up, and you want all of the fields, you
could just use something like:

SELECT query1.* FROM query 1
UNION SELECT query2.* FROM query 2
UNION SELECT query3.* FROM query 3;

Where of course you would have to substitute your actual query names.

OR, if you prefer, you could take the SQL from each of your three queries
and combine them in the Union query(in which case the three individual
queries would no longer be needed). In that case, copy the SQL from the SQL
view of each query and paste them together into the union query. Place a
UNION statement between each select, and delete the two earlier ";'s". Also,
make sure that the field order and types match.

If you want to select only some fields from your three source queries,
syntax would be as posted by John Vinson in an earlier post:

SELECT this, that, theother FROM Query1
UNION ALL
SELECT something, somethingelse, whatever FROM Query2
UNION ALL
SELECT a, b, c FROM Query3;

By the way, it is up to you whether you want to use UNION ALL or just UNION.
UNION ALL will display duplicates if there are any, UNION will not display
duplicate records.

HTH, Ted Allen

 

[Brook]

Thanks again for the information,

The only problem that I am having now is that my field (OrderNumber)in
each of my tables/queries is as follows:

tblSampleOrder -- OrderNumber = "SPL-" 0000
tblInventoryOrder -- OrderNumber = "NW-" 0000
tblCustomOrder -- OrderNumber = "KI-" 0000

And when run my Union Query for all three of my Select Queries I loose my
OrderNumber formatting -- It shows up in my Union Query as a "1", "2" etc..

Any ideas?

Thanks,

Brook

 

[Ted Allen]

You can use the Format() function within each of the select queries to return
a string representing the appropriate number formats.

HTH, Ted Allen

 

[Brook]

Good Day Ted,

Can you please ellaborate on what you are suggesting below?

Thanks,

Brook

 

[Ted Allen]

Sure,

Currently your query probably looks something like:

SELECT Table1.OrderNumber, Table1.FieldB,... FROM Table1
UNION SELECT Table2.OrderNumber, Table2.FieldB,... FROM Table2
UNION SELECT Table2.OrderNumber, Table3.FieldB,... FROM Table3;

You can use the format function to return the OrderNumbers formatted as
strings such as the following:

SELECT Format(tblSampleOrder.OrderNumber,"""SPL-"" 0000") AS OrderNo,
tblSampleOrder.FieldB,... FROM tblSampleOrder
UNION SELECT Format(tblInventoryOrder.OrderNumber,"""NW-"" 0000"),
tblInventoryOrder.FieldB,... FROM tblInventoryOrder
UNION SELECT Format(tblCustomOrder.OrderNumber,"""NW-"" 0000"),
tblCustomOrder.FieldB,... FROM tblCustomOrder ORDER BY OrderNo;

Of Course, you can also keep the numeric order number in the SELECT list if
you want to have both (formatted string and number). In that case you would
insert the format statement into the list rather than replacing the original
selected field.

HTH, Ted Allen

 

[Brook]

Thanks for the detailed info!

I am going to give this a shot..and I will post a response on whether or
not it worked for me.

Brook