Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2007

    Unanswered: Creating a match criteria

    Firstly I would like to know if the following is possible.

    The data is stored in two tables in MS Access.

    First table is called ‘OPBASE_tbl’ which contains a list of patients that have an attendance in the hospital. The date of the attendance is identified by the ‘Date Of Session’ field. Every patient has a unique ID ‘UNITNO’,

    The second table is called ‘OUTPATIENTS_tbl’, which also contains UNITNO (unique identifier) and the appointment date.

    What I want is if a record in ‘OPBASE_tbl’ (G110007/2)

    If a patient (G110007/2) has 3 records on ‘OPBASE_tbl’ - 09/04/2008, 30/04/2008, 11/06/2008

    UNITNO Date Of Session EP_TYPE Matched
    G110007/2 09/04/2008 N
    G110007/2 30/04/2008 F
    G110007/2 11/06/2008 F

    And 1 records in ‘OUTPATIENTS_tbl’ 19/04/2008,

    UnitNo Clinic Codes AppointmentDate
    G110007/2 826 19/04/2008

    Then I want to place a tick on the record for 09/04/2008. the methodology I am using is, if the appointment occurs between the two date of session on the OPBASE_tbl, place a tick on the first date, (between the two dates)

    If the Appointment Date was the 01/05/2008, then a tick would be placed on the second field (30/04/2008)

    I’m just thinking how this would be done in Access? I usually use queries but can’t think how this can be done. Do I need to write a module of some sort?

    Attached is a sample dataset

    Thanks in advance
    Attached Files Attached Files

  2. #2
    Join Date
    Oct 2007
    anyone please, need you thoughts and advice

  3. #3
    Join Date
    Nov 2007
    Adelaide, South Australia
    Well what you want is very confusing... one of the main reasons for that is that there is no meaning behind it. Another reason is the definition of what you actually want.

    You kinda said you want this:

    If A is If X = 3 and Y = 1 then tick X[1].

    All I get is "syntax error" ^^

    And without being a mega pro at SQL, I'd say you would need a VBA script to go through the data and tick the appropriate field for the appropriate data.
    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

Posting Permissions

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