Relationship Setup, Custom Autonumber, Subforms

[Brinker1975]

Maybe some of you can offer some pointers on my db setup. I've gotten to the
point where I feel like I'm not making any headway.

1) As I see it, it's got two one-to-many relationships, customers,
contracts, and meters. Shouldn't meters be linked to contracts, and contracts
to customers?

2) [CustomerID] is linking "Customers" and "Contracts", but I'm not sure how
to link "Meters" to "Contracts". I don't really want to use an autonumber.
I'd like to use [CustomerID]X, where X equals A, B, C, etc. depending on the
number of contracts (which will be a low number BTW). Is that possible and
how? I have searched all over and not found any detailed explanations.

3) I'd like to make a form that accepts information for all three tables. So
I thought a subform for "Contracts" would work. When I created one, it showed
every contract, not just the one(s) associated with that record. Any advice?

4) "Meters" basically just lists meter numbers & physical addresses. Can I
make that a second subform? It wouldn't make 2 subforms, but maybe if I
modify my relationships it will?

 

[Tim Ferguson]

1) As I see it, it's got two one-to-many relationships, customers,
contracts, and meters. Shouldn't meters be linked to contracts, and
contracts to customers?

So each customer has one or more contracts, and each contract relates to
one or more meters?

What about meter-readings?

2) [CustomerID] is linking "Customers" and "Contracts", but I'm not
sure how to link "Meters" to "Contracts". I don't really want to use
an autonumber. I'd like to use [CustomerID]X, where X equals A, B, C,
etc. depending on the number of contracts (which will be a low number
BTW). Is that possible and how? I have searched all over and not found
any detailed explanations.

Yes: the table Contracts will have a field called Contracts.CustomerID
that refers to the Customers table.

You have a choice for the PK for the Contracts table. The
AlwaysUseAnAutonumber team recommend having an autonumber field called
Contracts.ContractID while the AlwaysUseANaturalKey gang would use two
fields, Contracts.CustomerID and Contracts.SerialNumber which is what you
are suggesting above. The advantage of the autonumber is that you don't
have to worry about allocating it; while with the other version you have
to provide your own code to create the SerialNumber (it's not very hard:
google on this group for "Custom Autonumber"). The advantage of the
SerialNumber approach is that it makes subsequent queries faster and
easier as you will often not have to make joins on the Customer table.

Similarly, with Meters, you can have a three-field PK (CustomerID,
ContractNum, MeterNumber) or a simple autonumber MeterID.

3) I'd like to make a form that accepts information for all three
tables. So I thought a subform for "Contracts" would work. When I
created one, it showed every contract, not just the one(s) associated
with that record. Any advice?

I have to confess a personal hatred of subforms, but I also know that I
am somewhat in a minority. There is no general reason why a subform based
on Contracts would not work, even with an embedded subsubform for Meters.
Extending another layer to MeterReadings would be starting to push the UI
as well as the patience of the user!

You get to see the correct subset of Contracts by setting the
ParentLinkField and ChildLinkField properties of the subform controls.

4) "Meters" basically just lists meter numbers & physical addresses.
Can I make that a second subform? It wouldn't make 2 subforms, but
maybe if I modify my relationships it will?

If you have put the relationships together correctly, and set the forms
and properties, then there is no reason this would not work. It's up to
you, though, whether you think it makes your users' lives easier or
worse.

Hope that helps


Tim F