Unanswered: Help with Table Design and Relationships
Background: My database is designed to record information on applications that are coming in. In the main table [tblApplications], I have 5 important fields: [ID], [FacilityState], [FacilityType], [ApplicationType], and [FacilityCounty]. There are 55 different Facility States, 20 Facility Types, 3 Application Types, and numerous different counties. I also have a table of contacts [tblContacts]. It is a list of people to contact when the applications are processed.
Problem: I need the database to automatically determine the correct contact based upon [FacilityState], [FacilityType], [ApplicationType], and [FacilityCounty]. Each application that comes in can only have one entry for each field but the contact may cover multiple (or all) counties in multiple states for only two of the 20 Facility types, and only one application type. Sorry if this is confusing. This has been causing me a headache trying to figure out the best way to do this. Please help! I would be happy to give more details of my scenario.
It appears that you might have multiple records of [FacilityState], [FacilityType], [ApplicationType] etc. that are associated with a single contact. Perhaps like one person or company buying 5 different cars. Does that sound right.
If so, then this is what I do.
I would have a form that has the contacts and also a form for the table/query that is for [FacilityState], [FacilityType], [ApplicationType] etc.
If applications come in from John Smith then I go to his record and click on a macro which opens the form for [FacilityState], [FacilityType], [ApplicationType] etc. and it opens it at a new record. The macro would also set the vale of some of the Fields in [FacilityState], [FacilityType], [ApplicationType] etc. with John Smiths ID Number, name etc. I would then enter the various details for Facility Type etc. If there were two applications for John Smith then I would close the [FacilityState], [FacilityType], [ApplicationType] etc. form down and reopen it for a another new record.
This is exactly what we have for applications for insurance where there might be several benefits for one person. Then we can either open all/or selected [FacilityState], [FacilityType], [ApplicationType] etc. records that apply to John Smith or we can come the other way and pull up all the people associated with a certain type of application.
The contacts table is actually a list of people in the State Office that will either approve or deny the application, based upon our recommendation. We have a list of 200 or so of them and only one will be forwarded our recommendation. The contact we send it to is decided upon [FacilityState], [FacilityType], [ApplicationType], and [FacilityCounty] of the application.
Mike, does your advice still apply then? Or does that change things?
Okay... I think I have a small grasp on what is going on here. I also think I may have a solution.
It sounds to me like you have two tables. One main one then a contacts one is that correct?
It sounds to me like you may need a couple of more tables to make these relationships work.
You need to send the 'data' for approval to ContactID of 1 IF [FacilityState], [FacilityType], and [FacilityCounty] = ContactID 1's Area. Is that correct? If those three fields = ID 2's area then you want to send it to him right?
If I am seeing this correctly I am thinking 3 new tables would help.
Those tables should be related to the contacts table. That way you can then create a query that says if The FT, FS, and FC are all equal to ID 1 then contact ID 1.
I dont know if I am making sense. Please feel free to question me further on my theory.
Mike: You are correct in that there is another contact from the facility where the application is coming from. He is being entered into the Application Table along with all of the other application info.
JS/All: You are correct with the existing table arrangement. And I think your theory is making sense to me. But are you saying that I need to create a separate record in a table for each possible scenario?
Example: If John Smith is the contact for Application Type = Initial; Provider Types = 1, 5, 7, 10, and 25; and States CA, NV, and CO. Also he handles all counties in NV and CO but only Placer, Sacramento, and San Joaquin counties in CA.
Then the table to look like this (the ";" to indicate separate fields): ID;Contact[John Smith];AppType[Initial];ProvType;FacState[CA];FacCounty[Los Angeles] and so on to handle each scenario? Please say no.
Teddy: There could be 2200 different combinations if the contact where only decided by facstate/factype/apptype. 55*20*2 = 2200. Do I have to make a table with all the combinations? That would take a very long time. And what if a new factype is added? I would have to redo the table.
I would have to say definitely not. What I was saying was create a table with all the states, create a table with all the types, and create a table with all the counties. You should let a query do the possible matches. You should use the ContactID as a foreign key in your 3 tables I just mentioned. So you know ID 3 (Jon Smith) = State (CA) Type (whatever) County (Whatever)
Actually the ContactID should only be in the State... and possibly the county table.
I dont know how you have your regions devided up. If there is that much depending on the type of application then maybe you need the contactID in there but I think that would make you basically create every possible scenario.
Well, if there's no rhyme or reason to how these contacts are affiliated, I don't see how you can tell a computer what rules to use. It sounds like you need to put some serious thought into looking at an enterprise level platform here. I don't think access is going to do what you want it to do.
Basically you've said that the only deciding factors for a contact is the combination of state, provider type, facility and application type. If those are the four criteria you NEED to use, then yeah, you're looking at 2200+ entries.
yes and no... if there are 10 different ones then one of them is probably tied to a certain county right? so maybe you should use the contacts in the county. and link the county and state tables together.
I believe you are on the right track...
Originally Posted by debruinsm
Ok. I'm getting this. So what if CA has 10 different contacts? Do I then need 10 records for CA in the State table? One for each contact?
I would have a table that has the details of the person making the application and this would be a unique record.
I would then have a second table to hold the application details. To do this I would I simply have a macro that opens a form that is based on the table for holding applications and open that form at "new record". When the form was opened then some setvalue actions would put the Applicants ID number and any other of his details that you might want in the record for the Application.
I would then have a third table and form that held the details of the Contacts, that as I remember is the person who will approve the application. Like the application table/form this form would alos be opened as new record and the applicants ID number would be entered via SetValue. The reason I would have Contact as a multi record relating to the applicant is that i am assuming that the applicant could make other applications in the future and the Contact could be a different person second time around.
I assume that when an application is made there are probably dates entered and then given those dates that were entered there will be things that should happen by some future date. Since all three forms have the applicants ID number then one of the forms can open one of the other forms based on dates.
If Contact could be a few different people that are involved during the application process then the separate table will handle that OK.
This is roughly how we do it with inurance applications and benefits.