4 Selects, 1 Union

[Perceptus]

I have 4 queries each with the same amount of Ouput fields. 50k+ rows of data.

q1
SELECT tblWIP_History.PartNumber, tblWIP_History.UnfinishedSerialNumber, tblWIP_History.Location, tblWIP_History.Qty, tblWIP_History.UnitCost, tblWIP_History.ExtendedCost, Format([TransactionDate],"Short Date") AS StartDate, Format([IsMadeDateTime],"Short Date") AS EndDate, tblWIP_History.LongDescription
FROM tblWIP_History
WHERE (((Len(Format([TransactionDate],"Short Date")))>3) AND ((Len(Format([IsMadeDateTime],"Short Date")))>3));

q2
SELECT tblWIP.PartNumber, tblWIP.UnfinishedSerialNumber, tblWIP.Location, tblWIP.Qty, tblWIP.UnitCost, tblWIP.ExtendedCost, Format([TransactionDate],"Short Date") AS StartDate, Format(Now(),"Short Date") AS EndDate, tblWIP.LongDescription
FROM tblWIP;

q3
SELECT tblInventoryHistory.PartNumber, tblInventoryHistory.SerialNumber, tblInventoryHistory.Location, tblInventoryHistory.Qty, tblInventoryHistory.UnitTotalCost, tblInventoryHistory.ExtendedTotalCost, Format([TransactionDate],"Short Date") AS StartDate, Format([Ship_Date],"Short Date") AS EndDate, tblPartMaster.LongDescription
FROM tblInventoryHistory INNER JOIN tblPartMaster ON tblInventoryHistory.PartNumber = tblPartMaster.PartNumber
WHERE (((Len(Format([TransactionDate],"Short Date")))>1) AND ((Len(Format([Ship_Date],"Short Date")))>1) AND ((tblInventoryHistory.WasDeletedByAdj)=False));

q4
SELECT tblInventoryCurrent.PartNumber, tblInventoryCurrent.SerialNumber, tblInventoryCurrent.Location, tblInventoryCurrent.Qty, tblInventoryCurrent.UnitTotalCost, tblInventoryCurrent.ExtendedTotalCost, Format([TransactionDate],"Short Date") AS StartDate, Format(Now(),"Short Date") AS EndDate, tblPartMaster.LongDescription
FROM tblInventoryCurrent INNER JOIN tblPartMaster ON tblInventoryCurrent.PartNumber = tblPartMaster.PartNumber
WHERE (((Len(Format([TransactionDate],"Short Date")))>1) AND ((Len(Format(Now(),"Short Date")))>1));






From here I use query that says

SELECT * FROM q1
UNION
SELECT * FROM q2
UNION
SELECT * FROM q3
UNION
SELECT * FROM q4


When I run any of the queries as they are, data appears as I want. When I run q3, I do not get any rows that are "WasDeletedByAdj" = true. However when I run the Union Query I do get values where WasDeletedByAdj=True. Even though the query the data comes from, says dont.

I have tried Union All, didn't see anything different.


Thoughts?

[pbaldy]

Are you sure the record isn't coming from one of the other queries? You can add an alias field to let you know which query it's coming from:

SELECT *, "Q1" As SourceQuery FROM q1

[Perceptus]

The item only exists in tblInventoryHistory. Its pretty strange a problem, when I run q3 by itself and look at the datasheet, all is well.

I also tried just using the queries instead of the query names in the union. I got the same results.

It would be an easy Kludge, but I would rather not if you know what I mean.

[pbaldy]

Then I can't see how the UNION could pull a record that the individual query didn't. The ALL keyword deals with duplicates. Is it possible to post a db that exhibits the problem?

[Perceptus]

I will see what I can come up with for an example db.

[Perceptus]

Are you sure the record isn't coming from one of the other queries? You can add an alias field to let you know which query it's coming from:

SELECT *, "Q1" As SourceQuery FROM q1

went over from scratch and indeed this was the problem.

Mind=blown

The item was in 2 places with both entries showing almost the same data that I was looking at. I got working now.



Thank you for the assistance pbaldy.

[pbaldy]

No problem; glad you sorted it out.