Can one table's record affect another tables record?

Can one table's record affect another tables record?

Post by YnVzY2hlcj » Sat, 27 Sep 2008 21:53:01


I created a database to track internal audits. I have two tables; the main
table 鈥淐omplAudit鈥?and a reference table with the auditor鈥檚 schedule. In my
entry form for the ComplAudit table, I added subforms to show, by auditor and
month, what departments are assigned. This all works great.

I am wondering - if I add a yes/no field named 鈥渃omplete鈥?to the reference
table, could the entry in the ComplAudit table trigger a 鈥測es鈥?value in the
reference table鈥檚 鈥渃omplete鈥?field? Thus in the ComplAudit table, if Auditor
1, for Sept 2008, audits Dept "A", there is a yes value added to the
reference table within the record that called out Auditor 1, Sept 2008, Dept
"A". Is this possible? Thanks for your opinions and advice.
 
 
 

Can one table's record affect another tables record?

Post by Jeff Boyc » Sat, 27 Sep 2008 23:31:33

It sounds like you are trying to do this within and between tables. If so,
DON'T!

Access tables store data, Access forms display it. Use a form and the rich
event environment to help you manage your data.

By the way, I don't understand your data structure well enough to offer
specific suggestions. Are you saying that your second table is a "child"
table to the "ComplAudit" table?

More info, please...

Regards

Jeff Boyce
Microsoft Office/Access MVP

 
 
 

Can one table's record affect another tables record?

Post by YnVzY2hlcj » Sun, 28 Sep 2008 00:50:01

I would say yes, I believe so. There are relationships created between the
tables. Would it be best to add a hidden checkbox to the form and write code
for it? Something like if value of fields SchdMonth, AuditorNumb, and Dept =
a record in reference table then check box = yes or true? Better yet those
three fields must = a record in the reference table.

The reference table fields are MnthSchd, AuditorNumb and three department
fields labeled Audit1, Audit2, and Audit3. Each record in the ComplAudit
table should equal the SchedMonth, AuditNumb and one of the three department
fields.

Would this code be in the before update event? And, would you be able to
help me write it? I really appreciate your insight on this. Thanks.
 
 
 

Can one table's record affect another tables record?

Post by Jeff Boyc » Sun, 28 Sep 2008 03:01:26

sually, it is YOU that creates relationships between the tables. Since
your tables reflect the data, the relationship(s) reflect the data. What
are the relationships between your two tables?

Based on your description of 'repeating fields' (i.e., "Audit1, Audit2,
Audit3"), I suspect your table structure is an attempt to replicate a
spreadsheet, rather than use the relational design available in Access. In
fact, unless your tables are well-normalized, both you and Access will have
to struggle to make use of Access' relationally-oriented features/functions.

Until the table structure is cleaned up, it doesn't make much sense to be
working on cleaning up the forms/procedures...

Folks here in the newsgroups are largely volunteering their time. If you
need someone to help you build an application, you may want to look to hire
someone. If you have specific questions about what you've already
done/tried, this is a great place to post them.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"buscher75" < XXXX@XXXXX.COM > wrote in message
news: XXXX@XXXXX.COM ...