A difficult question (2)

[Steveanderson]

I am trying to combine several tables into a results table. I have encountered problems because the field structure of these tables is different and varies from table to table.

Table 1
Fields A / B / C

Table 2
Fields A / B / C / D

If I try to use an append querry the data from table 2 cannot be inserted in table 1 because of the field D.
Is there a way to force access to create automatically the new field during the append querry ?

Thanks in advance

 

[dkinley]

Why not append Table 1 to Table 2 then?

If not, go into Table 1's design mode, add the field and save. Then run your append query.

To answer your question though, no. You could use a data definition query for the mod and then an append query. I've never tried it but you may be able to combine the two into a data definition query. Other than that, you could do in VBA quite easily.

-dK

 

[Steveanderson]

Can you give me an example of the VBA code I should use ?
thanks

 

[vbaInet]

Sounds like you're breaking normalization rules there. Duplicating data isn't a good idea. Use a UNION query if you want to combine both and set the D fieldfor table 1 to Null in the Union:

http://www.techonthenet.com/sql/union.php

That way you don't have to duplicate data.