Results 1 to 9 of 9
  1. #1
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Help (Access2000)

    <font color=red>red</font color=red> I am having trouble with a query I think should work but doesn't. Basically, what I'm trying to say in the query pasted below is that if the call type field is blank and the do not call field is blank, then show me those patients. I put a patient in with no calls to test it and he doesn't show up.

    SELECT T_CallData.RNA_ID, T_CallData.Call_ID, T_CallData.CallDate, T_Demographics.DoNotCall, T_CallData.CallType
    FROM T_Demographics INNER JOIN T_CallData ON T_Demographics.RNA_ID = T_CallData.RNA_ID
    WHERE (((T_CallData.CallType) Is Null))
    GROUP BY T_CallData.RNA_ID, T_CallData.Call_ID, T_CallData.CallDate, T_Demographics.DoNotCall, T_CallData.CallType
    HAVING (((T_Demographics.DoNotCall) Is Null));


    Thanks in advance,
    Laura

  2. #2
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Help (Access2000)

    If you are saying that there are records from T_Demographics but not from T_CallData, then you will need to change the INNER JOIN to a LEFT JOIN.

    Pat

  3. #3
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access2000)

    I tried your suggestion but it didn't work. T_Demographics only has the field that triggers me not to call a patient. I don't want them to show up on the Initial Call Due if there is a note in the DoNotCall field. I wonder if I move the field to T_Call Data if it would help?

  4. #4
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Help (Access2000)

    Don't move any fields yet.
    Do you mean you have a record in T_Demographics but no record in T_Call and you want the T_Demographics record to show?
    If you change it to a right join does it work (I may have my joins mixed)?

    Pat

  5. #5
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access2000)

    No, that didn't work either. But your statement that I have a record in T_Demographics but no record in T_Call and I want the T_Demographics record to show is correct. In T_Call Data I have the RNA_ID, Call_ID, CallType & CallDate. From T_Demographics, I am only pulling the DoNotCall field. Basically, I only need the DoNotCall field to prevent calling someone we have already talked to and they don't want to be called again.

  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Help (Access2000)

    Why have you have got a GROUP BY based upon T_Call variables, when there are none returned. Take that out and put a LEFT JOIN and it should work, I think.
    Pat

  7. #7
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access2000)

    For some reason, I get three blank records when I do that. I have 2 dummie records that I want to show up but even the RNA_ID field is blank in the three records that appear.

  8. #8
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Query Help (Access2000)

    And you should be showing blank records, because you show T_Demographics.DoNotCall which is null. What is the RNA_ID field ? Show more of the fields from the T_Demographics table and see what happens.
    Pat

  9. #9
    Star Lounger
    Join Date
    Dec 2002
    Location
    New Orleans, Louisiana, USA
    Posts
    95
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Help (Access2000)

    <img src=/S/bananas.gif border=0 alt=bananas width=33 height=35> Thanks! That worked. The RNA_ID is the unique identifier for all patients (like an account #). I added the RNA_ID from the T_Demographics table and it shows up. Thanks so much for your help and your patience, Patt.

    Laura

Posting Permissions

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