Microsoft Access - Best Design for Inventory Database with Multiple Locations

Asked By Michael Grabowski on 07-Oct-09 12:03 AM
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.

Thanks! Salamat!

This design should depend based on the users and data - Venkat K replied to Michael Grabowski on 07-Oct-09 12:38 AM

size that is inserted into database.

If the data is huge and more number of records are being updated into databse for each location then it is better you can normalize the table and maintain individual table for each location. Since if you maintain only one table it will degrade the performance.

So in master table you can maintain the location id and Location names and master data.


ID LocationID LocationName Address PIN Phone (details related to each location)


Sub tables

ID LocationID (reference to above table) PID PName PAddress PContact (sub details related that location)

If the data that will inserted into DB is less then you can maintain all the above columns in a single table.


Thanks - Asked By Michael Grabowski on 07-Oct-09 12:47 AM

Thanks for your response. I see what you mean with the table designs and we'll go that way.

Now, we prefer to keep our initial location prompt form. This form displays a drop-down of all the clinics and once they press submit, opens an input form that restricts them to their location data through query. We have this because data is entered by people with very limited computer skills. Having a location combo on the actual form would increase the possibility that someone will accidentally edit data for other locations. The initial location form also just feels nicer as a UI element.

My remaining challenge is that I need to pass the ClinicID over to a common input form so I can query information for the selected clinic. I seem to remember coming across a post about passing temporary values to a form's tag. Is this the right direction? If so, does that work in Access 2003 as well?

Thanks again for your response. It's helpful and we appreciate it.

Size - Michael Grabowski replied to Venkat K on 07-Oct-09 12:49 AM


We'll be dealing with a small amount of information. Maybe 150 equipments maximum at each clinic. So it sounds like tables for each location would be overkill.

Thanks for your response!
Passing Value to RecordSource - Michael Grabowski replied to Michael Grabowski on 07-Oct-09 01:52 AM
Thanks for the guidance as I refresh what Access skills I used to have.

The table design is set, but as I mentioned, I need to pass a value from a location selection form to RecordSource on a universal input form. What I plan to do is create queries for each location, then pass that value to RecordSource once a location is selected from the initial database form.

I'm poking around for this one, but any help would be appreciated. Am I on the right track?

Best Design for Inventory Database with Multiple Locations - mv ark replied to Michael Grabowski on 07-Oct-09 02:51 AM
To decide on whether to co-mingle clinic data in a single database with a single schema or to use a single database, but separate clinic data into different schemas, check this article -

For help with db design, check this -
Thanks - Asked By Michael Grabowski on 08-Oct-09 04:20 AM
I found out how to pass OpenArgs the other and it works for what we want.

The functionality is to maintain a certain UI feel and also to serve as a safeguard so our facilities stay in their area of the database.

Thanks for your help on this!