Combine 2 fields into a new table showing 1 field

[bryn30]

I need help creating one table to pull a report from. I have one table that has two fields that show identical information. However this report needs to be grouped on the two fields.
Example;
Circuit_ID
A_CFA
A_FOC
A_TEST
Z_CFA
Z_FOC
Z_TEST
A_MKT
Z_MKT
A_REG
Z_REG
A_NUM
Z_NUM

I need a table that will show
NUM (a union of [A_NUM] and [Z_NUM])
REG (a union of [A_REG] and [Z_REG])
MKT (a union of [A_MKT] and [Z_MKT])
Select information
A_CFA
A_FOC
A_TEST
Z_CFA
Z_FOC
Z_TEST

The report will pull all records in XXX [MKT]

Many thanks in advance

 

[ghubbell]

Hi bryn30,

In a query make any "field" you desire by combining them (temporarly) like:

Num: [A_Num] & [Z_Num]

Or maybe for a name:

FullName: [FirstName] & " " & [LastName]

in the "Field" row of the query grid.
Add your other fields to the query, save it and build your report based on this saved query. Gord
ghubbell@total.net

 

[bryn30]

Thanks Gord,
I don't think I explained what I am trying to do correctly.
I have one table with many fields
I need to create one query with certain data
A_CFA
A_FOC
A_TEST
A_NUM
A_REG
A_MKT

and another query based on the same table with certain data
Circuit ID
Z_CFA
Z_FOC
Z_TEST
Z_NUM
Z_REG
Z_MKT

Then Create another union query that join them both into one table that I can run a report on.
NUM (a union of [A_NUM] and [Z_NUM])
REG (a union of [A_REG] and [Z_REG])
MKT (a union of [A_MKT] and [Z_MKT])
Select information
A_CFA
A_FOC
A_TEST
Z_CFA
Z_FOC
Z_TEST

 

[ghubbell]

Hi Bryn30,
An example using the Northwinds Sample database:

SELECT Products.*, Products.ProductName
FROM Products
WHERE (((Products.ProductName)="Aniseed Syrup&quot)
UNION
SELECT Products.*, Products.ProductName
FROM Products
WHERE (((Products.ProductName)="Chang&quot)

All I have done is created a query with the first criteria.
Created another with the second.
Opened the first in SQL view, remove the";" on its end, pasted the seconds SQL in to the first, and added the "union" between.
Once unioned you must continue to work in SQL and use caution if you select certain fields in one: you must select the same in the second. Gord
ghubbell@total.net