Table Design Architecture - Form Challenge

T

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 &&&&&&&&&&&&
 
T

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Table Design - Relationships 4
tricky question 4
Design Confusion! 0
Data Entry Form and Subform problems 5
Table Design 3
Combo Boxes & Filtering Value 4
Table Design Question 3
How to normalize this data 1

Top