How can I relate these tables?

[Jaybird]

Here are the fields for two tables:

The first is HDRPLAT:
CUSTNO
SHIPNO
INVNUM
INVDTMD
INVDTY
SHIPDTMD
SHIPDTY
JOBNO
CUSTPO
CRMEM

The second is LINPLAT:
INVNUM
ACCTLAB
ACCTPRE
ACCTSUF
KEYNUM
LINETOTL
ORDQTY
SHIPQTY

HDRPLAT is a table for invoice records and LINPLAT is a table for invoice
details. I can create a one to many relationship on the INVNUM fields where
it is the primary key for HDRPLAT and the foreign key for LINPLAT. Does it
matter if LINPLAT has no primary key? I can create a multi-field primary key
for this, but do I need to? I want to create subforms based on these tables
and nest the LINPLAT subform within the HDRPLAT subform, so that it will be
easier to relate both tables to my Order Number (=JOBNO) field on my
mainform. The problem I've been having is that one or another of the
subforms wind up being unupdatable.

 

[John W. Vinson]

HDRPLAT is a table for invoice records and LINPLAT is a table for invoice
details. I can create a one to many relationship on the INVNUM fields where
it is the primary key for HDRPLAT and the foreign key for LINPLAT. Does it
matter if LINPLAT has no primary key? I can create a multi-field primary key
for this, but do I need to? I want to create subforms based on these tables
and nest the LINPLAT subform within the HDRPLAT subform, so that it will be
easier to relate both tables to my Order Number (=JOBNO) field on my
mainform. The problem I've been having is that one or another of the
subforms wind up being unupdatable.

It's probably not updateable because you don't have a primary key! EVERY table
should have a PK. In this case a multifield key would seem appropriate.

Note that the Form should be based on the "one" side table, and the Subform on
the "many". If you're trying to base either on a query joining the two tables,
don't - that's defeating the purpose of the subform.

John W. Vinson [MVP]

 

[Jaybird]

I appreciate the quick and straightforward reply. I've been struggling with
this for a while. I've gone ahead and started over.

The main form is based on the Order Entry table and is called frmInvoice.
The primary key for the Order Entry table is called [Order Number].

I have four subforms:

Subform 1 is based on the Contacts table and is read only and is called
sbfContacts. It is related to the main form by the field [CustID]. I've
never had any problems with this subform.

Subform 2 is based on the HDRPLAT table and is called sbfHDRPLAT. The
primary key is [INVNUM]. This is for invoice records. It is related to the
main form by the field [JOBNO], which is equivalent to [Order Number] on the
main form.

Subform 3 is based on the LINPLAT table and is called sbfLINPLAT. This is
for invoice details. It is related to HDRPLAT by the field [INVNUM]. This
has a multi field index made up of [INVNUM], [KEYNUM], and [LINETOTL].

Subform 4 is based on the tblInvoice table and is called sbfInvoice. This
allows us to notate the individual charges per invoice and stores the batch
number and date. It is related to the main form by the field [Invoice
Number].

I've cleared out any dubious queries that were acting as record sources and
related the subforms directly to their underlying tables. I've also renamed
all the subform controls to match the underlying subforms. The problem seems
to be my relationship between subform LINPLAT and HDRPLAT. I cannot get
HDRPLAT to update. I recreated this subform and placed it onto sbfHDRPLAT,
and it seems to work fine. I hope that this answers my question, but I'm not
entirely confident that it has. Let me play around with it and I'll let you
know.

A final question: How do you relate two tables as one to many when the
table on the many side has a multi field primary key?

 

[John W. Vinson]

I appreciate the quick and straightforward reply. I've been struggling with
this for a while. I've gone ahead and started over.

I've cleared out any dubious queries that were acting as record sources and
related the subforms directly to their underlying tables. I've also renamed
all the subform controls to match the underlying subforms. The problem seems
to be my relationship between subform LINPLAT and HDRPLAT. I cannot get
HDRPLAT to update. I recreated this subform and placed it onto sbfHDRPLAT,
and it seems to work fine. I hope that this answers my question, but I'm not
entirely confident that it has. Let me play around with it and I'll let you
know.

If each record in HDRPLAT relates to multiple records in LINPLAT, then you
need either a sub-subform (making HDRPLAT a Single view subform, since you
can't put a subform on a datasheet or continuous form); or do some pretty
tricky stuff with coordinated subforms.

A final question: How do you relate two tables as one to many when the
table on the many side has a multi field primary key?

If the one side has a single field primary key, you would simply use that
field as the Master link field and the corresponding single foreign key field
as the child link field. It makes no difference how many fields make up the
subform's table's primary key.

John W. Vinson [MVP]