update query for this?

[pronate]

Hi,

Just need to know how to write this SQL...

2 tables, Orders_tbl and Order_detials_tbl.

I have a "closed" yes/No field for both tables. If each order item is delivered, the user will tick "Closed" in the Order_detail_tbl. If all the items are delivered, the Order_tbl should be ticked "closed".

I need a update query to closed the Order_tbl automatically if all its items are closed.

Cheers
Teck Beng

 

[JeremyNYC]

Teck,

It's probably better if you get rid of the "Check" field in the form that's on the "one" side of this one to many join.

One of the big principals of Relational Database Design is that you don't store redundant data, and storing a "summary" field like this is storing redundant data. One of the big reasons for this is to avoid having to do updates like this. If the update fails, all of a sudden you're storing incorrect data.

I would instead write queries that include both tables and check if all of those "Check" fields are yes in the many side table.

If you want to read a great article about designing data structures, check out the Fundamentals article on my web site. It was written by Paul Litwin, an Access guru.

If you have questions about how to write particular questions, post them here.

Jeremy =============
Jeremy Wallace
Designing, Developing, and Deploying Access Databases Since 1995

http://www.AlphaBetCityDataworks.com

Take a look at the Developer's section of the site for some helpful fundamentals.