auto-record creation

[Badswell]

I currently have a table that company information is imported into. On of the fields is whether or not the company is current (0=No, 1=Yes, 2=Left Blank). These profiles can also be added and edited manually.

I have a 2nd table of information that is entered manually for only the current companies.

Based on the companies in table 1 that have 'Current'=1, is there a way to auto-generate a record for each (PK=name) in table 2?

 

[pbaldy]

Sure; an append query will do it. Something like:

INSERT INTO Table2 (Field1, Field2)
SELECT Field1, Field2
FROM Table1
WHERE Current=1

 

[Badswell]

Ah yes I forgot about those. That accomplishes that, however, would there be a way (might need VB coding to do this) to run this query only if the company didn't already exist in Table 2?

If they are already there, I don't want the information appended. Trying to avoid key violation errors.

 

[pbaldy]

This is off the top of my head, so it may need tweaking:

INSERT INTO Table2 (Field1, Field2)
SELECT Field1, Field2
FROM Table1
WHERE Current=1 AND Field1 Not In (SELECT Field1 FROM Table2)