UNION query Challenges

[Guest]

I have this union query (below) that joins two tables. However, when I create
a new query with the union query as it's source, I do a sum on the [Bill
Amount] with a Group By on [Vendor], expecting to get a total on bill amount
with 1 vendor and all the rest should roll up to a second record because
vendor is otherwise blank. However I end up getting an extra record (total of
3).

Based on this union query structure can you give me some ideas of why that
would be?

SELECT Code, ProjNo, F3, Invoice, TT, LLT, MidTask, TASK_NAME, [Empl],
[PER_DIEM_CODE] as [Per Diem Code], Classification, [Class Grade], [Title],
[Job Title Override], Hours, [Bill Rate],[Raw Cost], 0 as Quantity, [Bill
Amount], ExpDate, "" as [ExpCat], [ExpType], "" as Vendor, "" as VIN, Thru
FROM [Labor-Current]

UNION ALL SELECT Code,ProjNo, F3,Invoice,TT, LLT, MidTask, "" as TASK_NAME,
[Empl],"" as [Per Diem Code], "" as Classification,"" as [Class Grade], "" as
[Title], "" as [Job Title Override], null as Hours, [Bill Rate], null as
[Raw Cost], Quantity, [Bill Amount],ExpDate,[ExpCat],[ExpType], Vendor, VIN,
Thru
FROM [Expense-Current];

 

[Guest]

What is displayed in the third vendor field? Nothing?
You may have vendor, nulls, and zero lenght entry in the record which is not
the same as null. You can make all null zero lenght or all zero lenght to be
nulls. An update query with this Is Null as criteria to update to "" for
zero lenght. Or "" for criteria to update to Null.

 

[Guest]

I understand what you're saying. However, after using an update query and
making the records comply with either "" or Null as their value, the Vendor
column is still separating them. I'm still not sure why it is. There is not a
space in the records either. Note: these records are imported from Excel
which is exported directly from an Oracle SQL server.
--
THX cs

What is displayed in the third vendor field? Nothing?
You may have vendor, nulls, and zero lenght entry in the record which is not
the same as null. You can make all null zero lenght or all zero lenght to be
nulls. An update query with this Is Null as criteria to update to "" for
zero lenght. Or "" for criteria to update to Null.

I have this union query (below) that joins two tables. However, when I create
a new query with the union query as it's source, I do a sum on the [Bill
Amount] with a Group By on [Vendor], expecting to get a total on bill amount
with 1 vendor and all the rest should roll up to a second record because
vendor is otherwise blank. However I end up getting an extra record (total of
3).

Based on this union query structure can you give me some ideas of why that
would be?

SELECT Code, ProjNo, F3, Invoice, TT, LLT, MidTask, TASK_NAME, [Empl],
[PER_DIEM_CODE] as [Per Diem Code], Classification, [Class Grade], [Title],
[Job Title Override], Hours, [Bill Rate],[Raw Cost], 0 as Quantity, [Bill
Amount], ExpDate, "" as [ExpCat], [ExpType], "" as Vendor, "" as VIN, Thru
FROM [Labor-Current]

UNION ALL SELECT Code,ProjNo, F3,Invoice,TT, LLT, MidTask, "" as TASK_NAME,
[Empl],"" as [Per Diem Code], "" as Classification,"" as [Class Grade], "" as
[Title], "" as [Job Title Override], null as Hours, [Bill Rate], null as
[Raw Cost], Quantity, [Bill Amount],ExpDate,[ExpCat],[ExpType], Vendor, VIN,
Thru
FROM [Expense-Current];