Help! Another Interesting Problem

[pinecrest515]

Dear Forumers,

Thank you again for the help so far. I am relatively new to Access but need to get some things done fast, so appreciate the help.

Suppose I want to update/modify a table called Sheet1 based on results fromm queries Query1, Query2, and Query3.

Here is what I want to do:
1) Add a new column to Sheet1, called Code.



ALTER TABLE Sheet1 ADD COLUMN Code Text(10)

.....somehow I am getting a "query must have a destination field" error from the add column SQL above. Any thoughts into why there is an error?

2) Run Query1. Then update newly created Sheet1 Column called 'Code'...so that for all Sheet1 recordes returned by Query1, set Code = 'Green'.

3) Run Query2. Then update Code column in Sheet1 so that for all records returned by Query2, set Code = 'Green'.

4) Run Query 3. Query 2 and Query 3 may return the same records in some instances. If record falls under Query 3 BUT NOT in Query 2, then set Code = 'Yellow' for record. Else, do nothing.

5) For all records in Sheet1 not in Query1, Query2, and Query3, set Code = 'Red'.

How can I go about accomplishing steps 1-5 above?

Much Appreciated!

[weekend00]

1) when I run the query, it completed successfully without any problem, the field is added as expected.

2345, I am not sure what is your query123, if they are getting data from only one table sheet1, you don't need seperated query. if they are result from other tables, please provide the key relation field with sheet1.

[pinecrest515]

Dear Weekend00,

Yes, I ran the ADD COLUMN query again, and it ran fine.

As for steps 2,3,4,5, the queries get data from the same table Sheet1. However, each query is quite complex, using at least one self join of Sheet1 onto itself, and two of the queries use another level of INNER JOIN on top of that.

Is there a way to say, for example,

Update Sheet1
Set Code = 'Yellow
Where Exists IN Query 3 AND WHERE NOT Exists IN Query 3?

[weekend00]

you need to povide a key (primary key or any field(s) which identify a row)to link sheet1 with the query

[pinecrest515]

How would you privde the key? How would I write that query?

[pinecrest515]

I tried something like this...

UPDATE Sheet1 SET Code = 'Green'
WHERE Query2.[Item Nbr] = Sheet1.[Item Nbr];

Got an error

[weekend00]

UPDATE Sheet1 inner join query2 on Query2.[Item Nbr] = Sheet1.[Item Nbr] SET Code = 'Green'

[pinecrest515]

Thanks Weekend00, you are the best!

However, I am getting some errors:

Query2 in realife is called GreenItems.

When I ran the following query:
UPDATE Sheet1 INNER JOIN GreenItems ON GreenItems.[Item Nbr] = Sheet1.[Item Nbr] SET Code = 'Green';

I get an error saying: "The specified field 'Code' could refer to more than one table listed in the FROM Clause of your SQL statement."


I add Sheet1.Code to Code, and re-run the query,
UPDATE Sheet1 INNER JOIN GreenItems ON GreenItems.[Item Nbr] = Sheet1.[Item Nbr] SET Sheet1.Code = 'Green';

I get the following error: "Operation must use updateable query".

[weekend00]

try to add DISTINCTROW right behind update.

[pinecrest515]

Thank you Weekend00! You are the best!

It works for my step 3. I am still trying to figure out steps 4 and 5.

For step 4, Query 2 (GreenItems Query) and Query 3 (YellowItems Query) may return the same records in some instances. If record falls under Query 3 (YellowItems Query) BUT NOT in Query 2 (GreenItems Query), then set Code = 'Yellow' for record. Else, do nothing.

After step 3, everything under Query 2 (GreenItems Query) would have been coded 'Green', so I tried the following query, but was not able to delete any additional records (base on my test data, I should be deleting 4 records):

UPDATE DISTINCTROW Sheet1 INNER JOIN YellowItems ON [YellowItems].[Item Nbr] = Sheet1.[Item Nbr] SET Sheet1.Code = 'Yellow' Where Sheet1.Code <> 'Green';

[weekend00]

or you may put
Where Sheet1.Code is not null;

[pinecrest515]

That's a good idea, but I still have Step 5, where I color code the rest as RED. The syntax 'WHERE Sheet1.Code is not null' works for step 5. But for step 4, I need to color code all records found in Query 3 that are NOT found in Query 2 (Query 3 is inclusive of Query 2).

[weekend00]

'WHERE Sheet1.Code is not null' will work for all queries if you run them in order.

[pinecrest515]

I ran the following:

UPDATE DISTINCTROW Sheet1 INNER JOIN YellowItems ON [YellowItems].[Item Nbr] = Sheet1.[Item Nbr] SET Sheet1.Code = 'Yellow'
WHERE Sheet1.Code is null;

And got the result!

Thank you, you are wonderful!

[weekend00]

u r welcome