I need an MS Access query that can do move data from 2 fields and put them into 1 field in a certain order!?

Question

FIELDS: field1 field2 field3 field4

Field1 - has a list of 100 employee names
Field2 - has a list of employee numbers
Field3 - has a list of employee kid's names
Field4 - has a list of employee spouse names

I need to move the data in these fields into 3 fields/columns and stack the info from Field3 and Field4 onto each other.

Example:

field1 field2 field3 field4
Smith 001 Mary Jean
Chen 002 Wu Simon
Black 003 Tom Kate
Patel 004 Nigel Manu

I need to re-arrange the data in the following sequence:

fieldA fieldB fieldC
Smith 001 Mary
Chen 002 Wu
Black 003 Tom
Patel 004 Nigel
Smith 001 Jean
Chen 002 Simon
Black 003 Kate
Patel 004 Manu

There will actually be a FieldD that contains a descriptor for FieldC (ie. fieldD = spouse).

Anyone have any ideas? Field1 and field2 will be used as unique keys basically

Answer 1

Assuming the new table is named "newTable" and the original data is in a table called "origTable", you could do this:

insert into newTable
(fielda, fieldB, fieldC)
select field1, field2, field3 from origTable

Answer 2

2 issues come to ideas. First, the hot record isn't added to the table until eventually the cursor is stepped ahead to a distinct record, so be particular that the hot record is dedicated. Secondly, only including a clean record does no longer inevitably reason the question to re-study. you are able to favor to emphasise it by technique of creating use of a requery command contained in the VBA module after the record is added. wish that permits.