Results 1 to 12 of 12
  1. #1
    Join Date
    Jan 2008
    Posts
    114

    Unanswered: Duplicating records quandary...

    Hello..
    It's been some time since I posted here. Still the best resource out there..
    I'm having a bit of trouble with a report.

    I am pulling info from 3 tables, (CLIENT, MEDICATION, GUARDIAN)
    and it works great.. Apart from the fact that there are duplicate records. The only reason I can think of why this is happening, is the following:

    one Client has many guardians (usually 2)

    I have one checked as Legal guardian.

    What I want to happen is:

    Client info, together with Guardian info and med info. Using the Legal Guardian info

    I suspect because there are 2 guardians to some clients, it is duplicating each med for each guardian.
    If the client has only one guardian, then it works as it should..

    Does anyone have any idea how to get around this?

    Thank you very much for your time



    Tracy Scott
    Last edited by Gwyar; 11-25-09 at 07:53.

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

    The issue is how you want it to "get round this". What do you want to happen when there are two guardians?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jan 2008
    Posts
    114
    Hello Pootle Flump How are you?
    I guess I want the following info on the report.

    Name, gender, DOB, Primary Doc, Guardian (but only the one that is checked as Legal Guardian.

    As it is now.. If a client is taking prozac, it will be listed once per guardian.

    Any clearer?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK so is the problem that you are duplicating data, or that you are gettign duplicated results in your query. there is a none to subtle difference

    if its the latter then its down to how the query is formed.

    there are some dodgy tricks that you can pull if you know there will only ever be two guardians, such as pull the top guardian for #1 the bottom guardian for #2 (or no guardian if there is only one guardian allocated for that client. soem of this you coudl do in the form or report, and just not dispaly duplicate guardian details

    the other alternative is to rethink at what point in time do you get the guardian details... you could get the client and the meds, and then subsequently get the guardian details
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I'm good thanks Tracy. And how are you?

    So we want every client. If the client has a legal guardian we want to show that guardian. If the client does not have a legal guardian we do not want to show any guardian details for that client. In other words, if a guardian is not a legal guardian we do not want their data.
    Is this correct?

    Can a client have more than one than one legal guardian?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Jan 2008
    Posts
    114
    Hi again.
    Yes, the Client can have more than one legal guardian, but for my purposes I need one guardian as the emergency contact for the client, so I figured if I checked one of the guardians as the LegalGuardian, and made the query as yes to LegalGuardian then the reports would work, but alas.
    I thought about deleting the 2nd guardian altogether. That should fix the problem.. maybe.
    I could maybe copy the LegalGuardian to another table, but then that is duplicating the data.

    Could I get it to only choose the first guardian belonging to a client?

    Tracy

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    OK one solution is to nominate a person as the primary contract (set a value in the guardian table) you will then need to enforce the one primary guardian per persons rule.so you cannot have more than one guardian.

    if you don't need a primary contact, just 'any old guardian contact' will do, then consider setting the 'primary contact' value in a query runs say one a week or just pull the top guradian ID for this client.. you may have to do that using a subquery
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Jan 2008
    Posts
    114
    Thank you healdum
    (Love your sig)

    Quote Originally Posted by healdem View Post
    OK one solution is to nominate a person as the primary contract (set a value in the guardian table) you will then need to enforce the one primary guardian per persons rule.so you cannot have more than one guardian.
    Would this be a one to one relationship? I know they are not used often in Access...
    What value would I set in the guardian table?
    Even if I have set one of the guardians as LegalGuardian (which will do as my emergency contact), the Query works fine, providing I only use 2 tables.
    If I add the Medication table everything goes to pot.
    Actually I just tried it with 3 tables and the Query works.. sort of
    It shows like this:
    If a client is taking 2 meds, then the query is showing the clients name twice and the guardians names twice. (once for each med) That's not right is it. I need to do some normalizing>?

    Thanks for your help...

    Tracy

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by Gwyar View Post
    Thank you healdum
    If a client is taking 2 meds, then the query is showing the clients name twice and the guardians names twice. (once for each med) That's not right is it. I need to do some normalizing>?
    actually that is right, its the results I'd expect when you join tables you have asked for everyhtng from each of the tables that matches the join. if you have two medications then you will have two identical client details. if you have three guardians defined and two medications you will get 6 rows, the client data will be the same in all four, you'd get 2 medications details and 3 guardian details for each medication.

    where are you using the results of this query
    it may be that you can rectify this problem in the report or form. bear in mind you don't have to display everything you get fromt he query, judicious use of sorting and grouping, hide on duplicate can eliminate many of these sort of problems
    I'd rather be riding on the Tiger 800 or the Norton

  10. #10
    Join Date
    Jan 2008
    Posts
    114
    Hmmm...
    It's going straight to a report...
    I have searched for the duplicate thing but can't find it anywhere....
    Is it just a option you can check in the report or some code?

    I have tried the sorting and grouping too... to no avail....

    Tracy

  11. #11
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you need to be a bit smarter with the report definition
    set the sorting and grouping to be say client, medication
    have a group header for the client BUT not the medication
    in the group header stuff all the data relevant to the client + the guardian(s)
    in the report detail stuff all the data to each medication.
    if you want you could stuff the guardian details into the group footer
    I'd rather be riding on the Tiger 800 or the Norton

  12. #12
    Join Date
    Jan 2008
    Posts
    114
    Yay... All is working now. It took a bit of clicking and dragging but we got there in the end..
    Thank you V much healdem

    Tracy
    On to the next little problem...

Posting Permissions

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