Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2011
    Posts
    26

    Unanswered: Linking variable dates

    Hi everyone, I've been lurking here for the past few weeks and have been picking up some really great tips.

    I'm building my first database and have come across a problem that can't be answered in any of the books I have. I have a clinic database in which I have to relate two tables, clinic visits and lab results. Within clinic visits I have their medical records number, the date of their visit, and various measure taken during the patient's visit. Within lab results I have the medical records number, date at which the lab tests were run, and the lab results. I initially thought to link them by medical records number and clinic date so that under each clinic visit, I could view the lab results specific for that clinic visit date. The problem that I ran into is that the labs are sometimes done a day or two after the clinic visit. I know I could manually change the dates to be equal, but I am given the lab info at the end of the month after hundreds of lab tests have been run in an excel format. How can I link the data to the appropriate medical records number and date while allowing the lab date to vary by a few days?

    I'm sorry if they way I described my problem is confusing and if the terminology isn't correct, I'm still in the early stages of learning. If you have any questions of clarity I would be glad to describe my problem more. Thanks in advance.

  2. #2
    Join Date
    May 2010
    Posts
    601
    Welcome!

    Does the lab data contain the related visit date that?
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

  3. #3
    Join Date
    Jun 2011
    Posts
    26
    It does not and it is that specifically which is my problem. Here's an example of what I have:

    Clinic data:
    patient number clinic date weight
    1 4/4/11 123
    2 4/4/11 153
    3 4/4/11 136
    4 4/5/11 145
    5 4/5/11 156
    1 6/10/11 124
    2 6/10/11 159
    3 6/11/11 146

    Lab data:
    patient number lab date lab result
    1 4/4/11 8
    2 4/5/11 9
    3 4/5/11 6
    4 4/6/11 4
    5 4/5/11 5
    1 6/12/11 8
    2 6/12/11 8
    3 6/13/11 7

    As you can see the lab date is either on the same date as the clinic date or within a few days. If the clinic and lab date were the same I would be fine as I could make a relationship between those two variables, but the dates differ a little. The patient numbers can be linked fine, but the dates are my obvious problem. Is there any way to state in the relationship to allow some flexibility with the dates? Like if the lab date is within 5 days of the clinic visit then it would be ok to link them. I'm thinking no as I haven't come across anything even remotely similar, but I figured I would ask the board as I'm still new to access. Thanks for your help.

  4. #4
    Join Date
    May 2010
    Posts
    601
    I would still link/related the records by the primary key not the date. I would only use the date as a way to determine which records are related.

    I would write some VBA code to look at the lab data a figure out how to match them up.
    Boyd Trimmell aka HiTechCoach HiTechCoach.com (free access stuff)
    Microsoft MVP - Access Expert
    BPM/Accounting Systems/Inventory Control/CRM
    Programming: Nine different ways to do it right, a thousand ways to do it wrong.
    Binary--it's as easy as 1-10-11

Posting Permissions

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