updating tables (1 Viewer)

[Adam1970]

Hi
I hope someone can help, I have 3 tables

PurchaseT - Containing PurchaseID & Status
PurchaseDetailT - Containing PurchaseDetailID, PurchaseID & Complete (Y/N)
ReceivingT - Containing PurchaseDetailID, PurchaseID & Complete (Y/N)

When selecting yes in the complete field in the ReceivingT i want the corresponding complete field in PurchaseDetailT to update to Yes.
then
if all lines are set to yes in PurchaseDetailT for the corresponding PurchaseID, i then want the PurchaseT status to change from default value "Live" to "Complete". If not complete it should remain "Live"
I look forward to your comments

 

[theDBguy]

Hi. Welcome to AWF!

If something can be calculated, it's usually recommended to not do store it in a table. So, if the complete field is selected as yes, then that should be enough to know that the related table would also be completed. No need to set it as completed as well.

 

[tvanstiphout]

Hi
I hope someone can help, I have 3 tables

PurchaseT - Containing PurchaseID & Status
PurchaseDetailT - Containing PurchaseDetailID, PurchaseID & Complete (Y/N)
ReceivingT - Containing PurchaseDetailID, PurchaseID & Complete (Y/N)

When selecting yes in the complete field in the ReceivingT i want the corresponding complete field in PurchaseDetailT to update to Yes.
then
if all lines are set to yes in PurchaseDetailT for the corresponding PurchaseID, i then want the PurchaseT status to change from default value "Live" to "Complete". If not complete it should remain "Live"
I look forward to your comments

ReceivingT - Containing PurchaseDetailID, PurchaseID & Complete (Y/N)

That is a mistake. If I give you a PurchaseDetailID, you can dip into the PurchaseDetailT table to retrieve the PurchaseID, so it should not be in this ReceivingT table.

 

[bob fitz]

Cross-Posted: https://www.accessforums.net/showthread.php?t=88649

Please read: https://excelguru.ca/a-message-to-forum-cross-posters/

 

[Adam1970]

ReceivingT - Containing PurchaseDetailID, PurchaseID & Complete (Y/N)

That is a mistake. If I give you a PurchaseDetailID, you can dip into the PurchaseDetailT table to retrieve the PurchaseID, so it should not be in this ReceivingT table.

Thanks, have fixed this. purchasedetailT is updating.
Now just need an update to the purchaseT only when all corresponding purchasedetailT are marked as complete

 

[plog]

Now just need an update to the purchaseT only when all corresponding purchasedetailT are marked as complete

Nope. To iterate DBguy---if something is calculable you don't store the result of the calculation. You do the calculation to get the result when you need it.

That means you build a query based on PurchaseDetailT to determine if all details are complete then reference that query when you want to know that information for a record in PurchaseT

 

[Adam1970]

Nope. To iterate DBguy---if something is calculable you don't store the result of the calculation. You do the calculation to get the result when you need it.

That means you build a query based on PurchaseDetailT to determine if all details are complete then reference that query when you want to know that information for a record in PurchaseT

Thanks
I need to explain what I'm attempting.
On Receiving Form I have 2 cascading combo boxes.
1. PO number
2. PO items.
I want to set combo 1 to only show PO numbers where the status is live, hence the reason for the status field in the PurchaseT