Data Input Many to Many relationship

[sharkey_lsu]

Hi,



I have created a database with several many to many relationships. One thing I'm having trouble with and would love advice on is how to best handle data input for these many to many relationships. I know that I am getting a little ahead of myself but I want to make sure that I am bulding my relationships correctly so that my forms will run smoothly. I have attached an image of my current cardinality but it is still a work in progress.

There are two scenarios that I would like to provide functionality for.

Ex 1:Create a new Operator and select which clients the operator is associated with. There will need to be the ability to select many operators through a checklist style interface for the end user.
Ex 2: Ability to view the details of a specific Client and add or remove an operator from being associated with that Client.

I am relatively new to access but this is not my first database development project however it is way more complex than previous ones I've developed. Please feel free to digest and comment on the current structure of my tables as I am very appreciative of constructive criticism. I think I'm on the right track but I'm beginning to get lost as I move toward storing actual inspection data and results. I guess since Im already here i will go ahead an ask a question in this regard too. What would be the best method for accomplishing this task:

1. Create a new inspection -> Select Rig to be inspected -> Select Inspection Type -> Select Inspection Sub type -> respond to appropriate inspection regulations. (this is how I think the end user process would unfold)

I am incredibly confused in this regard because I will need the actual inspection items to populate based on the Inspection type as well as the inspection sub-type. If anyone has a good solution that I could build off of before trying to tackle this myself I would be truly grateful. I am certain that there will be a great deal of trial and error otherwise. I will undoubtedly have more questions as the project unfolds and I truly appreciate any help that I am able to recieve from more some of the more experienced developers of this forum. Thanks in advance for any advice.

[June7]

Image is not showing for me.

Options for many-to-many data entry.

1. one form bound to junction table with comboboxes to select from the two source tables

2. main form bound to 1 source table, subform bound to junction table with combobox to select from other source table

3. reverse the source tables from option 2

You might find cascading (dependent) comboboxes useful: http://www.datapigtechnologies.com/f...combobox2.html

[orange]

I'm looking at your relationships. I don't see inspection sub-type. Seems you have junction tables to resolve the Many:Many. I don't understand the link between RigInspectionHist and Inspectiontype. I think you have jonied these incorrectly.

June has listed some options re Forms and populating records.

[sharkey_lsu]

Thanks for the input I will certainly research the possibility of using cascading comboboxes. I have not spent enough time regarding the relationship/tables needed to make the actual inspection work and the more I look at it the more I do not understand how I will make it work. I am thinking that I will need a table for each inspection types' sub-type. The reason being that an inspection of a certain primary type will contain multiple sub-types to be performed. I would love to view a sample database that is used to perform inspections so that I can get a better understanding if this is possible.

[ssanfu]

RE: table InspectionType
It looks like you have the field names confused with the data. The field name should be something like "InspDesc" and the data would be: "PINC", "USCG", "BOP"

As for the Sub-Insp (is that the same as InsCatagory?) see the pic....
Attachment 19632


My $0.02......

[sharkey_lsu]

You are correct, I did have those field names mixed up. The issue that I still have however is that I am not sure how to get an inspection form to populate based on the specific inspection type and category. I have attached an updated relationship diagram for reference of where I was able to get today. I still kept the inspection category out of the inspections type because the list of categories will change based on the inspection type that is chosen. Also I was hoping to store the data for each inspection type (PINC, USCG, ETC) in a respective table in order to keep the number of records down per table as the back end will be split and stored on a server. I am not worried about storing the inspection categories in separate tables however.

Lastly, is there anything that i need to be aware of before things get too far in regard to populating an inspection questionnaire? The ultimate goal of this system will be to not only store all of the data in a highly organized manner, but to also only present the inspector with the regulations that are required during the actual inspection. And to be able to analyze the information on many levels.

Also, I want to go ahead and apologize for any newbie questions i may be asking. This is by far the most complex database I have ever developed and I'm really only 2 days into it. Thanks again for any help that is able to be provided, I really hope that I am able to get this working correctly.