Update query based on Min Values problems

[audrey]

Grrr.... this has been a pain for a week now. Hopefully someone will be able to help. I want to have a query update table values based on the min values of a different query. Here's what I have (well, just the important stuff):

Table: Exhibitors
Field: mrkt_pts

Table: Livestock
Field: Exhibitor_ID
Field: Placing

Each exhibitor can have multiple livestock, thus they can have multiple placings. So I created a query to show only their highest placing animal:

Query: qry_supcomp_mrkt_pts

SELECT DISTINCTROW exhibitors.ID, exhibitors.Name, First(livestock.ExhibitorID) AS [First Of ExhibitorID], First(livestock.[Tag No]) AS [First Of Tag No], Min(livestock.Placing) AS [Min Of Placing], First(livestock.market_animal) AS [First Of market_animal]
FROM exhibitors LEFT JOIN livestock ON exhibitors.ID = livestock.ExhibitorID
GROUP BY exhibitors.ID, exhibitors.Name
HAVING (((First(livestock.market_animal))=-1));

This outputs (Example):

Exhibitor ID | Name | Tag No. | Min Placing
108 | John Doe | 5001 | 1

And here's where I hang up.... I want to create a query that updates the [Exhibitors]![mrkt_pts] according to the Min Placing. For example...

If Min Placing = 1, then update [Exhibitors]![mrkt_pts] = 15
If Min Placing = 2, then update [Exhibitors]![mrkt_pts] = 14
If Min Placing = 3, then update [Exhibitors]![mrkt_pts] = 13
If Min Placing = 4, then update [Exhibitors]![mrkt_pts] = 12
etc..
If Min Placing = or > 15, then update [Exhibitors]![mrkt_pts] = 1

I tried several different variations of the following query [update_qry_supcomp_mrkt_pts] to just update mrkt_pts to 15 where Min Placing = 1:

UPDATE qry_supcomp_mrkt_pts INNER JOIN exhibitors ON qry_supcomp_mrkt_pts.ID = exhibitors.ID SET exhibitors.MarketPoints = 15
WHERE (((qry_supcomp_mrkt_pts.[Min Of Placing])=1));

But I just get the "Operation must use an updateable query" error.

ANY help would be GREATLY appreciated!

Thanks,
Audrey

 

[ByteMyzer]

Try the following query:

UPDATE exhibitors AS e
INNER JOIN livestock AS l1 ON e.ID = l1.ExhibitorID
SET e.mrkt_pts=Iif(l1.Placing<15,16-l1.Placing,1)
WHERE l1.Placing =
 (SELECT MIN(l2.Placing)
  FROM livestock AS l2
  WHERE l2.ExhibitorID = l1.ExhibitorID
  AND l2.market_animal = -1
 );

 

[audrey]

Thanks Byte for the reply!

*Edited

Byte... you are wonderful. It was my mistake thinking your query wasnt working... it works perfectly! When I checked the query I was looking at the wrong placing field. When I realized I was looking at the exhibitor's placing instead of the min value placing for the animal, i saw that it was 100% right on! Thanks again for your help!

 

[khawar]

You can simply use

UPDATE qry_supcomp_mrkt_pts 
INNER JOIN 
exhibitors ON 
qry_supcomp_mrkt_pts.ID = exhibitors.ID 
SET exhibitors.MarketPoints = 16-[qry_supcomp_mrkt_pts].[Min Of Placing];

 

[ByteMyzer]

No, khawar, as audrey said, if you try, Access comes back with "Operation must use an updateable query."

Update queries based on a join to an aggregate query do not work in Access. Trust me, khawar, I do know what I'm talking about.


audrey, you're very welcome, and I'm glad it worked for you.