relationship design

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

hp9500 - 19 Jul 2005 21:07 GMT

Hi,

I have an orders and an order details table with a one to many relationship
based on a work order ID. I also have a location information table that
includes addresses; the location name is the primary key in this table. The
Order details table lists the A location and Z location for each order in
separate fields that are indexed and allow duplicate values. I query my
location information table via a combo box when users select the A and Z
location fields of the order details table in an order form.

I am designed a separate form based on the orders table with a subform for
the order details table. I am trying to display the location information
that accompanies both the a and z location fields in two more separate
subforms from the main form. For example, if the A loc is Philadelphia and
the Z loc is Los Angeles, one subform below will list the address in
Philadelphia and another subform below will list the address in Los Angeles.
I can't seem to create a one-to many relationship because the location name
in the loc info table is the one side of the relationship but both the A loc
and Z loc are the many side and they are in the same table. I can't embed
the location info subforms in the order details subform in order to use the
link master/child fields options b/c the order details subform is a
continuous form. The loc a and loc z info subforms are also continuous
forms b/c there may be several order detail records to an order and each
order detail record may pertain to different a and z locations. So there may
be a subform listing two or three rows of detail records and two other
subforms that list several rows of addresses each. I'm not sure how to
approach this; whether I can use queries, macros or code to achieve the
form's design goals or I need figure out how to build the correct
relationships. All help is gratefully appreciated.

Reply to this Message

tina - 20 Jul 2005 01:21 GMT

your table relationships sound correct to me. just to clear this up at the
start: hopefully you are not using any Lookup fields in any of your tables.
if you are, convert them back to ordinary fields; you don't need them and
they just cause a lot of problems.

you *might* be able to take advantage of the LinkChildFields and
LinkMasterFields properties of your two "address" subforms.

first, get the correct name of your OrderDetails subform *control* (it may
be the same as the name of the subform itself, or it may be different). to
get the name: open the main form in design view. click once on the
OrderDetails subform (within the main form design view) to select it. in the
Properties box, click the Other tab and look at the Name property. that's
the name of the subform control.

next, in the "A address" subform, set the LinkMasterFields to
Forms!MainFormName!OrderDetailsSubformCONTROLName.Form!LocationAComboBoxName
(note that the above all goes on one line.) substitute the correct form,
subform control, and control names, of course.
set the ChildLinkFields to the primary key field of the "A address"
subform's underlying table/query.

in the "B address" subform, do the same except point to
LocationZComboBoxName
using the full syntax, same as above.

hth

> Hi,
>
[quoted text clipped - 25 lines]
> form's design goals or I need figure out how to build the correct
> relationships. All help is gratefully appreciated.

Reply to this Message

hp9500 - 20 Jul 2005 15:31 GMT

Thanks tina, the linkchild/linkmaster fields did work when using the
declarations you suggested. That is exactly how I had hoped for those
controls to work.

Any suggestions why the A address subform & the Z address subform don't
display multiple records when multiple records are displayed in the
orderdetails subform? I have both of these forms set as continuous forms
with the labels in the form header and the controls in the details section of
the subform.

Thanks again.

> your table relationships sound correct to me. just to clear this up at the
> start: hopefully you are not using any Lookup fields in any of your tables.
[quoted text clipped - 61 lines]
> > form's design goals or I need figure out how to build the correct
> > relationships. All help is gratefully appreciated.

Reply to this Message

tina - 20 Jul 2005 16:33 GMT

> Any suggestions why the A address subform & the Z address subform don't
> display multiple records when multiple records are displayed in the
> orderdetails subform?

the address subforms are behaving correctly. no matter how many records are
displayed in the OrderDetails subform, only one record at a time is the
"current" record (that's why a form has a Current property, to refer to the
record that currently has the focus). so, in the address subforms, the
LinkMasterFields property settings are referring to only the values of those
fiels *in the Current record of the OrderDetails subform*.

hth

> Thanks tina, the linkchild/linkmaster fields did work when using the
> declarations you suggested. That is exactly how I had hoped for those
[quoted text clipped - 24 lines]
> >
> > next, in the "A address" subform, set the LinkMasterFields to

Forms!MainFormName!OrderDetailsSubformCONTROLName.Form!LocationAComboBoxName

> > (note that the above all goes on one line.) substitute the correct form,
> > subform control, and control names, of course.
[quoted text clipped - 44 lines]
> > > form's design goals or I need figure out how to build the correct
> > > relationships. All help is gratefully appreciated.

Reply to this Message