Question Database with multiple departments (1 Viewer)

[Toolman]

Hi,

Sorry for the number of posts recently.

I'm our department's data geek. The Rehab Department has several sub departments like PT, Cardiac, Wound, and Speech. I keep dept stats and productivity info on all of them in Excel with the spreadsheets located on 2 different network drives, in a number of folders and sub folders.

Now I know that while there are things that are possible to due, it may not be the smart thing to do. I would to know know if my idea is one of those "not-so-smart-things-to-do".

I envisiond one Access application that opened to a switchboard offering a choice of departments that user would like to visit. Opening the dept of choice would lead the user to a host of reports/queries specific to that department.

The structure as I saw it: PT is the largest department and would have 6-8 related tables. The other depts are much smaller in volume and would have, at most, 2-3 related tables. None of the depts "share" info or need to be related to each other.

I will be the sole data entry person. The department heads just need to view the results of their labors.

Am I nuts? Should I have one file for each department? Or is there another way?

Thanks for your time.

Don

 

[Galaxiom]

Much of the same kind of data is being stored on each department? For this type of information not only would I put the departments in the same database file I would put their records in the same tables with a foreign key field to indicate each record's department.

Extra record types that only apply to some departments would be stored in other tables linked once again by the foreign key.

Separating the departments into different tables would require extra custom forms and reports for each department.

Forget the switchboard. They only support command buttons and decorative triviality.
Make a master form where you enter the department of interest in a combobox control. Include this control in the record source queries of the common forms and reports and they will automatically be configured to that department. Same reports, same forms, same button to open them.

It is quite easy to design the common forms and reports to hide the controls that do not apply to a particular department when it is selected. Where the information in the extra tables is essentially further detail to common information these reports could be opened from within the main report. The button could be made to only appear when applicable or even change its function and caption depending on the department.

Other data that applies solely to a department might have separate forms entirely. The buttons on the master form to open these could be disabled when a department they do not apply to is selected.

 

[Toolman]

GlaxiomAtHome,

You said a mouthful!! Thank you for your thoughts. I have a lot to research to get this show on the road.

Luckily this project is open ended. I can take what time I need.

Could I bother you again with a hint to some resources on-line that are examples of what you mentioned? Being a beginner in Access I mentioned the switchboard, not knowing about Master Forms.

Thank you very much for your help.
Don

 

[Galaxiom]

You have made the right first step by coming here before you start.

To begin, search this site for "Normalization". I have already introduced some of the concepts of Normalization in your other thread asking about how to format your dates. It is important to understand it before designing the table structure. If the table is structure right, the design of the rest is much more straightforward.

Switchboards do have their place but I am not a big fan of them. They make it easy for the inexperienced to design the navigation of the database but everything they do and more can be done on a form.