Update Query problem

[TooManyReports]

I am trying to use this query to update a table, but it tell me,"Operation must use an updateable query". What am I missing? The field is a Yes/No field that is going to be updated.

UPDATE [Daily Change] INNER JOIN DW_PRICE ON ([Daily Change].[SCUST#] = DW_PRICE.[SCUST#]) AND ([Daily Change].LINE = DW_PRICE.SLINE) AND ([Daily Change].PLCD = DW_PRICE.SPLCD) AND ([Daily Change].SUBC = DW_PRICE.SSUBC) AND ([Daily Change].[JOBBER TYPE] = DW_PRICE.SDTYPE) SET [Daily Change].Added = Yes;

 

[vbaInet]

Too many joins or TooManyJoins

Or, is Daily_Change or DW_Price a query?

 

[TooManyReports]

Really?!? Too many joins? what a piece of junk! I need those joins to identify the correct items to update. Neither one is a query, both are tables.

I have another update query that is using the same amount of join. Both are tables also.

UPDATE tblFlexPricePrevious INNER JOIN tblFlexPrice ON (tblFlexPricePrevious.CUSTOMER = tblFlexPrice.CUSTOMER) AND (tblFlexPricePrevious.LINE = tblFlexPrice.LINE) AND (tblFlexPricePrevious.PLCD = tblFlexPrice.PLCD) AND (tblFlexPricePrevious.SUBC = tblFlexPrice.SUBC) AND (tblFlexPricePrevious.[TYPE] = tblFlexPrice.[TYPE]) AND (tblFlexPricePrevious.[% OFF] = tblFlexPrice.[% OFF]) SET tblFlexPricePrevious.[MATCH] = Yes;

 

[vbaInet]

Yep, the joins are too complex or illogical so it causes your recordset to become read-only.

 

[TooManyReports]

any suggestion on how to get this to go?

 

[vbaInet]

I don't think you've worked out what you want to update so I advise that you view the data in a SELECT query first:

SELECT [Daily Change].Added 
FROM [Daily Change] 
INNER JOIN DW_PRICE ON ([Daily Change].[SCUST#] = DW_PRICE.[SCUST#]) 
    AND ([Daily Change].LINE = DW_PRICE.SLINE) 
    AND ([Daily Change].PLCD = DW_PRICE.SPLCD) 
    AND ([Daily Change].SUBC = DW_PRICE.SSUBC) 
    AND ([Daily Change].[JOBBER TYPE] = DW_PRICE.SDTYPE);

... and if possible reconsider your approach.

 

[jdraw]

Please show us your tables and relationships. Perhaps there are some structure issues? Or SQL options?
Also, many will advise you against field and object names with special characters (% #).

 

[TooManyReports]

Please show us your tables and relationships. Perhaps there are some structure issues? Or SQL options?
Also, many will advise you against field and object names with special characters (% #).

All tables are shown. Daily Change and Dw_Price are tables. No other relationships in place at this time. No SQL options are being used that I have selected. I would love not to have special characters, but on the DW_PRICE table is a table that I cannot control and out on a A/S400 system.

Maybe explaining the method to the madness will help. I use DW_Price (shows discount levels) join it with DW_MASTER (Customer table) and join it DW_Store (store locations) to make a table call tblFlexPrice (current days info) then on the next day I make it a table call tblFlexPricePrevious and add a yes/no column to it. Then remake tblFlexPrice for the day. I use an update query called MATCH(shown above) to identify records that were there from the day before. I then use a query called "add nonmatch" (append query) that added records to a table called Daily Change. From this point - this is where the problem update query is coming in - I need identify what records were added to the table call DW_Price in the table Daily Change, so I can tell what records where added and which ones were deleted from the DW_PRICE table.

I hope I explained what I am trying to do well. Thanks for the help so far.

 

[vbaInet]

Ok, but is the SELECT statement displaying the records you would like to change?

 

[TooManyReports]

well, no because the you need to add in customer, line,plcd subc, and jobber type ( the joins) to see exactly what records are going to updated.

 

[vbaInet]

I'm talking about the SELECT statement I gave you in post #6.

 

[TooManyReports]

I forgot to mention that Added is currently null

 

[vbaInet]

I forgot to mention that Added is currently null

That's fine. Does the SELECT statement in post #6 return any records?

 

[TooManyReports]

If I add those joins field to the select statement then I am sure about what is going to updated. Yes, if I go by a count of the amount of records to updated then yes, it does display what I think is the correct amount.

 

[TooManyReports]

Would the fact that I cannot update DW_Price (no permissions - A/S400) have anything to with that I cannot update an unrelated table(Daily Change - local table)?

 

[vbaInet]

Are you able to edit and save the Added field via the SELECT query in post #6?

What is the reason why the DW_Price field is not updateable? The following link lists out the possible cases:

http://allenbrowne.com/ser-61.html

Point 8 is the most likely case.

 

[TooManyReports]

I can update the field in the select, but I need to "clear" the fields first then I can manually update them. If I rerun the query, after I manually update the field, I cannot change the field again, until I do a "sort select to cleared" on the Added field, and then I can update the field again manually.

 

[vbaInet]

Can you upload a stripped down version of your db so I can have a look.

 

[TooManyReports]

I would but I don't think my company would be very happy if i did that. Plus I would need to strip down a 1.5gb database and you would need to simulate a a/s400 database connection that would be not so easy to do. It is the AS400 part that is giving me the problem.

 

[vbaInet]

All I need is the two tables in concern, the query and some bogus data.

I don't want the actual db.