Conceptualizing and Executing a Complicated Database

[asset33]

I truly do apologize – I know you all must get a great deal of e-mail begging you for help with solving problems. I wish I could say that this is different! However, I was hoping to get advice and expertise with a major problem that I am having. I am currently in the process of attempting to set-up a database, but am having trouble conceptualizing the design of the database.

I DO realize that ordinarily when designing a database, this would all pretty much be HORRIBLE. This goes against EVERYTHING I know about database design and creation. But I have to work with what currently exists...

Basically, there are 20 facilities, each of whom have been keeping Excel spreadsheets for the past few years of very specific data. Each clinic’s data fields are exactly the same, with 9 data fields. So we’re looking at 20 spreadsheets with the same basic information – Name, ID#, Date, Doctor, etc.

What I have been asked to do is create one big table in Access using these spreadsheets that will update in real time (or, close to it). For example, the entire database would be stored on a shared drive in the network that each clinic could open and access. Each day, each clinic would open “their” table, and use a customized form (created in Access) for data entry into their table. Using the form would automatically add the information into “their” table. Once finished, they would save their work, which would update the database stored on the shared drive. Each clinic’s “own” table being updated would also feed into the “main” database, that is, the information from all of the clinics’ tables. This one table would then be used to run reports and do queries.

Based on my internet research and reading books about Access, the best I’ve come up with in order to “stack” this data and get it to update the main table is to create a Union query. But do you know if a Union query would allow you to run queries off of the existing query without making the query and report-making process confusing for beginner users in Access? And would updating and adding more data to the individual tables also change the “main” table, created by the Union query? (this is a necessary function in the database)

What solutions would you suggest? How would you guys go about creating/linking/joining this database? Any and all input from you all would be greatly appreciated! Thank you so much for your help.

 

[georgedwilkinson]

I suggest the only one table approach with a couple of changes:
1. Normalize (that means you'll have more than 1 table, seems like a paradox, doesn't it?). Doctor names should be in their own table with a FK in the "main" table. Probably same with Name. If clinic is involved, so should clinic.
2. Only one table and only a single query for the main form. In the query's where clause, put something like:

clinicname = GetClinicName()

Have a global variable that gets the clinic's name when the DB starts up. Return that global variable in the custom VBA function "GetClinicName()". This query will be the basis for your "main" form.
3. In the "main" form's open event, make sure the "clinic" field defaults to the global clinic referenced above.
4. Use a split database design. Each clinic should have a unique copy of the front end (pointing to the backend) on their pc(s). Use Bob Larson's front end update program to keep your copies in sync (http://www.btabdevelopment.com/main/MyFreeAccessTools/tabid/78/Default.aspx).
5. Make sure your design is normalized. Don't make the unique key the primary key, use an autonumber. Make the unique key a unique index. This is the most important step. See step #1 for another reminder.

This should be moderately simple. Post back if you need more help.

 

[Rabbie]

Two main things to remember.

1. Access is not Excel. I know it seems obvious but if you look through old posts on this forum it is surprising how many people want to make everything look like spread sheets. To repeat the point in post 2 you need to have your data normalised or you will just make everything more difficult for yourself.

2. Work out what you are doing on paper. it's been stated on these forums many times "If you can't do it on paper, yopu won't be able to do it in Access". Also Access can't tell you anything you havent already told Access.

Union queries are not updatable. How would Access know which of the constituent tables to modify?

You should look at Allen Brownes excellent site www.allenbrowne.com

Also Bob Larson has a very good site as well. http://www.btabdevelopment.com

 

[dfenton]

I would suggest creating a single data file and having the 20 locations run the app on Windows Terminal Server. Otherwise, you have to deal with synching the data between the different locations.

 

[DCrake]

Simple Software Solutions

Can I throw my twopenneth in here as a suggestion.

As you all indicate using split databases is the norm, all pointing to the same back end.

In instances where you have multi sites I tend to split by back end into two parts. One that holds data that changes on a daily basis. Such as customer sales, patient episodes, etc. And another one that contains all my lookup tables, Such as Departments, Report Names, Ethnic Origins, Gp Master Files, Practice Master Files, etc. So if you need to perform occassional updates on the database back end you can specify which one to use.

for example I get monthly revisions of GP And Practice MasterFiles As these are exclusively used as lookup tables these are located in the support files mdb. I can peform updates, run a compact & repair etc without inconveniencing the users. And should anything go wrong I know I will not be affecting the users data.

Also by seperating the two it influences the issue of bloating.

CodeMaster:

 

[PaulJR]

I must say that the idea of splitting the backend is brilliant and would most likely suit my application as I have one main table that is updated daily, and several other lookup tables that are only updated by me. The tables will soon get pretty big.

I have Access books, often read tutorials and app notes on the Microsoft website....but none of them compare to the real life issues people have on these forums, and the kindness and generosity of the people that contribute to them. I am amazed with this site and only wish I had come across it sooner, and that perhaps on the odd occasion I might be in a position to contribute.

 

[DCrake]

Simple Software Solutions

Thank you for your praise, over 20 yrs of being a self taught programmer has enabled me to develop little tricks like this. However, I can still pick up tips myself all the time, and like you admire this site for its generosity.

CodeMaster:

 

[DCrake]

Simple Software Solutions

Taking this issue to its nth degree I once had a situation where I needed to import sales transactions from over fifty outlets on an overnight schedule. These were imported into one table, deleting the existing records from the previous day first. This led to severe bloating. Once the data was in the "Dump" table data was validated and converted to acceptable format before being appended to the live table.

My end solution was to have an empty blank database on the server. The first action was to delete a named mdb then use file copy to copy the blank mdb to a specified mdb (The one I just killed). Then run a make table query into the newly created mdb. The table it created already existed in the front end as a linked table. All I did then, for peace of mind, was to relink the table in the newly created mdb. This kept bloating down to a minimum.

We are talking about 50+ outlets open 6 days a week performing on average 500 sales transactions daily.

It was a franchise setup and the head office wanted to monitor sales at each outlet so that they got the right commission.

So if you say ((50*6)*500) * 52 gives you a single years sales transactions.

Equals (c) 7.5m records a year

And they would have queries would say "How many video recorders did we sell in Kilkaldy last Wednesday?"

Pheww