Merge two tables using an identifier and a field that is between two other fields

guamlet

Board Regular
Joined
Dec 29, 2002
Messages
145
I have some patient records in a massive database for surgery. Some patients have had multiple surgeries on different dates. One table shows the patient's medical record number and their entry date and exit dates from the hospital along with other data that i want to capture on their stay (e.g. length of stay, number of central lines, etc). the other table shows the medical record number and their date of surgery along with surgical specific data (time in the operating room, name of operation, etc). I need a way to create a query where I can match the two tables using the medical record number and the requirement that the surgery date be between the admission and discharge dates. I'm thinking I need to do it in SQL instead of the normal query design GUI, but I'm not that familiar with SQL syntax to write it, so troubleshooting would be a nightmare (is it because my syntax is wrong, or is it because the logic is wrong, etc).

Any suggestions?
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
You would write an INNER JOIN in SQL, but that can also be done in design view by clicking on Query, Show Table, Add, then drag the field(s) to be matched from one table/query to that of the same name in the previous one.
 
Last edited:
Upvote 0
I don't think INNERJOIN would work because I'm not looking for an equijoin. only the medical record numbers would equal. The surgery date from one table needs to be between the admission date and discharge date from the other tables to create a match that I can then show the rest of the data from. I'm thinking it's more of a SELECT tbl1.SurgDate, tbl2.MRN, tbl2.AdmitDate, tbl2.DischDate, tbl1.SurgTIME, tbl2.LOS FROM tbl1, tbl2 WHERE ((tbl1.MRN=tbl2.MRN) AND (tbl1.SurgDate BETWEEN tbl2.AdmitDate AND tbl2.DischDate)). What I want is then a table from this that shows MRN, SurgDate, AdmitDate, DischDate, SurgTime, and LOS correctly lined up keeping in mind that MRNs would appear more than once in some occasions because a patient might come one year for a surgery and a different year for surgery again. When I build databases like this, I make an indexed identifier to help with the linking, but this is from a research group against a registry database so there's no indexed fields to link.
 
Upvote 0
Your sql looks okay to me, except (if I recall correctly) Access like parens around the Between parameters:

WHERE x BETWEEN (y and z)

So you would need one more set of parentheses for that.
 
Upvote 0

Forum statistics

Threads
1,213,538
Messages
6,114,218
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top