Table Design Architecture - Form Challenge

[Tom Bock]

In an earlier thread, I raised the questions as to what would be the best
design for a relatively simple relationship such as:

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

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

Although the reply below (within &&&) seems very logical and the results in
a query DO GIVE me the proper results, I am really having a hard time to
string the table in forms and subforms.

Currently, I am unable to enter a new record that would create a new
autonumber in each of the 3 tables.

Here's how I would like to have the form structure:
1. Account - Main form
2. Facility and Bills - Sub form.

Here's why I chose this main/sub form structure.

Let's say I have:
- 10 facilities
- 15 accounts (I may have more than 1 account per medical facility)
- 50 bills (I may have more than 1 payment per account and per facility).

So basically, I want to display:
- the 15 records
- under which I have the 1 associated facility
- and see multiple payments (dataview) for the particular account

Again, currently, I cannot add new records under this structure. Any
advice???


&&&&& TABLE DESIGN &&&&&&&&&&&&

Link between tblFacility and tblAccount, and
between tblAccount and tblBills.


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)

&&&&& TABLE DESIGN &&&&&&&&&&&&

 

[Ted Allen]

Hi Tom,

It seems like the way that you are grouping your data
does not match the way that your data structure is set up.

From looking at your relationships, it seems that the
logical hierarchy would be One Facility/Multiple
Accounts/Multiple Bills per Account. But, on your form
you want to veiw by account, then have bills/facility
info in the subform. But, in order to do this you would
have to set up a source query with multiple 1:many
relationships, and thus it is not editable. Also, it
doesn't seem to be necessary to show the facility info in
the suborm, since it should be the same for each. It
would seem to make more sense to have the facility info
on the main form.

Instead, try basing the main form on a query based on the
Account info, and looking up the associated info from the
facility table. Then, have your subform be based only on
the bills table.

Or, try creating a form based on facility info, then
insert a subform based on the account info, and finally
insert a subform into the Account subform for the Bill
info. This would be the most straightforward.

Post back if I misunderstood your post, or if this
doesn't work for you.

-Ted Allen