create an uppend query on the fly?

[Dave]

Can someone give me some advice on this. I need to append tables together
for an Audit. It can be up to as many as 4 tables. All the tables that will
be appended have 2 fields in common, Part and NHA. When the fields are equal
in the tables all data must be on the same line as shown in the example below
for Data (TEST and GO) all other data still needs to go into the table just
as seperate rows. Since there is no set number of tables being appended I
beleve I must create an append query on the fly. Can some one give me advice
on how to tackle this? I also would like to know if there is a way of
referencing fields by pointer or position in table instead of Name. I assmue
this would be done somehow with DAO and string concatenation but just do not
knoe where to start.


Example: TBL1, Part, NHA, Col1, Col2 Tbl2, Part, NHA, Col3,
Col4
Data Test , A , 1a , 1b Test , A ,
4A -
DAta Fly , Z , 2a , 2b Stop , X ,
5A , 5B
Data Go , H , 3a , 3b Go , H ,
- , 6b


Result: Tbl3, Part , NHA, Col1, Col2, Col3, Col4
TEST A 1a 1B 4a -
Fly Z 2a 2b - -
Stop X - - 5A 5b
Go H 3a , 3B, - 6B

Thanks,

Dave

 

[Dave]

Reposting Sample data hopefully with better results Sample data:

TBL1, Part, NHA, Col1, Col2
Data Test , A , 1a , 1b
DAta Fly , Z , 2a , 2b
Data Go , H , 3a , 3b

Tbl2, Part, NHA, Col3, Col4
Data Test , A , 4A , -
Data Stop , X , 5A , 5B
Date Go , H , - , 6b


Result: Tbl3, Part ,NHA, Col1, Col2, Col3, Col4
Data TEST, A , 1a , 1B , 4a , -
Data Fly , Z , 2a , 2b , - , -
data Go , H , 3a , 3B , - , 6B
data Stop , X , - , - , 5A , 5b

Dave