Access query to combine two columns into one longer column with both sets of data

microsoft access

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)

Best Answer

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.

Related Question