Results 1 to 11 of 11
  1. #1
    Darkglasses is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    9

    Help I am stuck

    Hi ALl,

    My friend asked me to take on a project and build a database t replace out his paper based file system but I am hitting a brick wall . . .have taken many runs at it.



    The basic is that the database is to hold contact details of clients and their realated services they have taken up. So the first step is to normalize my database and have a good think about the design - engaging what i rember from 10 years ago. So:

    I have 3 tables relating to the clients
    - Client details
    - Partner details
    - Dependants details

    For services
    - Pension
    - Mortgages
    - Life assurance
    - and so on

    Each table has a primary key of their name then ID. However I cannot build or edit a template so that when I have Client details form open, the related partner/dependats details enter do not tally up. I have tried the client ID as foreign key and each tables primary in the client details table, don't work. HELP.

    The closest I got was to create a form with 3 different tabs and tab contained each data entry form (client/partner/dependants) but agin they went in unrealted.
    I tried putting a button on the client details table to open the partner details table and enter details if they had a partner. Didn't work.
    Tried a subform but 2 subforms looks terrible and the dependants would never create properly.

    I am off to read the tutorials on this form as other I have used on Youtube or website still has me struggling to find the gap in my logic.

    Hoping someone can just point is out.

    Thanks

  2. #2
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    What type of information is held in the three client related tables? Can you provide some example data?

  3. #3
    Darkglasses is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    9
    Cheers mate

    Client details
    - Name fields,
    - address,
    - contact numbers email,
    - Personal infor like D.O.B, gender,
    - Health details, smoker (Y/N), general notes
    - Occupation and income details

    Partner
    - Name fields,
    - address,
    - contact numbers email,
    - Personal infor like D.O.B, gender,

    Dependants
    - Name, relationship, DOB, living away from home (Y/N) general notes

    For services - still waitng on forms conplete but had a quick go on:
    - Policy number
    - company
    - review date

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    There are data models at this site that may be helpful. May not directly suit your needs, but some combination may shed some light on required structures.

    http://www.databaseanswers.org/data_models/index.htm

    Excellent tutorials at

    Crystal's http://www.allenbrowne.com/casu-22.html

    DataPig http://www.datapigtechnologies.com/AccessMain.htm

    Martin Green http://www.fontstuff.com/siteindex.htm#access

    Rogers Library http://www.rogersaccesslibrary.com/forum/topic238.html

  5. #5
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    The three client related tables have similar structures and all contain information about people, so I would recommend 1 table to hold all information about people and a table hold how the various people are related to each other.

    tblPeople
    -pkPeopleID primary key, autonumber
    -txtFName
    -txtLName
    -txtAddress
    -txtCity
    -txtState
    -dteDOB (date of birth)
    -gender

    tblRelatedPeople
    -pkRelatedPeopleID primary key, autonumber
    -fkPPeopleID foreign key to tblPeople
    -fkSPeopleID foreign key to tblPeople
    -fkRelTypeID foreign key to tblRelationshipType (describes the relationship that the person identified by fkSPeopleID has with the person identified by fkPPeopleID)


    tblRelationshipType (describes various familial relations i.e. son, daughter, spouse, cousin etc)
    -pkRelTypeID primary key, autonumber
    -txtRelationshipType


    Now since a person can have many ways to contact them (business phone, personal phone, cell phone, e-mail addresses etc.), that describes a one to many relationship which would best be handled with a table structure similar to the following

    tblPeopleContactInfo
    -pkPeopleContactInfoID primary key, autonumber
    -fkPeopleID foreign key to tblPeople
    -fkContactTypeID foreign key to tblContactTypes
    -txtContact (the actual phone #, e-mail address etc.)
    -logPreferred (identified the preferred contact method for the person)

    tblContactTypes (will just hold a list of contact types: business phone, home phone, home e-mail address)
    -pkContactTypeID primary key, autonumber
    -txtContactTypeName

    I would also have a table that holds the basic service type (pension, mortgage, insurance etc.)

    tblServices
    -pkServiceID primary key, autonumber
    -txtServiceName

    Now relate the service or services to the person with the particular details

    tblPeopleServices
    -pkPeopleServiceID primary key, autonumber
    -fkPeopleID foreign key to tblPeople
    -fkServiceID foreign key to tblServices
    -Policynumber
    -company
    -reviewdate

  6. #6
    Darkglasses is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    9
    Thanks orange, I am referring to these sites as I go.

    jzwp11, I appreciate the structure and and get what your saying . . . I was a long way of that. I am going to "pen and paper" this so I can see the relationships before I impliment a build because I can't picture it.

    Just one question, What are these forieng keys as I don't see them elsewhere:
    -fkPPeopleID foreign key to tblPeople
    -fkSPeopleID foreign key to tblPeople

    At the moment I am using the "contacts" template and editing it for the nice inut forms and was going to use the cases tables to input services. It all seems easier this time and maybe all those tutorials have sunk in. Anyway going to build in your structureOnce I get the contact from done.

  7. #7
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    Since two (or more) people can be related, the fkPPeopleID and fkSPeopleID both refer to pkPeopleID but just different people. You cannot have duplicate field names in the same table, so I had to distinguish them somehow hence the addition of P in one and S in the other. I usually use the "P" to denote primary and "S" secondary, that just helps me distinguish them; you can use whatever fieldnames you want but both must refer back to the pkPeopleID of tblPeople. If a person is related to more than one person, there would be a correpsonding records in tblRelatedPeople that defines those related people. For example, lets say that Harold has to 3 kids, Billy, Sami and Bobby. So all four people would be in tblPeople and you would have 3 records in tblRelatedPeople:

    Harry|Billy
    Harry|Sami
    Harry|Bobby

    Of course you would just have their corresponding ID values. So if the pkPeopleID for Harry is 1, Billy 2, Sammy 3 and Bobby 4, the tblRelatedpeople would look like this (just showing the 2 fields)

    fkPPeopleID|fkSPeopleID
    1|2
    1|3
    1|4

  8. #8
    Darkglasses is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    9
    That helps mate and thinking my weekend wil be fun

    I am currently adding tabs to the cusomter details form to add personal details and will make this the people form. It will have fields that are not applicable to kids but it's ready made editing is going well, just need to seperate out what fields are on what tabs.

    I apreciate the help and will post my build in case it helps anyone, or get"what the hell did you do?"

    Darkglasses

  9. #9
    Darkglasses is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    9
    Hey jzwp11,

    I penned out your structure and it all makes sense now seeing how the tables relate as I think learning how to use MS Access 2010 was a distraction ong get the fundimentials right. I am going to build tomorrow so there is time yet for tears

    I have added another table

    tblFinancialinformation
    -PKfinancialinfo
    -FKPeopleID foreign key to tblPeople
    -txtOccupation and income
    -txtAssets
    -txtinheritence
    *these are general descriptions for info to be entered. Form made with tabs.

    Does that sound ok?

    In looking forward when I make a query on a particular person then it will bring up their financial state and current policies, assets, etc.
    WHat I am unsure if is the data entry - is it best to build a form and draw field from a lot of different tables or make one form with multiple tabs?

    I could just design one form with the different tabsbut I was going to make my queries read only, wait the review date and notes will have to added. Mhmm.

    I think I will sleep on it and have a think for the morning. If i make a contact entry form then the user closes it and open another form and enter the popel ID?

    Darkglasses

  10. #10
    Darkglasses is offline Novice
    Windows Vista Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    9
    Right gonna try a split form or run a seach in the header to on People ID

    Then at the bottom I can enter the financial information

  11. #11
    jzwp11 is offline VIP
    Windows 7 64bit Access 2010 64bit
    Join Date
    Jun 2010
    Location
    Dayton, OH
    Posts
    2,901
    tblFinancialinformation
    -PKfinancialinfo
    -FKPeopleID foreign key to tblPeople
    -txtOccupation and income
    -txtAssets
    -txtinheritence
    *these are general descriptions for info to be entered. Form made with tabs.
    Actually, occupation, income, assets etc instead of being fields should be records in a table and then relate them to the person with the specific detail

    tblFinancialInfoItems
    -pkFinancialInfoItemID primary key, autonumumber
    -txtFinancialInfoItemName


    Assuming that since these items are financial in nature, you want to capture a particular monetary value for each

    tblPeopleFinancials
    -pkPeopleFinancialID primary key autonumber
    -fkPeopleID foreign key to tblPeople
    -fkFinancialInfoItemID foreign key to tblFinancialInfoItems
    -currAmout

    If i make a contact entry form then the user closes it and open another form and enter the popel ID
    I'm not sure what you are trying to say, but if you bind a form to a table and you assign the autonumber datatype to the pkPeopleID, Access will automatically assign a number to the pkPeopleID field when you start entering data in the form.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Stuck with combo boxes for ever !!! :(
    By Evgeny in forum Forms
    Replies: 9
    Last Post: 04-14-2010, 09:03 PM
  2. Stuck on Query
    By wes028 in forum Access
    Replies: 9
    Last Post: 01-14-2010, 08:33 AM
  3. Stuck on my Query by Form
    By Silver Rain 007 in forum Queries
    Replies: 0
    Last Post: 10-26-2009, 12:20 PM
  4. Newbie Here & Stuck
    By FOZILD in forum Access
    Replies: 5
    Last Post: 09-24-2009, 08:26 AM
  5. Newb stuck on Normalization
    By dave_wilford in forum Database Design
    Replies: 6
    Last Post: 05-19-2008, 02:39 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums