Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Jan 2008
    Posts
    114

    Red face Unanswered: Beginner with relationship woes....

    Hello.
    I've been looking around these forums for a couple of days now. Some great stuff here. And very knowledgeable people too.

    I am confused (and it doesn't take a lot these days), with the most simplest things.... Relationships.

    I have the following tables
    tblClients
    tblMedications
    tblSubstances
    tblStaff
    tblGuardians
    tblIncidents
    + many more..... (I work in a rehab facility)

    I don't know whether tables like tblMedications and tblSubstances should be just a lookup table (this seems the easiest option to me).
    The values stored in these fields will rarely change, and may be added to occasionally.
    On the other hand I'm reading about many-to-many relationships that require a join table in between.

    Heres what I am trying to do. (Using Access 2007)

    My client takes medications, has taken substances, has guardians, has a primary staff member assigned, has incidents, has medical info, has psychiatric info, has educational info...... And on and on....
    What would be the best way to accomplish the relationships between tables?

    The data base (1 table) we currently have has almost 200 fields, and holds everything from client info, to finance info, to psychiatric info, medical, educational...... you get the picture?
    I have managed to split this 1 table up into about 15ish tables, but how to join them. At least with them all in one table.. there was no need for joins...
    But it's not practical I know.. So what would you suggest?

    Any help would be much appreciated...

    Tracy

  2. #2
    Join Date
    Aug 2006
    Posts
    559
    Tracy,

    You need to figure out what fields in the tables are going to be related. What you want to do is say in the table "clients", make a Primary key of "ClientID" as an autonumber which will be a designated number for the client with other fields such as "First Name, Middle Name, Last Name, Birthday, etc". The Primary key (ClientID) would then be a foreign key in tables such as "medications", "staff", "guardians", "incidents", etc. This field designates that all of the information that concerns the individual with that number would then be pulled up using this.

    I've found it easier to draw it out on paper before getting into Access to do it because it'll allow you to work everything out and 'see' it before you see it. You know?

    If you need more help, post in a zip file what you have but make sure you also downgrade to a Access2003 format for some, and we'll take a look at it.

  3. #3
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    In regards to laying out relationships...

    If you have Visio, it's a great tool for laying out table relationships as well as code flow.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  4. #4
    Join Date
    Jan 2008
    Posts
    114
    Thanks Grafixx01

    That makes sense.
    I have made a quick mockup of a couple of the tables. (I was going to make a zip after converting it to 2003, but it wont convert because of the multi combo boxes seen in the tblClient)

    Hers a pic of the relationships.... Not sure if this is any use, but if someone could point me in the right direction

    Click image for larger version. 

Name:	2008-01-19_130919.jpg 
Views:	60 
Size:	94.8 KB 
ID:	8041

    Each client has a primary counselor (staff member) What would be the relationship between tblClient and tblStaff?
    Last edited by Gwyar; 01-19-08 at 14:30.

  5. #5
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by pkstormy
    In regards to laying out relationships...

    If you have Visio, it's a great tool for laying out table relationships as well as code flow.
    I don't have Visio, though I have a pen and paper.
    But good idea, I remember using it a few years back...

    Thanks
    Tracy
    Last edited by Gwyar; 01-19-08 at 14:58.

  6. #6
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    Tracy

    1) On your piece of paper write down every type of data that you can possibly think of and then think of some more. Actually, I do better making a table and making fields. But that is just how I see things. Do not worry about what should go in different tables at this stage. Remember that every field, whether in a main table or other tables is either a direct or indirect description of the main entity......in your case, the patient.

    2) Look at each type of data and see how many fields it can break into. In general you should not have fields where the data is multiple words. The common example being address entered in one field such as 45 Smith Street Hurstville. It is very simple to join data from different fields but it can be real fiddly trying to break up entries in a field across to several fields.

    3) You appear to have done this.....look for all data types that will come as multiple instances and especially where the number of instances is unknown. Medications being an obvious one.

    4) When you decide what groups of data will go into different tables then think hard about what should be the main entity or driver for the table. Medications might be an example.

    In reality medications form part of the description of a health issue, in other words they are one form of treatment. You can have a health issue and no medication but you can't have medication and no health problem. You could have two health issues and the same medication. Valium for anxiety and back trouble.

    With medication you might have a field for a numeric entry for dose and a field for the measure.....50.....mg or 50...mls. If in doubt add a field and break up the data. Starting date of medication, date dose changed, date medication ended etc.

    You can make a highly workable data base by simply having a form open (based on a secondary Many table) and a macro or code inserts the patient ID into the new record. Such forms can then be opened on matching ID number basis.

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Gwyar
    I don't have Visio, though I have a pen and paper.


    I always start with a pen and paper before even touching a PC. Foundations ar the most important part; you can't just rush in with a cement mixer and hope it'll all be ok!
    George
    Home | Blog

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Pen / Paper is sooooo OLD SCHOOL

    I personally find it easier / quicker to use a PC to design with. I HATE having to re-draw out designs because I've run out of room on some bit of wood-pulp!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  9. #9
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by georgev


    I always start with a pen and paper before even touching a PC. Foundations ar the most important part; you can't just rush in with a cement mixer and hope it'll all be ok!
    Heh.. no kidding..... The more stuff I write down, the more stuff i find to write down... blimey this is turning out to be a massive adventure. Or am I just complicating things... well time will tell...

    Need more ink....

    Tracy

  10. #10
    Join Date
    Jan 2008
    Posts
    114
    This is what I have to work with. I have deleted all the records (i hope)
    What would you suggest? (apart from RUN!)


    Tracy

    TestDB.zip

  11. #11
    Join Date
    Jan 2008
    Posts
    114
    Quote Originally Posted by StarTrekker
    Pen / Paper is sooooo OLD SCHOOL

    I personally find it easier / quicker to use a PC to design with. I HATE having to re-draw out designs because I've run out of room on some bit of wood-pulp!
    I would agree with you, but I am the definition of Old School

    Tracy

  12. #12
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    A couple of things I would consider.

    I don't like field names that end with #

    I would also consider using much shorter field names and for two reasons. Firstly, the query grid is limited to 255 characters as are macro conditions. If you have complex searches then the long field names will burn up a lot of characters and especially where the query gets its criteria from a field, unbound text box or combo on an open form. Also, very long field names just lend themselves to making incorrect entries, whether in code, queries or macros.

    The fields you have in your Client table that cover various medical practitioners will mean you are limited to only one doctor/dentist in each grouping. Of course if there will only ever be one doctor involved and you only want to display the current doctor then that would be OK.

    Some other fields such as Allergies are only allowing for a description in one field. Since they are not Yes/No fields then it suggests you want more information in such a field than just a Yes or No for Allergies.

    Some groups of data in your Client table would appear to be served by having them in a different table. For example, you have 6 or 7 fields for different medication and the field [Other]. In my opinion, whenever a field is for "other" there is a problem.

    You have a lot of queries which are using dates as the criteria. If these dates are not something that is "locked in" you might consider having such criteria supplied by a form. That allows the criteria to be changed withou going to Query Design.

    There are lots of queries but only two forms and if I remember correctly your main form is based on a table. A data base when up and running should only require forms to be used. Forms just don't serve as a display and data entry. The form allows for attachment to labels, buttons, fields and textboxes of code and macros as well as opening other forms for related records.

  13. #13
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would agree with you, but I am the definition of Old School
    Hahahahaaaaaaaaa!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by Gwyar
    I would agree with you, but I am the definition of Old School

    Tracy
    Well I think I fall into the "new skool" category; but who's counting
    George
    Home | Blog

  15. #15
    Join Date
    Apr 2004
    Location
    Sydney Australia
    Posts
    369
    George,

    You and StarTrekker make me feel like I was wasting my time responding to the thread starter.

    I even wonder if thread starter cares.

    Did I miss something here?????????????

    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
  •