table theory

[Guest]

i'm reworking my tables and seem to have a problem. I have 4 table -
Contracts, TaskOrders, Reports, and ReportDetails. Okay this gets cloudy so
help me out, Each contract will have multiple task Orders, each task order
will have multiple reports, each report will have many details.

I thought a table for just contracts would be good because it would cut down
on having contract number NX1 repeated X number of times. A table just for
Task orders for the same reason, which is linked to contract table by a
foreign key of contract number. TO table is the same way. After that I
kinda get lost.

I don't know how to link the TO table with the report table and have it
recognize that report 1 is attached to task order 1, attached to contract 1,
and then report 2 attached to task order 1, attached to contract 1. I know
this seems confusing, but I've struggled with this. Any suggestions.

 

[Arvin Meyer [MVP]]

In a drill down you need a foreign key of the field of the primary key of
the previous table. So:

tblContracts
ContractID - PK

tblTaskOrders
TaskOrderID - PK
ContractID - FK

tblReports
ReportID - PK
TaskOrderID - FK

tblReportDetails
ReportDetailID - PK
ReportID - FK

Now you join tables on their keys, so the joins go:

ContractID >>> ContractID
TaskOrderID >>> TaskOrderID
ReportID >>>
ReportID
Hope that clears it up for you.

 

[Guest]

That really clears it up for me. Thanks. One more question though. Access
seems to only allow one auto number per table, so is it going to be up to my
data entry people to make sure they get the ID fields right (the ones that
can't be auto numbered)? Or is there a convention around that?

 

[John W. Vinson]

That really clears it up for me. Thanks. One more question though. Access
seems to only allow one auto number per table, so is it going to be up to my
data entry people to make sure they get the ID fields right (the ones that
can't be auto numbered)? Or is there a convention around that?

Users should never even *SEE* autonumber values, or numeric ID's, much less
enter them! In addition, Autonumber values are meaningless, uncontrollable and
arbitrary - there are good reasons that Access only allows one, because having
more would be utterly useless!

You would use a Form with combo boxes to let the user select - by name - the
foreign key values, and store the (invisible) ID number into a Long Integer
foreign key field.

John W. Vinson [MVP]

 

[Arvin Meyer [MVP]]

That really clears it up for me. Thanks. One more question though.
Access
seems to only allow one auto number per table, so is it going to be up to
my
data entry people to make sure they get the ID fields right (the ones that
can't be auto numbered)? Or is there a convention around that?

The autonumber is on the Primary Key, the actual datatype of an autonumber
is a long integer. On the foreign key side there are many records that can
have the same foreign key. Example: 1 contract, many task orders. This is
what we call a 1 to many relationship. So we just add the ContractID field
to the task order table. If we use a bound subform to display the many side
of the relationship, built-in Access code will fill the data in the foreign
key field when the Link Master/Child property is set (this happens almost
automatically when using the subform wizard)