Auto update datafield

10 views
Skip to first unread message

Mail Merge Help

unread,
Oct 9, 2009, 5:49:01 AM10/9/09
to
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

unread,
Oct 9, 2009, 8:04:32 AM10/9/09
to

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.

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-formscoding/200910/1

sumo...@gmail.com

unread,
Mar 17, 2015, 4:53:53 AM3/17/15
to
all the best of luck for me all work done!!! by almighty allah. pray for me my work have to success and done.

sumo...@gmail.com

unread,
Mar 30, 2015, 8:38:06 AM3/30/15
to
Reply all
Reply to author
Forward
0 new messages