Relationship Problems

[buttercup4901]

I have a database with a Case table that has fields for 10 documents (Form01 thru Form10) or document serial numbers.

I have 23 different documents (FormA thru FormW), each is set up as a table. Each table has a serial number set so that the first character represents the form’s identity A thru W (so FormA’s serial numbers are A0001, A0002, A0003, etc.)

The Relationship between the Case table and all of the 23 Forms is thru the Cases’ serial number field.

Here’s where it gets confusing (for me, anyway)…

-- I need to be able to create new forms from the Case layout. That can be attached a forms submenu, if necessary.

-- Each form’s date and title needs to list on the Case layout in sequential order by date.

-- A Case can have duplicates of the same form type.

Example:

CASE # 53234

Form01: #K00668 Withdrawal Order 07/19/2006

Form02: #A00027 No Hearable Issue 08/06/2006

CASE # 53235

Form01: #J01237 Agency Withdrawal 12/09/2007

CASE # 53236

Form01: #E66389 No-Show Dismissal 02/04/2007

Form02: #I00032 Dismissal Rescission 02/10/2007

Form03: #E66390 No-Show Dismissal 02/23/2007

So each time a new form for a case is created, no matter which of the 23 different kinds of documents it is, it needs to list out just like in the example. My ideal would be to create each form only one time and use it as a template, which is why I thought of giving each its own table. But I just can’t seem to figure out the mechanics of how to get this to work. It seems like it should be simple to do, but I sure can’t figure it out.

Any help would be GREATLY appreciated.

[Fitch]

I think it's a mistake to use 10 fields for your forms like that. Better would be to make a join table (CaseForms) that's related to Cases by Case ID. You'd also want to relate Forms to CaseForms by Form ID. From Forms use Max(CaseForms::Serial Number) + 1 to increment the number for new instances of a form. Does that make sense?

[buttercup4901]

Hello, Tom

Your solution makes sense up to a point. I understand the join table concept and agree that makes good sense. Don't understand how Max(CaseForms::Serial Number) + 1 would be used. Would you be willing to go into more detail?

BTW, I remember you from years ago at PMUG when I hung around with Neil McK and worked on MouseTracks.

[Fitch]

If you need a new "E" form, you go to the E form and you'll need a relationship from the form identifier ("E") to a corresponding field in the join table (also "E").

In your example:

E66389

E66390

Max(CaseForms::Serial Number) would be E66390.

Add +1 to get your next serial number, E66391.

Hmmm, I don't recall anyone in PMUG named buttercup...