How to create query across multiple tables (1 Viewer)

[vcarrill]

I have a database with a: (form, table, and subform) for each MFG Supervisor.

The Prod Manager does not want the supervisors to have access to each others employee information.

How would I then create a query that will run a report to capture the data from the various supervisors under (1) report, or will it have to be separate reports?

Thank you

 

[Gasman]

You would have them all in one set of tables and then only present the data for that particular user.?

 

[theDBguy]

Hi. You can create separate reports and then combine them all into one as subreports (unless you mean to mix the data from all of them).

 

[Pat Hartman]

Let me expand on Gasman's suggestion.
1. You would use a single table/query/form rather than one for each Supervisor. This is Access not Excel. Things are done more efficiently in a relational database.
2. You need some type of login so you can keep a table that lists all Supervisors. You may need additional tables for security but I don't have enough information.
3. Your form will be bound to a query that selects only data for the logged in user. This is where I think we need more tables. I don't know what data you are trying to secure. It seems wrong to tie everything to a supervisorID since that supervisor might be replaced by a different supervisor and you don't want to have to run update queries to update potentially thousands of records to tie them to a different supervisor.

I have implemented similar requirements but they were tied to something that doesn't change like a department or a job. So only the supervisor assigned to a department can view data relating to that department. Or only the supervisor assigned to a job can view data relating to that job. There also needs to be some overriding authority that can see all data. You will need at least one extra table to relate jobs to supervisors or departments to supervisors if the relationship is many-to-many. If the relationship is 1-m, then the SupervisorID just gets put into the Job record or the Department record and each can have one and only one supervisor at a time. If you need history, you would need to create a history table so you can see who was the supervisor for that department in Sept of 2011.

Please tell us more about the data you are trying to secure.

 

[vcarrill]

Let me expand on Gasman's suggestion.
1. You would use a single table/query/form rather than one for each Supervisor. This is Access not Excel. Things are done more efficiently in a relational database.
2. You need some type of login so you can keep a table that lists all Supervisors. You may need additional tables for security but I don't have enough information.
3. Your form will be bound to a query that selects only data for the logged in user. This is where I think we need more tables. I don't know what data you are trying to secure. It seems wrong to tie everything to a supervisorID since that supervisor might be replaced by a different supervisor and you don't want to have to run update queries to update potentially thousands of records to tie them to a different supervisor.

I have implemented similar requirements but they were tied to something that doesn't change like a department or a job. So only the supervisor assigned to a department can view data relating to that department. Or only the supervisor assigned to a job can view data relating to that job. There also needs to be some overriding authority that can see all data. You will need at least one extra table to relate jobs to supervisors or departments to supervisors if the relationship is many-to-many. If the relationship is 1-m, then the SupervisorID just gets put into the Job record or the Department record and each can have one and only one supervisor at a time. If you need history, you would need to create a history table so you can see who was the supervisor for that department in Sept of 2011.

Please tell us more about the data you are trying to secure.

What you just described would be ideal! I'm aware this is access and not excel, but I lack the expertise.

Can you point me to some material or a video that may assist me in doing exact what you just said!

As you said, I don't need or want to "tie" the information by supervisor, so I changed it to department.

Ideally I need to create separate logins for each supervisor and for a superuser who can see everything.

The Production Manager only wants a supervisor to see the employees for their department and that's it, not any other supervisors employee data.

Greatly appreciate your advice thank you.

 

[Pat Hartman]

I don't know of any specific instruction. This requirement is not unusual but it isn't common enough for people to write about it. I've attached a sample custom switchboard sample that includes rudimentary login security. The security is based on objects rather than what you need which is data but it's a start and it makes perfect sense to use both in your app. You can incorporate it (and the switchboard if you like it).

I added a department table and added DeptID to the users table and the LogIn form but I did not build the additional custom layer of security for you because there weren't any forms in the sample that would make sense secured this way.

You will need to decide how handle the super user. One way is to create a superDept and assign that to the superusers. Then the criteria in the form's RecordSource queries where you need this security will be something like:

Where DeptID = Forms!frmLogin!txtDeptID OR Forms!frmLogin!DeptID = 1.