Connecting Date Ranges to an Incident Date

[PatrickKVC]

I am working on a report for a hospital. I am using two tables to show reportable incidents and the hospital unit they occured on. One table has the data showing the date range a patient is on a particular Hospital Unit. The other is a table for the reportable incidents. A patient can have multiple Unit records and multiple Incident records.

I am having trouble showing the patient connected to the correct unit the incident occured on. I am trying to write a formula to say if the incident ocured in the range range of one of the unit records for the patient select the corresponding unit from the Unit record to show on the report.

Any ideas for making this work?

Thanks!

 

[Charliy]

Is this Avatar? If it is there are other ways to get to the unit on a date.

 

[PatrickKVC]

Yes I am working in avatar. What table is best to grab the Unit information from?

 

[Charliy]

biling_room_board_worklist

 

[PatrickKVC]

Thanks so much! I am still figuring out the table structures and that got me exactly the information I needed.