Update query – Criteria when a child forms column for statusID is all equal to or greater than X number

Hey all! I’ve got a set of orders thats managed by two tables, one being the order table that hosts the order and customer information and then an order products table that has all the products linked to that order. I have Order Statuses for each product ordered in the orderproducts table and what i’d like to do is have an update query that changes the overall order status to a different number when the all the products in that order are say equal to or greater than statusID 6 (dispatched). I’m trying to work this out I think its something like having an if statement that checks after a status of a product is updated but i’m not sure how to write it…:(

Psuedocode i guess

If StatusID.column(?) =< 6 Then

DoCmd.OpenQuery “Update status query that changes the Master order ID status to ‘3 (complete)’

Is this possible am i even close? Thanks 🙁

Update: I tried to work out a way around this using queries while i waited for smarter people than myself. I created a query that counts the total records using Count on the ID which works, and a column that adds up the status IDs. I then tried to create an update query based on that query and a table and did StatusTotal/IDCount and set the criteria to 6 and then the only field i wanted to update was the Order status to 2 (with the criteria here set to 1 as well so it only changes pending orders to complete) but it says the query i’m using isent an update query so it wont let me do that either 🙁

submitted by /u/snugglewalrus
[link] [comments]

Posted on