Group and sum multiple text columns in Access

I have a table (tbl_Charges) which lists by individual a main charge classification and within the main class, it lists up to 5 infraction classifications. These are in text form. I need to be able to total the infractions by their classification.
Ex:
1st_Class         2nd_Class       3rd_Class      4th_Class        5th_Class
Safety              Safety             Conduct         Performance     Conduct
Conduct           Attendance
Performance    Conduct         Safety

Report to show:
Safety: 3
Conduct: 4
Performance: 2
Attendance: 1

I just can not wrap my head around it.
HA94694Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Patrick MatthewsCommented:
Hello HA94694,

With respect, that is a very sub-optimal table design.  For now, this will get the counts:

SELECT z.Classification, COUNT(z.Classification)
FROM
      (SELECT 1st_Class FROM tbl_Charges
      UNION ALL
      SELECT 2nd_Class FROM tbl_Charges
      UNION ALL
      SELECT 3rd_Class FROM tbl_Charges
      UNION ALL
      SELECT 4th_Class FROM tbl_Charges
      UNION ALL
      SELECT 1st_Class FROM tbl_Charges) z
GROUP BY z.Classification
ORDER BY COUNT(z.Classification) DESC

Regards,

Patrick
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Patrick MatthewsCommented:
Now, you will be far better off in the long term if you alter your design so that instead of having 5 class columns,
you shift that stuff to a related table with one chanrge per row, thus normalizing your data.

To make the case for change: what happens if tomorrow you needed to accommodate a 6th, 7th, or even 8th
class?  If you have to alter your table design because you suddenly have N+1 of the same kind of entity to
worry about, that signals a bad design to start with.
0
HA94694Author Commented:
The issue is in the tbl_Charges, the File# is the primary key. I did try to break it out.
Within the File# is the employee charge. There can only be 1 employee per file# and it has to be unique, but within the charge file# there can be up to 5 infractions. It is unusual for anyone to have more than 3.
I have a tbl_Employee_Data where the employee's ID is the primary
and tbl_Charges with File# as primary with the relationship to each other. In tbl_Charges, the employee ID can be listed in there multiple times, but by only 1 unique file#
0
HA94694Author Commented:
I was able to figure it out with some help from your post.
The table is how it was originally set up.
The users also wanted a easy to use form and reports. By separating them out as you suggested, does not simplify their inputting data into a single form.

This is my code that works perfectly, the way I needed and wanted.

SELECT Class1,Count(Class1) AS CountOfClass1
FROM tbl_Charges
WHERE (((tbl_Charges.Terminal) Like "*" & [Forms]![frm_Main]![Terminal_rpt] & "*") AND ((tbl_Charges.Charge_Date) Between NZ([Forms]![frm_Main]![Begin_Date],#1/1/1900#) And NZ([Forms]![frm_Main]![End_Date],#1/1/2100#)) AND ((tbl_Charges.Discipline) Not Like "Charge* drop*" And (tbl_Charges.Discipline) Not Like "Charge* withdr*" And (tbl_Charges.Discipline) Not Like "* at fault"))
Group BY Class1
Having (((Class1) IS NOT NULL OR (Class1)<>" "))
Union
SELECT Class2,Count(Class2) AS CountOfClass2
FROM tbl_Charges
WHERE (((tbl_Charges.Terminal) Like "*" & [Forms]![frm_Main]![Terminal_rpt] & "*") AND ((tbl_Charges.Charge_Date) Between NZ([Forms]![frm_Main]![Begin_Date],#1/1/1900#) And NZ([Forms]![frm_Main]![End_Date],#1/1/2100#)) AND ((tbl_Charges.Discipline) Not Like "Charge* drop*" And (tbl_Charges.Discipline) Not Like "Charge* withdr*" And (tbl_Charges.Discipline) Not Like "* at fault"))
Group BY Class2
Having (((Class2) IS NOT NULL OR (Class2)<>" "))
Union
SELECT Class3,Count(Class3) AS CountOfClass3
FROM tbl_Charges
WHERE (((tbl_Charges.Terminal) Like "*" & [Forms]![frm_Main]![Terminal_rpt] & "*") AND ((tbl_Charges.Charge_Date) Between NZ([Forms]![frm_Main]![Begin_Date],#1/1/1900#) And NZ([Forms]![frm_Main]![End_Date],#1/1/2100#)) AND ((tbl_Charges.Discipline) Not Like "Charge* drop*" And (tbl_Charges.Discipline) Not Like "Charge* withdr*" And (tbl_Charges.Discipline) Not Like "* at fault"))
Group BY Class3
Having (((Class3) IS NOT NULL OR (Class3)<>" "))
Union
SELECT Class4,Count(Class4) AS CountOfClass4
FROM tbl_Charges
WHERE (((tbl_Charges.Terminal) Like "*" & [Forms]![frm_Main]![Terminal_rpt] & "*") AND ((tbl_Charges.Charge_Date) Between NZ([Forms]![frm_Main]![Begin_Date],#1/1/1900#) And NZ([Forms]![frm_Main]![End_Date],#1/1/2100#)) AND ((tbl_Charges.Discipline) Not Like "Charge* drop*" And (tbl_Charges.Discipline) Not Like "Charge* withdr*" And (tbl_Charges.Discipline) Not Like "* at fault"))
Group BY Class4
Having (((Class4) IS NOT NULL OR (Class4)<>" "))
UNION SELECT Class5,Count(Class5) AS CountOfClass5
FROM tbl_Charges
WHERE (((tbl_Charges.Terminal) Like "*" & [Forms]![frm_Main]![Terminal_rpt] & "*") AND ((tbl_Charges.Charge_Date) Between NZ([Forms]![frm_Main]![Begin_Date],#1/1/1900#) And NZ([Forms]![frm_Main]![End_Date],#1/1/2100#)) AND ((tbl_Charges.Discipline) Not Like "Charge* drop*" And (tbl_Charges.Discipline) Not Like "Charge* withdr*" And (tbl_Charges.Discipline) Not Like "* at fault"))
Group BY Class5
Having (((Class5) IS NOT NULL OR (Class5)<>" "));
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.