[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1052
  • Last Modified:

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.
0
HA94694
Asked:
HA94694
  • 2
  • 2
1 Solution
 
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
 
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

Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now