Updating a value in a query 2

[plcman]

Hi

I have the following query:

SELECT Products.Material, Products.Description, Products.[Q No], [Inventory Transactions].ProductID, Sum(nz([unitsreceived])-nz([unitssold])-nz([unitsshrinkage])) AS [units in stock], Products.Discontinued
FROM Products INNER JOIN [Inventory Transactions] ON Products.ProductID = [Inventory Transactions].ProductID
GROUP BY Products.Material, Products.Description, Products.[Q No], [Inventory Transactions].ProductID, Products.Discontinued
HAVING (((Sum(nz([unitsreceived])-nz([unitssold])-nz([unitsshrinkage])))<1));

Which returns all products in my database that have no stock. I would like to have the discontinued field (checkbox) ticked on all of these products. Access does not allow me to change any values manually and an update query doesn't like the maths in the above sql.

Anyhelp would be nice.

 

[AlexCuse]

I would write an update query that joins (INNER JOIN) to the query you have above. Then all you need to do is set Discontinued to (1, true, whatever it is) where you have a match. Lets say your query above is called qryProductNoStock for simplicity's sake. You could then run something like this (not tested, assumes ProductID is your primary key):

update Products 
inner join qryProductNoStock
on Products.ProductID = qryProductNoStock.ProductID
set Products.Discontinued = 1

Hope this helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[plcman]

Hi Alex

many thanks for your input,I have tried what you have suggested and I get the error, Operation must use an updateable query.

Any ideas?

 

[AlexCuse]

Can you post the SQL you are using?

Ignorance of certain subjects is a great part of wisdom

 

[plcman]

Hi

I am using the code as posted previously for the first query (find_zeros) and the following which was adapted from what you posted.

UPDATE Products INNER JOIN find_zeros ON Products.ProductID = find_zeros.ProductID SET Products.Discontinued = -1;


Thanks

Regards

Alan Edwards

85% of statistics that are quoted are entirely made up!

 

[AlexCuse]

Hm, I have not seen that error before. I did check one of my queries that sets a check box column to 'checked' status, and I am setting the value to Yes (as I typed it, with no quotes). So you may want to try this?

Ignorance of certain subjects is a great part of wisdom

 

[plcman]

Hi

Thanks again but I can't get rid of the error

 

[AlexCuse]

Hm. I will try to set up a test, but hopefully someone else will have an answer before I get around to that. Sorry I can't be of more help,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[plcman]

Hi

On the Microsoft website I found some info and It seems that you cannot alter data in a query table where any field is based on a calculation.

I wonder if there is another way around this, all I want to do is stop my inventory showing records that have <1 in stock. I was planning to mark the record as discontinued when the last of the stock is removed and then for this flag to prevent the record been selected again.

Maybe someone else can think of a better way?

 

[AlexCuse]

How about this? Its' kind of kludgy but it might work:

update Products
set Products.Discontinued = Yes
where Products.ProductID in (select productID from find_zeros)

If this doesn't work, maybe we can put together an even dirtier one. I thin this will work though.

Hope it helps,

Alex

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

Perhaps this ?
UPDATE Products
SET Discontinued = True
WHERE Not IsNull(DLookUp("ProductID","find_zeros","ProductID=" & [ProductID]))

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[plcman]

Hi

Many Many Thanks to Alex & PHV, both of those solutions work fine.


I am most greatful for your help

 

[AlexCuse]

Glad you got it working

Ignorance of certain subjects is a great part of wisdom