Results 1 to 7 of 7
  1. #1
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Cycle Through Records (A2K)

    Good Morning,

    I have 2 tables each with it's own form;
    1--tbl_Welcome (rst for frm_Welcome Form and houses ALL data)
    2--tbl_OutreachResults (rst for sfm_OutreachResults and linked to tbl_Welcome and houses the outreach results from members)
    1. <LI>dDateOfAttempt (Date)
      <LI>nMemberID (Numeric)
      <LI>nCallComplete (Yes/No)
    PURPOSE:
    Outreach to NEW members to determine if the member(s) need Case Mgmt.

    OUTLINE:
    The state requires us to make 3 Outreach attempts to each new member. Each week our corporate office provides us with a new member list. Our users cycle through the records and input to the results of the outreach. 16 options are available to the user in a dropdown and depending on the selection made, the outreach can be closed or continued until the 3rd attempt.

    PROBLEM:
    If the call to the member is made on 3/19/2008, that record needs to me omitted from the recordset until the next day. This way if the outreach is continued it will reappear in the list tomorrow. This will prevent the users from contacting the same member in the same day.

    QUESTION:
    What is the best approach to handling the recordset for this situation. I've tried is to join both tbl_Welcome and tbl_OutreachResults by the nMember ID through a variety of joins WHERE nCallComplete = 0 and dDateOfAttempt =<>Date(). However, I haven't had any luck.

    tbl_Welcome has + 28k records and tbl_OutreachResults has + 35k records. The join is on nMemberID and When the query runs it should return:

    All records from tbl_Welcome that ARENíT in tbl_Outreach and ALL records in tbl_Outreach that ISNíT closed and the dDateOfAttempt is NOT today.

    Can someone provide some insight?

    Thanks in advance.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cycle Through Records (A2K)

    I'd first create a query that selects all nMemberIDs that are marked as completed in tbl_OutreachResults:

    SELECT nMemberID
    FROM tbl_OutreachResults
    WHERE nCallComplete=True

    Save as qryComplete.

    Next, create a query based in the two tables and on qryComplete.
    Join tbl_Welcome to tbl_OutreachResults on nMemberID, double click the join line and select the option to include ALL records from tbl_Welcome.
    Do the same for tbl_Welcome and qryComplete.
    Add the fields that you need from tbl_Welcome to the query grid, or *.
    Add some fields from the other table and from the query, and set the criteria as in the screenshot below.

    I'll attach a sample database to the next reply.
    Attached Images Attached Images
    • File Type: png x.png (3.0 KB, 0 views)

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cycle Through Records (A2K)

    Sample database attached.
    Note: with the number of records you have, the query will no doubt be slow.
    Attached Files Attached Files

  4. #4
    5 Star Lounger
    Join Date
    Feb 2001
    Location
    Louisville, Kentucky, USA
    Posts
    605
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Cycle Through Records (A2K)

    BRILLIANT, just BRILLIANT....thank you very much Hans, it's greatly appreciated.
    Roberta Price <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>

  5. #5
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cycle Through Records (A2K)

    I see if the criteria is entered any other way the query results in no records. Would you explain the logic please?
    Thanks
    chuck

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Cycle Through Records (A2K)

    If you enter two conditions A and B in the same row, the query returns all records that satisfy both A and B.
    If you enter two conditions A and B in different rows, the query returns all records that satisfy either A or B, or both conditions.
    Does that help?

  7. #7
    3 Star Lounger
    Join Date
    Mar 2006
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    289
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Cycle Through Records (A2K)

    Yes, that helps indeed.
    Thanks
    chuck

Posting Permissions

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