depicting 1 - M relationship on a form!

[shez]

If i have 3 tables:
personal(id,...)
link(id,addressId,type)
address(addressId,...)

Now when I select all these fields and create a form I get one set of address details but i really want TWO! I can use "field list" in design mode and duplicate address related fields but how would access store them in the database and maintain relationship?

 

[mithani]

Have you tried query, put all three tables in a query and call query in your form.

Mithani

 

[shez]

Have you tried query, put all three tables in a query and call query in your form.

Mithani

would that query also help me to ADD info or just to view info? because if I want to add via form through that query, the query wont return any result?

 

[Pat Hartman]

Use a main form to display the personal information and a subform for the address information. that will allow you to show multiple addresses for one person.

 

[shez]

cheers for the answer, i did do that but i was wondering instead of having a subform could i not have two set of addresses text box? for example:

Type, houseNo, street...

Type, houseNo, street

and possibly Type in one set fixed to HOME and in the other it is fixed to TERM (there can only be TWO types!)

i also thought because there are only TWO types could i not do something like:

personal(id, name, homeAddressId, termAddressId...)
address(addressId,houseNo,street....)

 

[Pat Hartman]

There are only two types now but think how happy you would be if you implemented the 1-m relationship correctly and it became necessary to add a third type. You'll look like a hero as you tell the client - no problem, we'll just add another value to the type table.

If the two types are frequently the same physical address such as billing and shipping, the most efficient solution is actually a m-m relationship. You have a company/person table, an address table, a type table, and a personAddress table but instead of the normal two fields in the relation table, you have a third. That allows you to link the same address twice to a person. Once as the shipping address and once as the billing address. This does take some extra programming though because if an address changes, you need to be certain that it is changing for all roles because if it is not, you would need to add a new address record and change the relation record to point to the new address record. For example:
PersonID
1
2
AddressID
1
2
RoleID
Shipping
Billing
PersonAddress
1, Shipping, 1
1, Billing, 1
2, Shipping, 2
2, Billing, 2

If you want to change only the Billing address for person 1, you need to add address 3 and change the billing relation record to
1, Billing, 3