Link two tables with variable dates

[Dissander]

Dear Team,
I am new to access. could I have some help please? I have a table with a list of surgeries performed in a year and another with a list of blood transfusion received. As transfusion tend to happen after surgery, this means transfusion date will differ from surgery date. When I link the 2 tables by the hospital number of patients, the results come back with all transfusions patients had, even well before, say, 1 year before surgery. Is there a way I could link up these 2 tables by dates +/- few days only please? Thank you. Any help would be very much appreciated.
Thank you.

 

[sonic8]

You can use this criteria expression to filter the records down to transfusions that happen on the surgery date or 10 days after.

... WHERE TransfusionDate BETWEEN [SurgeryDate] AND DateAdd('d',10,[SurgeryDate])

If you want to include records where the transfusion happened before the surgery, you can use a DateAdd with a negative value for the number instead of the unchanged surgeryDate.

 

[Dissander]

Dear Sonic8, many thanks for your quick reply. I really appreciate your help. I will give it a try now. Thank you.

 

[Dissander]

I am very sorry. I am getting the error message - "Data type mismatch in criteria expression."
I don't know where I am going wrong here. Thank you.

 

[sonic8]

The columns TransfusionDate and SurgeryDate are both of data type Date in your table, aren't they?
If not, change them to Date. Otherwise will you experience all sorts of problems with your database.

 

[Dissander]

Many thanks for your reply Sonic8. They are indeed typed as date. The transfusion table is a database I have put together using UNION. I have transfusion tables for 2015-2016 (monthly as well) linked in one by using UNION. The I have linked this 'database 15-16' in the surgery table. However, I have noticed that the 'database 15-16' union linked table has 2 extra 00 in the date of transfusion. I don't know how to remove them. Could this be the cause the error?
Thank you.

 

[sonic8]

However, I have noticed that the 'database 15-16' union linked table has 2 extra 00 in the date of transfusion. I don't know how to remove them. Could this be the cause the error?

Yes, that is most likely the cause of the error because the resulting value with the added 00 is not a date any more.

Double check your UNION-Statement for any errors.

In general, it is not the best database design to use multiple tables partitioned by date for the same type of data (your transfusion tables). If this is external data imported into the db, consider changing the process to import this data into one single transfusion table containing all the data.

 

[Dissander]

If this is external data imported into the db, consider changing the process to import this data into one single transfusion table containing all the data.[/QUOTE]
Thank you, Sonic8. I import my monthly data from Excel To individual Access table then link them together with UNION. If it is not the best way to combine the data, what do you suggest I do please? I am planning to do an audit on how many patients required blood transfusion post a specific surgery.
In excel the date column is typed as custom (e.g. 06/12/2016 22:13). Then I copy & paste the whole selected sheet in Access.
Thank you for taking time helping me.

 

[sonic8]

In excel the date column is typed as custom (e.g. 06/12/2016 22:13). Then I copy & paste the whole selected sheet in Access.

I suggest you store all transfusion data in just one table. This will have much better performance than the Union Query.

You can copy, rename the oldest of your transfusion tables to not include any date/month info in the name. Then delete all data from that copied table.

Then run an Append Query to insert all the data from the Union Query into that table.

Whenever you get new data, you can still copy&paste this into a new Access table, but run an append query afterwards to append the data into your master transfusion table.

But you should look into the issue with the added 00 to the dates in the existing Union Query before performing the above steps. Once you got that sorted, your query should work right away without restructuring your database.

 

[Dissander]

Many thanks for your smart suggestions. I will try this now. Wouldn't I run into the limitation of ACCESS, that is, restriction to 2GB per table? When i use UNION, I link data from tables 2014-2017 and I was planning to add more.

 

[sonic8]

Wouldn't I run into the limitation of ACCESS, that is, restriction to 2GB per table?

I wasn't aware that the amount of data is so huge for this to be a problem.
Yes, you might run into this limitation. Actually, it is only 1GB per table but 2GB for the whole database.

Have you checked how big the current database is with the existing tables? Is it really realistic to hit that size limit anytime soon?

You might be better of using another backend database system, like MS SQL Server, if you need to analyze such a huge volume of data.

 

[Dissander]

Hello Sonic8. Apology, my mistake. I have put all transfusion data from 2014-2017 in one table and the whole data comes up to 303mb! (not 2Gb). I have 2 tables. For transfusion I have named it tbl_all and solus for surgery. In tbl_all i have fields: episodedate, bloodprod, hospital number, price and in the solus table I have fields: Date/Time of Procedure *, hospital number, patient names etc. could you help me with the criteria expression please? Thank you. Much appreciated.

 

[sonic8]

In tbl_all i have fields: episodedate, bloodprod, hospital number, price and in the solus table I have fields: Date/Time of Procedure *, hospital number, patient names etc. could you help me with the criteria expression please?

First you need to join both tables by the PatientId (or whatever its exact name is) column to establish the connection between both tables. Then you add my criteria expression to limit the transfusions to the time frame of the procedure.
Here adjusted for your actual column names.

... WHERE episodedate BETWEEN [Date/Time of Procedure *] AND DateAdd('d',10,[Date/Time of Procedure *])

 

[MarkK]

Also note that you can do multi-field comparisons and use inequalities in the join expressions...

...
FROM tSurgery As ts INNER JOIN tTransfusion As tt 
   ON ts.HospitalID = tt.HospitalID
   AND ts.PatientID = tt.PatientID
   AND [COLOR="Purple"]ts.SurgeryDate <= tt.TransfusionDate + 10[/COLOR]
...

...in addition to using WHERE clause constraints to return exactly the rows you are looking for.
hth

 

[Dissander]

Dear Sonic8 and Markk. Thank you. These were exactly what I was looking for and they work. My Team was impressed when I showed them, but the credits go to both of you. Thank you so very much. On that note, similarly, I am on my way to help others in my own field. I am very grateful.

 

[MarkK]

Thanks for posting back with your success Dissander!