crating a db (access XP)

Topic

New Reply

We have three departments (COP, CCP, SHH) each department have 10-15 employees and all the departments use the same form. Every now and then, a department head or a supervisor from one department would like to have a view only access to another departments record. I have to generate a report every month, every quarter, and at the end of the year (first by users, second by department ). The way I plan this process is to design three table (tblUsersCOP, tblUsersCCP, and tblUsersSHH). The form also will have few drop downs, (e.g. GENDER: male, female RACE, AGE, etc.) but not sure if I should create a table for all my dropdowns.
1. If I crate only one table (tblUsers) how do I go about assigning which user has access only to their department database?
2. I’m familiar w/ value list, but not sure once I create lookup table how to link them.

Thank you in advance for your help!
OCM

Reply | Quote

Replies

I assume that right now you have three separate databases. If that’s the case, you could active Access User Security, and give Read access to users from other departments. By using User Security you could also collapse all three databases into one, and you could dispense with the tblUsers and simply define them in the Security system. You also have the advantage of being able to track who made what change. You could get a similar effect by using the Windows Networking file permissions, and setting other departments to have only read access on the folders of a given department, but then you run into issues with file locking and such. You might also want to split your database and have a common front-end on each workstation – if you decide to have three backends, each frontend could connect to all three initially, or you could do it dynamically if you want to make connections using VBA. If you want further amplification on any of this, please post back.

Reply | Quote

Wendell,
Actually, the person before me didn’t know Access and created an Excel spreadsheet for data entry and reporting purposes. so we don’t have any existing database. Each department is growing and Excel start giving us a lot of problems thus, I would like to create a new database from scratch.

Yes, my plan as you suggested is, to split the database and have a common front-end on each workstation. But for now I just want to get feedback as to how the design/structure of the database should look like.

Thanks,
-OCM –

Reply | Quote

Wendell,
Actually, the person before me didn’t know Access and created an Excel spreadsheet for data entry and reporting purposes. so we don’t have any existing database. Each department is growing and Excel start giving us a lot of problems thus, I would like to create a new database from scratch.

Yes, my plan as you suggested is, to split the database and have a common front-end on each workstation. But for now I just want to get feedback as to how the design/structure of the database should look like.

Thanks,
-OCM –

Reply | Quote

I assume that right now you have three separate databases. If that’s the case, you could active Access User Security, and give Read access to users from other departments. By using User Security you could also collapse all three databases into one, and you could dispense with the tblUsers and simply define them in the Security system. You also have the advantage of being able to track who made what change. You could get a similar effect by using the Windows Networking file permissions, and setting other departments to have only read access on the folders of a given department, but then you run into issues with file locking and such. You might also want to split your database and have a common front-end on each workstation – if you decide to have three backends, each frontend could connect to all three initially, or you could do it dynamically if you want to make connections using VBA. If you want further amplification on any of this, please post back.

Reply | Quote