Displaying a report with data from multiple columns (1 Viewer)

[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?

 

[Pat Hartman]

The problem is, the program could be in any column in the table.

You can do this with your current structure by using a where clause with four conditions. One for each program and connected with OR's.

Where Program1 = "abc" or Program2 = "abc" or Program3 = "abc" or where Program4 = "abc"

However, as the others have mentioned, it will be far better going forward if you actually normalize the structure so that the table is:

UserName
ProgramName

Once normalized, the Where would be:
Where ProgramName = "abc"

AND, you won't have to change your forms/reports/queries/table if you add a fifth program

That way each program is in a separate row rather than a separate column. What you have now is a "spreadsheet" and not a relational table. You can use a compound PK of both columns or you can use an autonumber as the PK (which is preferred) but then you need a unique index on the two fields which MUST be built using the indexes dialog. The GUI only lets you create single field indexes although it allows you to create multi-field PKs.

 

[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.

 

[Pat Hartman]

I just can't figure out how to get the report I need (due to my tables being out of wack!!)

Not only did I tell you how to normalize the table which is the correct solution but I also told you how to make the query with the tables as they exist today. #4. Did you not understand the instructions for either method?

I'm just not a DBA

Access developers are not DBA's. But if you aspire to develop applications using Access or any other RDBMS, you MUST learn the basics of normalization.

When you create columns with numeric suffixes, ask yourself, what would Amazon do? What if I had dozens or hundreds of programs? They are certainly not going to be creating tables with hundreds of columns with a common name and a numeric suffix. That would be a nightmare to work with. Would you create a class roster by adding one column for each potential student? Some classes will have a smallish number of students, maybe 20. But what about lecture halls that can hold hundreds? How are you going to ensure that Johnny isn't enrolled multiple times in the same class, among many other types of questions.

It is very important to work with a firm foundation so even though I gave you the "now" solution, I suggest that you actually solve the problem and normalize the table. Do not take shortcuts. They always end up taking more time than doing it right the first time.

 

[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.

 

[Pat Hartman]

Here is a m-m example.

Many-to-many example

I added a new version on 1/16/23 so choose that one rather than the old one unless you specifically want the old one. The old zip file contains both A97 and AXP versions of the database. It uses venue booking as the example and shows the relationship from both sides. A venue main form with...

www.access-programmers.co.uk

It shows two different methods of working with m-m relationships. From the Employee view, the form uses a subform to show the classes. From the Classes view, the form uses the double-click event of Class title to open a class form. In both cases, the "other" side of the relationship comes from a subform or form bound to the junction table.