Query isnt updating table based on criteria

[shabbaranks]

Hi all - yep me again. Ive got an append query which isnt appending:

INSERT INTO ProjectsTable ( NAME, REFERENCE, NAME2 )
SELECT ProjectsTable.NAME, ProjectsTable.REFERENCE, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable ON ProjectsLinked.NAME = ProjectsTable.NAME
WHERE (((ProjectsTable.NAME) Is Null) AND ((ProjectsTable.REFERENCE)="!XXX01") AND ((ProjectsTable.NAME2)="Main Project")) OR (((ProjectsTable.REFERENCE)="!XXX99") AND ((ProjectsTable.NAME2)="Active Centre")) OR (((ProjectsTable.REFERENCE)="!XXX98"));

Does this look like it should add fields which are Active centre or Main Project but not XXX99 / 98 or 01.



There is one record in the table which its just not importing/appending??

Am I correct in thinking this should update

[orange]

Try this ( try the SELECT part and see if it gives what you want before doing the Insert) [untested]



INSERT INTO ProjectsTable ( NAME, REFERENCE, NAME2 )
SELECT ProjectsTable.NAME
, ProjectsTable.REFERENCE
, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable
ON ProjectsLinked.NAME = ProjectsTable.NAME
WHERE
(ProjectsTable.NAME Is Null) AND
(ProjectsTable.NAME2 ="Main Project" OR ProjectsTable.NAME2="Active Centre")
OR NOT
(ProjectsTable.REFERENCE="XXX99"AND ProjectsTable.REFERENCE ="XXX98" AND ProjectsTable.REFERENCE ="XXX01") ;

[shabbaranks]

I think its my not equal to which may be causing a problem? I thought <> or =!XXX was not equal to? If I run this in a query it displays nothing if I take away the <> or ! it displays the data I dont want to add - confused

[ssanfu]

I think its my not equal to which may be causing a problem? I thought <> or =!XXX was not equal to? If I run this in a query it displays nothing if I take away the <> or ! it displays the data I dont want to add - confused

From Help: [!charlist] Any single character not in charlist

So the expression "!XXX01" would match anything *except* a value that contained an X, 0, or 1


Also, "Name" is a reserved word that shouldn't be used as object names.
In addition, it is not very descriptive. Name of what? A project, Supervisor, a car???

[shabbaranks]

So what would be the best method if within my query I dont want to specifically add certain records to my table? As for name columns I created this before I knew there were reserved names I will amend that to be more specific.

[orange]

Try this one
INSERT INTO ProjectsTable ( NAME, REFERENCE, NAME2 )
SELECT ProjectsTable.NAME
, ProjectsTable.REFERENCE
, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable
ON ProjectsLinked.NAME = ProjectsTable.NAME
WHERE
(ProjectsTable.NAME Is Null) AND
(ProjectsTable.NAME2 ="Main Project" OR ProjectsTable.NAME2="Active Centre")
OR NOT
(ProjectsTable.REFERENCE="XXX99" AND ProjectsTable.REFERENCE ="XXX98" AND ProjectsTable.REFERENCE ="XXX01") ;

[ssanfu]

So what would be the best method if within my query I dont want to specifically add certain records to my table? As for name columns I created this before I knew there were reserved names I will amend that to be more specific.

Did you try the example from Orange??
Try the select portion first to ensure that all of the records are being selected, then add the INSERT part.

[shabbaranks]

Hi,

I tried what orange suggested and returned blanks. I have made slight progress I can avoid importing the columns I dont want by using Like but its still importing everything again when I run the query....

INSERT INTO ProjectsTable ( NAME, REFERENCE, NAME2 )
SELECT ProjectsLinked.NAME, ProjectsLinked.REFERENCE, ProjectsLinked.NAME2
FROM ProjectsLinked
WHERE (((ProjectsLinked.REFERENCE) Not Like "XXX01" And (ProjectsLinked.REFERENCE) Not Like "XXX99" And (ProjectsLinked.REFERENCE) Not Like "XXX98") AND ((ProjectsLinked.NAME2) Like "Active Centre")) OR (((ProjectsLinked.NAME2) Like "Main Project"));

[orange]

Make an mdb version of your data base and post it so we can work with the copy.
Remove anything private/confidential.

Do a compact and repair and zip the mdb.

[shabbaranks]

When you say mdb its accdb, hope this isnt a problem - thanks.

[orange]

Well I have Acc2003, and ssanfu indicates he has Acc2000. accdb is new in Acc2007. So we can not open/read/use an accdb.
In Acc2007, you have an option(somewhere) to save a copy of your database in mdb format.

[shabbaranks]

It wont let me convert to an older version of Access due to the features and settings of the database which I have used.

[ssanfu]

It wont let me convert to an older version of Access due to the features and settings of the database which I have used.

OK, then... back to basics.

First, you should use brackets around "NAME", since it is a reserved word.

Next, try this:

SELECT ProjectsTable.[NAME]
, ProjectsTable.REFERENCE
, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable 
ON ProjectsLinked.[NAME] = ProjectsTable.[NAME];

If that returns records, then add a WHERE condition:

SELECT ProjectsTable.[NAME]
, ProjectsTable.REFERENCE
, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable 
ON ProjectsLinked.[NAME] = ProjectsTable.[NAME]
WHERE
(ProjectsTable.[NAME] Is Null);

Again, if the correct records are returned, ass another WHERE condition:

SELECT ProjectsTable.[NAME]
, ProjectsTable.REFERENCE
, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable 
ON ProjectsLinked.[NAME] = ProjectsTable.[NAME]
WHERE
(ProjectsTable.[NAME] Is Null) AND
(ProjectsTable.NAME2 ="Main Project" OR ProjectsTable.NAME2="Active Centre");

Then add the last condition:

SELECT ProjectsTable.[NAME]
, ProjectsTable.REFERENCE
, ProjectsTable.NAME2
FROM ProjectsLinked LEFT JOIN ProjectsTable 
ON ProjectsLinked.[NAME] = ProjectsTable.[NAME]
WHERE
(ProjectsTable.[NAME] Is Null) AND
(ProjectsTable.NAME2 ="Main Project" OR ProjectsTable.NAME2="Active Centre") AND NOT
(ProjectsTable.REFERENCE="XXX99" AND ProjectsTable.REFERENCE ="XXX98" AND ProjectsTable.REFERENCE ="XXX01") ;

Not sure of the how you want the field exclusions order to be. But the thing is to get the select working first, then add the INSERT clause.