Form with Subform

[jdmcleod]

I am designing a database for an automated audit program which is currently a
paper based system and wanted to get some feed back to determine if I am
heading in the right direction. My database will have two main tables. The
first table will have the audit reference #(primary field), audit section and
the audit procedures. The second table will have information gathered from
the user when he or she completes an audit step. It will have the audit
reference #(primary field), date completed, completed by, reportable
conditions(yes/no checkbox), and descrition of reportable conditions. I am
thinking the best way to set this up is with a form with a subform. The user
will pick the audit reference from a list and the subform will display the
fields in table 2 for them to fill in. I am new to Access and struggling
with this so any help or ideas would be very much appreciated. Thanks and
let me know if you need more information.

 

[JP]

Just to make sure I understand:

Is the first table essentially a "master file" that contains info about how
to conduct each step, with the second table being info about steps actually
conducted? Furthermore, can there be multiple entries in the second table
for one entry in the first table (e.g., different entries when the same
audit procedure is conducted in different places or on different dates)? In
other words, is there a one-to-many relationship between records in the
first table and records in the same table (for each individual record in the
first table, there can be more than record with the same reference # in the
second table).

If that is the case, what you're doing is a pretty standard approach.

However, it's not clear to me from your description that this is the
relationship between the records. For example, you say that reference # is
the primary field in both tables. If that is the case, what distinguishes
between different records in the second table. Is there some primary key
that consists of the reference # plus other things (e.g., user, date,
location, audit assignment) that uniquely identifies each record?

 

[jdmcleod]

Hey JP, thanks for your assistance. You are correct, the first table is the
master table where the audit procedures will be stored and the second table
is where our auditors will enter data about the procedures they actually
performed. There can only be one record in the second table for each record
in the master file. What I am trying to accomplish is to allow the user to
open a form, select from a drop-down list the audit step they worked on and
then enter the results of their testing into the subform. I am new to access
and sort of learning as I go, so any advice you have would be great. Thanks
again.