Union Query seems to have too few records

[David Ball]

Hi,
I have created a Union query from four Select queries.
When I total the records from all four select queries I get 68,187 but the Union query only returns 61,091.
Is there something obvious that I can check to try to figure out what happened to the other 7,000 records?
Thanks very much
Dave

 

[David Ball]

SQL for the Union Query:

SELECT tblMechanicalLAV_Stockcodes.PACKAGE_NUMBER, tblMechanicalLAV_Stockcodes.GROUP_NUMBER, tblMechanicalLAV_Stockcodes.ROS_DATE, tblMechanicalLAV_Stockcodes.Status, tblMechanicalLAV_Stockcodes.TAG_StCode, tblMechanicalLAV_Stockcodes.DESCRIPTION, tblMechanicalLAV_Stockcodes.Adjusted_Avail_Date, tblMechanicalLAV_Stockcodes.MWR_NUMBER, tblMechanicalLAV_Stockcodes.MWR_STATUS, tblMechanicalLAV_Stockcodes.MWR_REQUIRED_DATE, tblMechanicalLAV_Stockcodes.MWR_ISSUED_DATE
FROM tblMechanicalLAV_Stockcodes

UNION

SELECT tblMechanicalLAV_Tags.PACKAGE_NUMBER, tblMechanicalLAV_Tags.GROUP_NUMBER, tblMechanicalLAV_Tags.ROS_DATE, tblMechanicalLAV_Tags.Status, tblMechanicalLAV_Tags.TAG_StCode, tblMechanicalLAV_Tags.DESCRIPTION, tblMechanicalLAV_Tags.Adjusted_Avail_Date, tblMechanicalLAV_Tags.MWR_NUMBER, tblMechanicalLAV_Tags.MWR_STATUS, tblMechanicalLAV_Tags.MWR_REQUIRED_DATE, tblMechanicalLAV_Tags.MWR_ISSUED_DATE
FROM tblMechanicalLAV_Tags

UNION

SELECT tblPipingLAV_Stockcodes.PACKAGE_NUMBER, tblPipingLAV_Stockcodes.GROUP_NUMBER, tblPipingLAV_Stockcodes.ROS_DATE, tblPipingLAV_Stockcodes.Status, tblPipingLAV_Stockcodes.TAG_StCode, tblPipingLAV_Stockcodes.DESCRIPTION, tblPipingLAV_Stockcodes.Adjusted_Avail_Date, tblPipingLAV_Stockcodes.MWR_NUMBER, tblPipingLAV_Stockcodes.MWR_STATUS, tblPipingLAV_Stockcodes.MWR_REQUIRED_DATE, tblPipingLAV_Stockcodes.MWR_ISSUED_DATE
FROM tblPipingLAV_Stockcodes

UNION

SELECT tblPipingLAV_Tags.PACKAGE_NUMBER, tblPipingLAV_Tags.GROUP_NUMBER, tblPipingLAV_Tags.ROS_DATE, tblPipingLAV_Tags.Status, tblPipingLAV_Tags.TAG_StCode, tblPipingLAV_Tags.DESCRIPTION, tblPipingLAV_Tags.Adjusted_Avail_Date, tblPipingLAV_Tags.MWR_NUMBER, tblPipingLAV_Tags.MWR_STATUS, tblPipingLAV_Tags.MWR_REQUIRED_DATE, tblPipingLAV_Tags.MWR_ISSUED_DATE
FROM tblPipingLAV_Tags;

 

[sneuberg]

If you don't use the keyword ALL the union query will combine records that are the same. Maybe that's what you are seeing.

See http://www.fmsinc.com/microsoftaccess/query/union-all/index.htm for more info

 

[David Ball]

Thanks very much, sneuberg

 

[plog]

This UNION issue is a symptom of a larger problem. I don't think you should have 4 different tables for this data--it will all fit into 1 table.

FROM tblMechanicalLAV_Stockcodes
FROM tblMechanicalLAV_Tags
FROM tblPipingLAV_Stockcodes
FROM tblPipingLAV_Tags

You are storing data that should be in fields, in table names. Piping/Mechanical should be a value in a column of a table, not in the name. Tags/Stockcodes should be in a field as well.

How come you don't have a table for each status? For example, tblMechanicalLAV_Stockcodes_Expired, tblMechanicalLAV_Stockcodes_Current, tblMechanicalLAV_Stockcodes_Due? Trick question, its because you are storing the status value in its own field. You need to do the same with Mechanical/Piping and Tags/Stockcode and get all 4 of these tables' data into one table.