Adding a record to multiple tables

[Guest]

I am working on a database for my wife. She is a Girl Scout leader and she
asked me to help her develop a database to help her track the records of her
girls as they go through their badges. This database contains about 20 or so
tables. Each table has a group of badges with several yes and no fields to
track the requirements. The relationship between each badge table and a
table containing the scout's personal information is a one to one using the
scout's ID#. This ID# is a manually entered number. In order to populate
all of these tables, I have created a master form from the scout's personal
table with subforms for each of the badge groups.

The issue I am having is when I go to enter in a scout's personal
information and no badge information, I am unable to save the record. It
comes up saying that a record in one of the other tables is required.

How do I get the ID# to populate to all of the other tables without having
to manually type in the ID on all of the subforms? I have tried Enforcing
Integrity and Cascading Updates but this does not work. Any help would be
benefitional.

Thanks

 

[Joseph Meehan]

This sure sounds like a job for Normalize Man (or girl).

It sound not be a problem at all if the tables are fully normalized. Are
they ?

 

[Guest]

I believe they are. Here is an example:

tbl_record contains ID# (pk), LastName, FirstName, Address, etc
tbl_1badge contains ID# (pk), (list of badge group 1)
tbl_2badge contains ID# (pk), (list of badge group 2)
tbl_3badge contains ID# (pk), (list of badge group 3)

so on and so on for 21 tables. I needed to do this because each badge has
12 field related to it and there are 113 badges.

Again, thanks for all the help.

 

[pietlinden]

I believe they are. Here is an example:

tbl_record contains ID# (pk), LastName, FirstName, Address, etc
tbl_1badge contains ID# (pk), (list of badge group 1)
tbl_2badge contains ID# (pk), (list of badge group 2)
tbl_3badge contains ID# (pk), (list of badge group 3)

so on and so on for 21 tables. I needed to do this because each badge has
12 field related to it and there are 113 badges.

Again, thanks for all the help.







- Show quoted text -

sounds like you're not set up quite right...
Maybe

Person(PersonID, FirstName, LastName,...)
Earns(PersonID, BadgeID, EarnDate....)
Badge(BadgeID, BadgeName, BadgeGroup?...)

then you can do all that.

 

[Joseph Meehan]

sounds like you're not set up quite right...
Maybe

Person(PersonID, FirstName, LastName,...)
Earns(PersonID, BadgeID, EarnDate....)
Badge(BadgeID, BadgeName, BadgeGroup?...)

then you can do all that.

That looks about right to me, but since I am not sure exactly what they
OP has or wants to do, I can't be sure. In any case I believe the OP's
current table organization is the reason they are experiencing a problem.
The only thing I can think of that may be making the problem more complex is
that it appears each badge requires various requirements to be completed and
the OP wants to also track the completion of those requirements. That should
be able to be added with one or two additional tables, IF I am guessing
right.