Append Query - Different Field Names (1 Viewer)

[runwin]

Hi There

I'm creating an automated system on access, basically it uploads client's files and analyses their data. The files will always be different, with the amount of fields changing and with different field names each time
One part of it, is appending new contacts to their data. This means records which we can add new contacts to, needs to be duplicated with the new contact placed at the end. So it needs to be like

Company Name New Contact Name
A
B
B Tom
B Harry

Because it's automated with different field names each time, the duplicating part is an issue. I can use the * rule which appends all fields, however this will not work in this case, if we are adding more than 1 new contact, the new contact will be duplicated rather than having 2 new different contacts.
Ideally I want rule saying, append all fields EXCEPT the fields where the new contacts are placed, but I don't think this is possible

I'm using Access 07 for this
Using a mix of VBA and SQL in the modules

I've been stuck on this issue for months, so any advice would be greatly appreciated

Thanks
Rob

 

[CJ_London]

not totally clear from your explanation but I think what you need is 2 left joins between your import table and destination table on companyname and contactname and set a criteria of

where destinationtable.contactname is null

 

[runwin]

Thanks for your advice CJ, I'll look into '2 left joins' over the weekend and see if this helps me in anyway