Table Relations Assistance Please 1

[seb2]

Looking for advice with the following relationship....

Primary Tables
Filing(Number, Title, Analyst,etc)
Form(FormNumber, Title, Type, Desc, etc)
State(Name, Abrv, Address, etc)

Bridge Tables
FilingForm(FilingNumber, FormNumber)
FilingState(FilingNumber/FilingFormNumber??,State, MailDate, ApprovalDate, etc)

*A 'filing' is a compilation of 1-many 'forms' that is sent to states for approval.
*A 'filing' may be sent to 1-many 'states'
*A 'form' may be in 1- many 'filings'
*A 'state' will receive many 'filings'

I'm not confident of how the tables should be set up to allow for the multiple many to many relationships.
I'm in the process of trying to sketch making my access forms for this relationship and it needs refinement.

Please feel free to ask questions to help clarify.

Thanks!!!!

 

[LittleSmudge]

FilingState(FilingNumber/FilingFormNumber??,State, MailDate, ApprovalDate, etc)

Becomes

FilingState(FilingNumber,State, MailDate, ApprovalDate, etc)

and you're sorted.

A State can receive many Filings
A filing can contain many Forms
Any one of those forms could appear in multiple filings
Any filing could be sent to multiple state.

If the four lines above are fair representations of your problem then

State links to StateFile links to File
and
File links to FileForm links to Form



G LS

 

[seb2]

Been out of the office for a couple of days....

Yes, I agree with the scenaro. It is the same that I had originially... but while I was creating my forms I came across some problems...

Problem ... A State MAY request changes to a Form, if so then a new 'state specific' form is created. Altering tblForms to the following will allow queries to be run to link back to the initial form filed. But how to handle for the user interface????

tblForms(Number, Title, Type, Desc, StateSpecific(y/n), SSReason, state, MainFormNum(the one it's replacing)

Suggestions??!? Need more info?

 

[LittleSmudge]

Rather than call the last field in tblForms "MainFromNum" call it ParentForm and you instantly have the concept of 'inheritance' beloved of all OO programmers.

Also, If this newly generate form is part of a new filing collection, which inturn then only goes to the one state requesting the new form then the state can be obtained by following the Relational links from tblForm-tblFiling-tblState. You do not need to store the state in the tblForm. However, if the new form could be used by other States in the future then you do need to keep the field.


All your forms that display tblForm data can have the SSReason, State, ParentForm controls set to Invisible.
In the form's On_Current event check the status of StateSpecific and make the controls visible if set to True.

Also change visibility status on the StateSpecific On_Change event.

The in the ParentForm Double_Click event open a pop-up form that will allow the user to select the parent of this form from all of the available forms.

QED?


G LS

 

[seb2]

Sounds good and seems to be working. I really appreciate your assistance!

There is the possibility that a StateSpecific form may be for more than one State. So I assume that I should add it as a new separate Form and add it the Filings' ~ FilingForm.

Questions:
1 - How do I set up the 'child form' to inherit from the 'parent' in Access? Do I clone the Parent?

2- If the user creates a filing adding the forms to be mailed and later has to add a state specific form, how do I hide the fact that the parent form was filed there initially. I could just add a SubForm to the frmStates that would include StateSpecific information.

 

[LittleSmudge]

Q1 Ohh now I fear I'm getting confused

I thought the forms that when into filings were Word type forms rather than Access Forms.

You make a child form by opening the Parent form in Word, doing the changes and saving as the childName

You keep a record of that by storing the Parent's FormId in the tblForm.Parent field



Q2
You can have an option to delete one record from the list.
One idea would be a button on the form that reviels a hidden list box and a "Go Do Delete" button
Select the entry to be deleted from the list box and then hit the "Go Do Delete" button

Button On_Click event then opens a record set on the joining table and removes the matching record.

Then makes all these controls nonvisible again.






G LS