Count Question Across Multiple Fields - Rusty Access User Alert!

[suziebd33]

Hi Access Friends!

It has been quite some time since I used the Access SQL part of my brain, but I am trying to create a simple report with Access 2016 version to do the following:

COUNT the repeating ticket numbers in fields, "Ticket_1", "Ticket_2", "Ticket_3" and show the total count AS NumberOfCallsPerTicket

Here is my test table: custom_table

status	addi_status	appl	call_date	call_time	tsr	subject_id	ticket_1	ticket_2	ticket_3
OS	R2	CITE	6/4/2018	8:38:07 PM	BSB	1S12345	INC123	INC456	INC789
OS	E2	CBMY	6/4/2018	8:39:20 PM	BSB	1S12345	INC123	INC789
OS	E2	CITE	6/4/2018	8:40:31 PM	BSB	1S12345	INC123	INC987
OS	R2	CBMY	6/4/2018	8:41:34 PM	BSB	1S12345	INC123	INC789
OS	R2	CBMY	6/4/2018	8:42:48 PM	BSB	1S12345	INC123	INC456
IS	R1	CITE	6/4/2018	8:46:03 PM	BSB	1S98765	INC333

I have it working when picking out of "ticket_1" column only...

subject_id	ticket_1	NumberOfCallsPerTicket
1S12345	INC123	5
1S98765	INC333	1

Here is my statement for the above:
SELECT subject_id, ticket_1, COUNT(ticket_1) AS NumberOfTicketsPerCall
FROM Custom_mycite
GROUP BY subject_id, ticket_1
ORDER BY subject_id, ticket_1

I am trying to get results similar to the below totals which I calculated by hand. I also want to include the "subject_id" field per row/count result as shown in the above screen shot.

INC123=5
INC333=1


INC456=2
INC789=3
INC987=1

Any insight/advice/assistance would be greatly appreciated!!!

Kind regards,
suziebd33

[Perceptus]

This is a bit of a kludge, but would this work for you?

SELECT "Ticket1" as tid, subject_id, ticket_1, COUNT(ticket_1) AS NumberOfTicketsPerCall
FROM Custom_mycite
GROUP BY subject_id, ticket_1
ORDER BY subject_id, ticket_1
Union All 
SELECT "Ticket2" as tid, subject_id, ticket_2, COUNT(ticket_2) AS NumberOfTicketsPerCall
FROM Custom_mycite
GROUP BY subject_id, ticket_2
ORDER BY subject_id, ticket_2
Union All 
SELECT "Ticket3" as tid, subject_id, ticket_3, COUNT(ticket_3) AS NumberOfTicketsPerCall
FROM Custom_mycite
GROUP BY subject_id, ticket_3
ORDER BY subject_id, ticket_3

Its also possible that I completely missed what you were looking for. This is the first thing I thought of

[pbaldy]

The repeating fields (ticket 1, etc) are a design mistake:

Fundamentals of Relational Database Design -- r937.com

I'd fix the design so those were in a related table. As is, you can use a UNION query to normalize the ticket data and do your counts on that. But it's a workaround, I'd fix the design.

[suziebd33]

The repeating fields (ticket 1, etc) are a design mistake:

Fundamentals of Relational Database Design -- r937.com

I'd fix the design so those were in a related table. As is, you can use a UNION query to normalize the ticket data and do your counts on that. But it's a workaround, I'd fix the design.

@ Perceptus AND pbaldy: Thanks to you both, I think what I am hearing is create more tables to make this less clunky. I figured I would need to end up doing so, but just wanted to make sure I hadn't forgotten something simple. I haven't used access in ages.

Back to the drawing board, thanks again to you both!

[pbaldy]

I got notification of a moderated post, but don't see one. Did you delete it? It's moderating because of the link, which will stop after you have a few more posts. It's an anti-spam setting on the forum.

[suziebd33]

I didn't delete it, I was trying to thank you both for the quick responses and to let you know that I am taking your advise and going to create a couple more tables to make it less clunky

Thank you again!
-Suzebd33

[pbaldy]

It just now showed up (post 4) so I approved it.

Welcome to the site by the way!