Table Design - Relationships

  • Thread starter Thread starter Tom Bock
  • Start date Start date
T

Tom Bock

I need some feedback w/ table designs:

This is the structure:
1. tblFacility
2. tblAccount
3. tblBills

Each facility has 1 or more accounts.
Each account has 1 or more bills.

What is the best way to create the relationships? I believe I need to
create a junction table.

If yes, will the junction table have all FKs to their PKs?
Or should I create 2 junction tables.... #1 between Facility & Account, and
#2 between Account and Bills?

Any advice is appreciated.

Thanks,
Tom
 
Tom,

You just need to create links between tblFacility and tblAccount, and
between tblAccount and tblBills.

The relationships you define depend entirely on their real-world
relationships. Can a facility have a bill without an account? Based solely
on what you've identified thus far, the following relationships may suffice.

tblFacility
FacilityID 'Autonumber - Primary Key
FacilityName
'--- other columns

tblAccount
AccountID 'Autonumber - Primary Key
FacilityID 'Long Integer - Foreign Key to tblFacility.FacilityID
'--- other columns

tblBills
BillID 'Autonumber - Primary Key
AccountID 'Long Integer - Foreign Key to tblAccount.AccountID
'--- other columns

The relationships would be:
tblFacility.FacilityID (many) --> tblAccount.FacilityID (one)
tblAccount.AccountID (many) --> tblBills.AccountID (one)

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
 
Graham:

Thanks so much for providing me the structure and information in this
matter.

This works great!!! I truly appreciate this.
 
Graham:

I now realize that I have a problem with the forms. I followed your
instructions and designed the tables as suggested.

I now wanted to use forms w/ subforms to enter the data. However, no matter
how I design the forms, I always get errors, e.g. "Forms not updatable" or
"Missing Index".

Looking at the raw tables via a combination query, all records are lined up
properly and I have the accurate count of records.

Any suggestions as to how I must string them into a form?
 
Tom,

The form might not be updatable if you failed to include the Primary Key for
each record, or if you joined tables in a query.

If you're using subforms, it is a good idea to bind the main form to one
table, and the subform to another, setting their
LinkMasterFields/LinkChildFields to the main form's Primary Key.

Graham R Seach
Microsoft Access MCP, MVP
Sydney, Australia
 
Back
Top