Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I need an MS Access query that will combine data from two identical fields in two different tables into one field. The two tables are "Payroll_2011" and "Payroll_2012"; and both tables have columns "Name", "Payperiod End Date," and "Amount".

I do not need to concatenate them. Something like FirstName&" "&LastName isn't what I am looking for.

I just want the query to have, for example, Payroll_2011.PPE and Payroll_2012.PPE rolled into one field that I can sort from Oldest to Newest and see their payroll history across the years without having to open multiple tables.

Please help? (Sorry about lack of formatting)

share|improve this question

1 Answer 1

If your desired result is to have one "table" (query) that has the same fields as the original tables, but combined, then you need a Union Query. These cannot be done in the normal Access query builder, but rather it has to be done in SQL view. In order for a Union query to work, the fields of the corresponding tables must match exactly, both in column names, number of columns and order of columns. If your existing tables don't match exactly, you should set up a simple select query that will return them exactly the same, then use those queries to form the union.

Once your tables or queries are matching, create a new query in design view, but do not add any tables. Switch the design view to SQL view and enter the following:

SELECT * FROM Payroll_2011
UNION ALL
SELECT * FROM payroll_2012;

If you had to use queries to line up the columns, substitute those names instead. Then run the query. The result will be all of the data from Payroll_2011, lined up with the data from Payroll_2012. At this point, you should be able to use the normal Access sorting and filtering options.

share|improve this answer
    
Thanks! It's a work in progress, partly because the payroll tables are quite large, so I can't give a status update (yet). –  Michelle F Dec 5 '12 at 19:27

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.