Union Query Help

[bgwool]

I have a union query that is supposed to combine different fields on 'Survey Code' and 'EmployeeID', which is working, but it is also combining duplicates in 'Time1/Time2/etc' or 'Pay1/Pay2/etc'. Can anyone help change my query so that the [Time] and [Pay] don't combine on like values?

Code:

SELECT Hours1Total.[SurveyCode], Hours1Total.[EmployeeID], Hours1Total.[Time1], Hours1Total.[Pay1]


FROM Hours1Total;
UNION SELECT Hours2Total.[SurveyCode], Hours2Total.[EmployeeID], Hours2Total.[Time2], Hours2Total.[Pay2]
FROM Hours2Total;
UNION SELECT Hours3Total.[SurveyCode], Hours3Total.[EmployeeID], Hours3Total.[Time3], Hours3Total.[Pay3]
FROM Hours3Total;
UNION SELECT Hours4Total.[SurveyCode], Hours4Total.[EmployeeID], Hours4Total.[Time4], Hours4Total.[Pay4]
FROM Hours4Total;
UNION SELECT Hours5Total.[SurveyCode], Hours5Total.[EmployeeID], Hours5Total.[Time5], Hours5Total.[Pay5]
FROM Hours5Total;
UNION SELECT Hours6Total.[SurveyCode], Hours6Total.[EmployeeID], Hours6Total.[Time6], Hours6Total.[Pay6]
FROM Hours6Total;
UNION SELECT Hours7Total.[SurveyCode], Hours7Total.[EmployeeID], Hours7Total.[Time7], Hours7Total.[Pay7]
FROM Hours7Total;
UNION SELECT Hours8Total.[SurveyCode], Hours8Total.[EmployeeID], Hours8Total.[Time8], Hours8Total.[Pay8]
FROM Hours8Total;

[JoeM]

By defaults, duplicates are weeded out when using UNION. UNION ALL will not do that.
See: http://www.fmsinc.com/microsoftacces...-all/index.htm

[bgwool]

Thank you for the reply. I fixed the issue by adding a auto-number field to the queries being unioned so that they each have a unique value. Your solution seems easier, though. I'll try it.

[JoeM]

Thank you for the reply. I fixed the issue by adding a auto-number field to the queries being unioned so that they each have a unique value

How many different tables is this data coming from (is Hours1Total a Table or Query)?

If you have multiple tables and did this by adding Autonumber fields to multiple tables, note that it is possible to have records in separate tables that appear to be duplicates and just happen to have the same Autonumber value. In that case, those would be combined, doing it the method your first tried (whereas UNION ALL would not have that issue).

Just wanted you to be aware of that, in case you don't use UNION ALL and see some unexpected results.

[bgwool]

This is also great info, thanks. Luckily, all of the Autonumber fields come from the same table, so it shouldn't be an issue in this instance. The UNION ALL is a far more elegant way to perform the same function, though, and I will use it in the future.

[JoeM]

Great! Glad you got it working the way you need.