Table Design - Relationships

[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

 

[Graham R Seach]

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

 

[Tom]

Graham:

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

This works great!!! I truly appreciate this.

 

[Tom]

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?

 

[Graham R Seach]

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