Union Query Problem

[DLF]

This is my first attempt at using a Union Query - Any help would be greatly appreciated!

I have 5 fields I'm combining from two tables:
SELECT [AcctNumber],[ReqNumber],[ReqDate],[Description1],[SupplierName]
FROM [tblRequisitions]

UNION SELECT [AcctNumber],[ReqNumber],[ReqDate],[Description1],[SupplierName]
FROM [tblJournalTransfers];

I have two issues to begin with:

1) In my tblJournalTransfers field ReqNumber (stored as text) I input mask: "JT "000;;_ so that I can differentiate a Journal Transfer number from a Requisition number. tblRequisitions is straight 3 digit number but stored as text also. The above Union Query is returning nice results, however it is ignoring the "JT" prefix in the ReqNumber field on the records pulled from tblJournalTransfers???

2) Can this Union Select query be used within another query? I need to add more fields from both tables to get the results needed for a report. When I tried to base another query on both the Union Query and the two tables (tblJournalTransfers and tblRequisitions) the resulting records were multiples (numerous sets of each record).

Thank you for your assistance!!!

Diana

 

[DLF]

Pat -

Thank you very much - you gave me a better way of looking at the problem. It works great now!