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.
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.