Hi, all. I need help due to complete brain freeze. I am trying to create an infirmary tracking system within an overall Health Services Reporting database. Need to show all patients currently in an infirmary bed at one of 8 different infirmary sites, as well as be able to show various bed availability across all sites based on the discharge date entered of the last patient in that bed. Need to track overall stay lengths for patients, per patient, per bed, per infirmary site. Need to be able to create a form that, based on the site ID entered, shows a) all current infirmary patients for that site; and b) all current open beds at that specific site. Also need to be able to allow the user to move a patient to one bed from another, without having to create a whole new record. HELP! Any ideas would be so appreciated. THANKS!

You need a table for patients and a table for beds. The bed table needs to identify its infirmary location. You have a many to many relationship between patients and beds so you need a junction table that links these and also records details of the occupancy such as start and end dates. You'll need other tables for infirmaries and so on. That should get you started.

Worth searching in these forums for other booking systems, hotels, hire cars, hospital beds, etc. They're pretty much the same in principle.