Merge fields (not concatenate)

[sberti]

I wondering if it is possible to merge to 2 fields into a new new field (but I don't mean to concatenate them)
This is to track stock that has Transferred from one Unit to another:

Unit Names: CC1, CC3, CC4, CC7

This is the kind of thing I'd like to do:

Fields: [FromUnit][ToUnit]

[FromUnit] [ToUnit]
CC1 CC3
CC3 CC4
CC4 CC7

Convert to:


[Transfers]
CC1
CC3
CC4
CC7

I've been trying to do it using a table and a copy of the same table, then trying to join the [FromUnit] in the Original Table with the [ToUnit] in the Copy Table. but I can't get it to work.

Any suggestions?

[June7]

Use a UNION query. No wizard or designer for UNION, must type into the SQL view of Query Designer. A UNION query will not allow duplicate records unless you include the ALL keyword.

SELECT [FromUnit] As Transfers FROM tablename
UNION ALL SELECT [ToUnit] FROM tablename;

What is the reason for this?

[sberti]

I'm tracking costs.
Each Unit contains fish.
Each unit has a monthly Operating Expense associated with it.
At certain times the fish are Graded by size into new Untis. (smalls, mediums, larges) This can happen a number of times throughout the life cycle of the fish.
I'm associating the Operating Expenses to each of the new Graded pens using the same percent as the percent of the Biomass (kgs of fish) that transfers to each pen.
In order to find the Total Cost of Operating Expense for the fish in the final pen, I need to be able to sum up the expenses back through all the Unit Transfers.

I'm finding it to be a challenge. I'll try your suggestion. Thanks