Page 1 of 4 123 ... LastLast
Results 1 to 15 of 47
  1. #1
    Join Date
    Jan 2008
    Posts
    114

    Unanswered: To lookup or not?

    I have been so confused by this whole thing for the past few weeks. I have deleted and restarted a db more times than I care to remember. But.
    I think I might finally have it....
    I've read a lot of comments about not using lookup function in Access as it can cause problems down the road.

    Are you saying do not use a lookup column in a main table? Instead use a combo box on a form? Which is bound to the field and table originally intended.?

    Im sure there was an easier way of putting this, but..

    Am I at least getting close?

    Thanks

    Tracy (slightly less confused)

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Yes.

    You should have two tables. Create a relationship between the two. Then provide the dropdown on the form as you describe. The relationship provides relational integrity, the combo box helps the user select the right entry.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Also, this is not called the "lookup function". I mention this as there is a "DLookup function" which might confuzzlate people
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Jan 2008
    Posts
    114
    heh.. told you I was confuzzlated

    This is really basic stuff I know. but I'm glad I am on the right track. (Not the same page yet, but at least in the same library )

    Re relationships...
    If I have 2 tables

    tblCLIENTS tblGUARDIANS
    ClientID GuardianID
    FirstName ClientID
    LastName FirstName
    Address.... LastName
    etc
    The Primary Key in tblClients is a foreign key in tblGuardians
    So is that the relationship right there.

    Or do I need to go into database tools, relationships, and drag ClientID from tblClients to tblGuardians (so there is a line between them.)
    I think this is the part that is confusing me.

    Thanks for your time pootle

    Tracy

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by Gwyar
    The Primary Key in tblClients is a foreign key in tblGuardians
    So is that the relationship right there.
    Correct - that is your logical design.

    Quote Originally Posted by Gwyar
    Or do I need to go into database tools, relationships, and drag ClientID from tblClients to tblGuardians (so there is a line between them.)
    Correct - that is the physical implementation of the logical design.

    You design the relationships (logical) and implement them (physical).
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2008
    Posts
    114
    Aha!!
    Well that makes much more sense to me now...
    What if you have 20 tables.. They all won't have physical relationship to your main tblClients..will they? Though they all 'may' have a logical relationship?

    I don't know why I am finding this so difficult... Maybe I'm just too old for this.. heh

    Thanks again pootle

    Tracy

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Never too old Tracy
    http://www.tonymarston.net/php-mysql...se-design.html

    Check this out - it will really help and well worth the effort
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Jan 2008
    Posts
    114
    Thats a great link. Thanks for sharing.

    I have attached a pic of the current layout of relationships, Am I heading in the right direction or??

    Thanks for your input. It is much appreciated.

    Tracy
    Attached Thumbnails Attached Thumbnails SecondTry.jpg  

  9. #9
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    My suggestions (presuming there is no data issues to worry about) and bearing in mind that I don't know your application needs like you would, but from looking at that ERD, this is what I would recommend:

    Backup FIRST!

    * Change Guardian.GuardianType to RelationshipID.
    * Delete Relationship.GuardianID
    * Delete Substance.ClientID
    * Delete Staff.ClientID
    * Move Substance.FrequencyOfUse to the ClientSubstance table -- IF this is to do with how often the client takes the substance and not how often the substance should be taken.
    *The Clients.MedicationID, MedicationDosage and MedicationTime fields look like they should be in a Medication table... or amalgamated with the Substance table. Hard to say without clear goals.
    * The Clients.Substance and FrequencyOfUse look out of place too, possibly they should just be deleted.

    As for Client and Guardian, well it depends on the nature of the relationship.

    Can one client have many guardians (that you want to record)?
    Can one guardian oversee many clients (that you want to record)?

    If you answer both of those a YES, then you're going to need a ClientGuardians table to form the many-to-many relationship.
    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

  10. #10
    Join Date
    Jan 2008
    Posts
    114
    The more I read your suggestions, the more clearer it becomes. Thank you so much.
    I have revised my table structure, and its looking much... nicer? (for want of a better word)

    I have added a couple of fields to ClientSubstance.
    Primary
    Secondary
    Tertiary.

    These are the drug of choice for the client based on their personal preference.
    They may use alcohol as their primary substance, and maybe MaryJ only sometimes which would be secondary... etc...
    I need to track the frequency of use (daily, twice a week, weekly etc..) for each of the fields. Primary, secondary and tertiary.
    Would I then need 3 additional fields, primaryfrequency, secondaryfrequency and tertiaryfrequency.
    I would have thought Substance:FrequencyofUse would have done it, but it's not. Or I did something wrong, which is far more likely

    So once again, thank you

    Tracy
    Attached Thumbnails Attached Thumbnails second.jpg  

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Reread about first normal form. If it makes no sense with regard to your last post please come back.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You're welcome btw

    Pootle is right, following the rules of first, second, third and fourth normal forms will be a great thing to study to get this all under control... but I find that a little experience is needed as well.

    I also like to help those who appreciate it, so, here's my second round of suggestions. Always remember to backup first!

    * ClientSubstance.ClientSubstanceID is not needed.
    * ClientSubstance.ClientID and ClientSubstance.SubstanceID are THE primary key (composite key). Select BOTH fields and then hit the key button.
    * Re-create the ClientSubstance<-->Client relationship so that ClientID is the linked field for both tables.
    * Rename ClientSubstance.PrimarySubstance to PreferenceLevel (which will contain primary, secondary or tertiary).
    * Delete ClientSubstance.SecondarySubstance.
    * Delete ClientSubstance.TertiarySubstance.
    * Move Substance.FrequencyOfUse into the ClientSubstance table.
    * Delete Clients.CurrentAge (this is always a calculation based on DOB, not a field).
    * Delete Clients.Substance.

    Don't know what ChartQA is, so not sure what to do with that one.

    Cheers

    ST
    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

  13. #13
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I don't recall ever knowingly getting to fourth base but I have been known to participate in a little BCNF. Third normal form is typically the point at which I am a happy flump.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  14. #14
    Join Date
    Jan 2008
    Posts
    114
    All suggestions taken on board and implemented

    If both ClientSubstance.ClientID and ClientSubstance.SubstanceID are THE primary key in the ClientSubstance table, shouldnt it be the same in the Guardian table? with both GuardianID and CLientID being the Primary key?

    Re: Client.CurrentAge If there is no field then it can't be stored anywhere? So it doesn't need to be stored? Which is neither here nor there really, just trying to understand.

    Re: Client.ChartQA (Quality Assurance)
    Every few weeks I have to go through each clients paper chart and figure out what is missing, signatures, vital info, documentation etc. This list or report is then emailed to that particular client's primary counselor for review.
    I'm betting I need another table for that info.
    I will work on this..

    You are my favourite StarTrekker and Pootle Flumps...

    Thank you both so much

    Tracy
    Attached Thumbnails Attached Thumbnails second1.jpg  
    Last edited by Gwyar; 04-08-08 at 06:37.

  15. #15
    Join Date
    Jan 2008
    Posts
    114
    Hohum...
    Back to being confused again...
    Regarding the substances a client uses.
    Substance.SubstanceType is pretty static. (alcohol, cocaine, MaryJ, OTC, Prescription etc...)
    As is the Frequency of use (1-3 times a week, 4-6 times a week, daily, 3 times a month, 1 a month.... etc)
    I was hoping to use these 2 fields as a picklist using a combo box.

    As it is I am unable to store this info anywhere. I can pick from Substance.SubstanceType, But I'm stuck from there. If I add a field to ClientSubstance called SubstanceType, could it be stored there? Or am I off on the wrong track again....
    Ack..

    TraCY

Posting Permissions

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