Results 1 to 14 of 14
  1. #1
    Join Date
    Apr 2004
    Posts
    5

    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.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    What is contacts dependant on? I don't understand how they fit into your schema.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    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.

    Mike

  4. #4
    Join Date
    Apr 2004
    Posts
    5
    Teddy;Mike
    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?

    Steve

  5. #5
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Steve

    We would need a change because Contacts is after the event so to speak. I was thinking Contacts was the person buying the 5 cars.

    So.....Contacts is like the person in the finance company that approves the lease finance for the car or cars. That sound right?

    Do we have a person (or organisations) that sends in the applications.

    In other words do we have

    A customer or applicant

    The customer or applicants....applications

    Then an "approver" who is Contacts.

    If so, this identical to insurance with Contact being the "insurance company or employee or employees" that approve the application for insurance.

    So I am guessing that we have at least two people, that is, Contacts and Applicant that may need to be contacted, depending on the "application" status.

    Is this the picture?

    Mike

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    My question is, what combination of state/facility/app type determine the contact? In a nutshell, that's the relationship between the tables.
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  7. #7
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Good Question

    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.
    FacilityType
    FacilityState
    FacilityCounty

    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.

    Hope I can be of service,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  8. #8
    Join Date
    Apr 2004
    Posts
    5
    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[1];Contact[John Smith];AppType[Initial];ProvType[1];FacState[CA];FacCounty[Los Angeles] and so on to handle each scenario? Please say no.

  9. #9
    Join Date
    Apr 2004
    Posts
    5
    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.

  10. #10
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    H3ll no!

    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.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  11. #11
    Join Date
    Apr 2004
    Posts
    5
    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?

  12. #12
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    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.
    Last edited by Teddy; 05-06-04 at 13:27.

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    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...

    JS

    Quote 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?
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Steve

    Here is what I would do.

    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.

    Mike

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •