We're designing a database to track inventories at 33 hospital clinics in the Philippines. Before we move to far with the database, we wanted to get some feedback on our basic design. I'm dusting off my Access skills for the this project. :)
As mentioned, we have 33 clinics. Currently, we have an opening form with a combo box listing each clinic. When a clinic is selected and submitted, the appropriate form for that clinic is opened using DoCmd.OpenForm. The form uses queries to pull location data (i.e. what equipment is required according to their accreditations) and equipment information (i.e. required quantity, expiry date). All form data is stored on a master table which we use for reporting.
Would there be any benefit to having individual tables for each clinic, instead of having the forms update one master table?
Also, I know that we could probably use one universal input form. However, I'm not sure how to pass the location value from the initial form to the input form. This is important because the clinics will log in to provide data only for their location.
Any pointers would be helpful. I can research the appropriate functions and code, so if anyone can offer basic advice, we'll work with it.