Auto update datafield

[Mail Merge Help]

Hi

I have 2 tables that have 1-M relationship.

In the second table that is in the many relationship side. I have many
records with the status field.

How to write a code so that if the [status] field all records in second
table is Completed, then the [Status] field in the first table auto update
to Complete. Else leave as is.

Thanks

 

[PieterLinden via AccessMonster.com]

Hi

I have 2 tables that have 1-M relationship.

In the second table that is in the many relationship side. I have many
records with the status field.

How to write a code so that if the [status] field all records in second
table is Completed, then the [Status] field in the first table auto update
to Complete. Else leave as is.

Thanks

You should be able to just AND them all together.

crap... this is one of the cases where it's harder to explain than to do...
In Access, False = 0 and True = -1. If we just sum up the Yes/No field,
we'll get the exact opposite of what we want. So, if we reverse things by
throwing a NOT in there, and then Sum that, we should be off to the races.

SUM(Not(Status])

So build a query on the CHILD table that includes the join key from the
parent table ( the foreign key) that gets the Sum. (Use a totals query).
Then in the second query, add the parent table and then the totals query.
Join on the PK-FK. Then you should be able to do the update.

Test this out on a dummy table... don't want to mess up your real data on a
mistake.