Displaying a report with data from multiple columns

[Dixonka74]

I'm new to Access and have been creating a database to track and monitor what programs our employees have access to. Now, I want to run a report to show who has access to a single program. The problem is, the program could be in any column in the table.

Here is what the table looks like:

I want to run a report to show who all has program ABC in their record. I'm assuming I could change everything and simply create a column for each program and use a check box and filter only the checked items but, we have over 150 programs...

Hopefully I explained it well enough for someone to throw me a bone!!

 

[theDBguy]

Hi. Welcome to AWF!

Unfortunately, your table structure is not properly structured that's causing you this problem. You may have to review the information about Database "Normalization" Rules to help you fix it.

 

[mike60smart]

I'm new to Access and have been creating a database to track and monitor what programs our employees have access to. Now, I want to run a report to show who has access to a single program. The problem is, the program could be in any column in the table.

Here is what the table looks like:
View attachment 110912


I want to run a report to show who all has program ABC in their record. I'm assuming I could change everything and simply create a column for each program and use a check box and filter only the checked items but, we have over 150 programs...

Hopefully I explained it well enough for someone to throw me a bone!!

Hi
As theDBguy said your tables are not normalised.

Can you upload a zipped copy of the database?

 

[Gasman]

And what happens when you start having program 151?

If you normalise, you would have a table along the lines of
UserProgramID. - Autonumber
UserIDFK - foreign key from user table
ProgramIDFK - foreign key from program table

Then it would be a simple query.
Then if program 151 is needed, just more of the same.

 

[Dixonka74]

And what happens when you start having program 151?

If you normalise, you would have a table along the lines of
UserProgramID. - Autonumber
UserIDFK - foreign key from user table
ProgramIDFK - foreign key from program table

Then it would be a simple query.
Then if program 151 is needed, just more of the same.

I understand what you're saying but, I'm confused about 1 portion. I've created a simple database where I have the following tables:

UsersT
ProgramsT
UserProgramT

I set up the relationships where UserProgramT is linked to each FK (Users and Programs). but, how do I then associate user "Keith" with program "ABC"? I'm assuming I would accomplish that in the UserProgramT table (a single entry for each program the user has)?

Appreciate the assistance. I'm trying to do it the right way the first time!!

 

[mike60smart]

I understand what you're saying but, I'm confused about 1 portion. I've created a simple database where I have the following tables:

UsersT
ProgramsT
UserProgramT

I set up the relationships where UserProgramT is linked to each FK (Users and Programs). but, how do I then associate user "Keith" with program "ABC"? I'm assuming I would accomplish that in the UserProgramT table (a single entry for each program the user has)?

Appreciate the assistance. I'm trying to do it the right way the first time!!

Hi

Your Input Form should look like the attached

 

[mike60smart]

I understand what you're saying but, I'm confused about 1 portion. I've created a simple database where I have the following tables:

UsersT
ProgramsT
UserProgramT

I set up the relationships where UserProgramT is linked to each FK (Users and Programs). but, how do I then associate user "Keith" with program "ABC"? I'm assuming I would accomplish that in the UserProgramT table (a single entry for each program the user has)?

Appreciate the assistance. I'm trying to do it the right way the first time!!

You would need to create a Subform based on the UserProgramT and Link the UserID PK to the UserID FK

 

[Dixonka74]

Okay, I think I'm getting it now... I appreciate the assistance!!

 

[mike60smart]

Okay, I think I'm getting it now... I appreciate the assistance!!

I have sent you a message via the Conversation window.

 

[Gasman]

I have a similar setup to yours for my Bibbys DB.
The controlling table is called Links.
It has it's own autonumber key, but then just foreign keys, for Date, Crew, Ship & Rank.

With that table, I can show everything I need for the website Bibby-gazette.co.uk that now emulates the Bibby Gazette that used to be issued when I was serving with Bibbys.

 

[MajP]

I'm new to Access and have been creating a database to track and monitor what programs our employees have access to. Now, I want to run a report to show who has access to a single program.

One nice thing about a Many to Many relationship is that the information can be shown from either perspective, but stored very simply.

The junction table
UserProgramT UserProgramT

UserProgramID	UserID_FK	ProgramID_FK
1​	1​	2​
2​	1​	3​
3​	1​	4​
4​	1​	9​
5​	2​	2​
6​	2​	4​
7​	2​	31​
8​	3​	4​
9​	3​	20​
10​	3​	81​
11​	1​	10​

Users and their programs

Programs and their users

 

[gemma-the-husky]

When you normalise data the structure changes in a way that avoids the blanks on each row. You don't really want a "sparse" structure like that, as it produces the type of problems you are getting.