Query - include all fields from primary sheet

Question

Hello,

I am putting together an Access database to keep track of permissions and accesses given to each employee at are company for each application we have. I stated off by building a table that lists each employee. The a created a table for each application that lists the access groups for their respective application (one table for each application). Then I created one table for each application that had 2 fields: 1) a lookup wizard that always you to pick an employee from the list on the first table, 2) a lookup wizard that allows you to select the access the user has in that application from the table that has the list of accesses for that application.

Now to get to my question: finally, I created a query that lists all employees, and the access they have for each application, all on one nice little table. This works, great... for the 3 IT guys that have accesses in every application. The problem is, it won't list any other employee, because they don't have an access in every single application. Apparently, it will exclude someone who doesn't have something to put in every field.

Obviously, I would like to be able to see every employee regardless.

Hopefully that all makes sense.

Thanks in advance for your help.

Answer 1

tblEmployees: EmployeeID, names, other info about employees

tblApplications: ApplicationID, name other info about apps

tjxEmpApp: EmpAddID (PK Autonumber), EmployeeID (FK), ApplicationID (FK)

Scottgem I think there should be a fourth table to indicate AccessLv.   Then your tjxEmpApp: would another foreign key and multifield unique index of the 3 foreign keys.

Then there would be a record per employee/application/access.

Answer 2

Thank you all for your posts.

This may show my ignorance in Access, but the main issue with that I ran into what the fact that not all applications had the same accesses. So to create one table that had all the accesses wouldn't work.

I did create a way to make this work though. I created a macro that exported all tables to an excel spreadsheet. Then I created a connection for each of those applications to one central spreadsheet. I linked that speadsheet back to the Access database. That way I can filter it by user, and even create a report to do such.

I know this may be an ignorant, round-about way to make it work, but at least it works.

Answer 3

You'll find an example of how to model this sort of basic many-to-many relationship type as StudentCourses.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to amend the form design accordingly.  

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

The model for this simple little demo file is illustrated below:

In this Students are analogous to your employees and Courses are analogous to your applications.  The many-to-many relationship type between them is modelled by StudentCourses.  Status is analogous to the level of access to the application each employee is given

The database includes a qryStudentCourses query which joins the tables, and which is the RecordSource for the report.

Answer 4

First, your design is off. You have a many to many relationships here. One employee can have access to multiple applications and one application can be used by multiple employees. So you should have three tables here:

tblEmployees: EmployeeID, names, other info about employees

tblApplications: ApplicationID, name other info about apps

tjxEmpApp: EmpAddID (PK Autonumber), EmployeeID (FK), ApplicationID (FK)

The last is referred to as a junction table which models the many to many relationship.

Having a table for each application is not the right design. Neither is using Lookup fields on the table level.

I would have a mainform bound to tbleApplications with a subform bound to tjxEmpApp. On the subform (in Continuous Form mode) you would have a single visible control, a combobox to select an employee.

To create your query you would add all three tables, joining tjxEmpApp to tbleEmployees on EmployeeID and tblApplications on ApplicationID. Right click on the join line between tjxEmpApp and tbleEmployees and select Join properties. Select the option for ALL from tbleEmployees and matching from tjxEmpApp.

Then add the Employee name and Application name columns. This query will then list all employees with whatever application they have been assigned. If an employee has no app then the Application name column will be blank.

Answer 5

> The a created a table for each application that lists the access groups for their respective application (one table for each application).

I stopped reading here. That is a really bad idea, and a violation of relational database design rules. It will make anything else you do much harder.

There should be ONE table with the list of access groups, and a "junction table" to express the many-to-many relation between the Applications table and the AccessGroups table:

tblAccessGroupsForApplications

ApplicationID long int required PK

AccessGroupID long int required PK

(of course you apply enforced relations between the related tables/fields)

Also, have you given thought to the idea that this new database is what all applications should use when they apply permissions. That way there can never be a discrepancy between what you enter here and what is in effect in the application.