Unmatched Query & Append

[thegrimmerdiscovery]

Hiya



Had some great advice on basic Access on the Excel boards from Joe4 - not least to get Access support here. Which obviously makes a great deal of sense.

Almost a total newbie to Access. I'm not sure if this is how it works here regarding etiquette so apols if I've transgressed any rules.

But here is the SQL I've got based on a small database I'm cutting my teeth on.

INSERT INTO Table2 ( Artist, Album )
SELECT Artist, Album
FROM Table1
WHERE Album Not In (SELECT Album FROM Table2);

You'll see that this enables me to copy any new artists and albums from table 1 to table 2 as they're added. It's great.

But when I try and get all logical and transfer this SQL code to the 'real' database I'm trying to start up it doesn't work:

INSERT INTO tblMilestones ( ProjectName )
SELECT ProjectName
FROM tblAudits
WHERE ProjectName Not In (SELECT ProjectName FROM tblMilestones);

I'm trying to copy any new project names from tblAudits to tblMilestones. But now I get a validation rule violation error. I've checked tblAudits and tblMilestones in design view for field names data type incompatibility but they all match up.

Any clues as to where I'm going wrong?? Thanks in advance.

[isladogs]

Access has an unmatched query wizard which I would recommend using at least while you are learning
Create a query with both tables and use an outer join from tblAudits to tblMilestones using the ProjectName field
Add the field ProjectName from both tables & untick it for tblMistones. Filter that same value as null
When done you'll have a SELECT unmatched query.
Now change it to an APPEND query with tblMilestones as your destination table

The query SQL is

INSERT INTO tblMilestones ( ProjectName )
SELECT ProjectName
FROM tblAudits LEFT JOIN tblMilestones ON tblAudits.ProjectName = tblMilestones.ProjectName
WHERE tblMilestones.ProjectName Is Null;