Finding a percentage

[espinobj]

Hello world. Im new to Access and have done my best to teach my self however Ive hit a road block and need some help, so go easy on me as this is my first post here as well. Hopefully I can explain this well enough to get some direction. Some back ground is I audit Fiber optic test results, I have one table with upwards of 250K results in it.

Simply put I need to get a percentage of results from a given serial number on a given day.
I would like the final report to show date, S/N, and the percentage



Code for the main Query. This guys ultimately find what results are negative. As you see it checks 4 columns for a negative value only one needs to be negative to qualify. It pulls its information from the main table. I need to know the percentage of results a particular S/N tested that fit the criteria below.

SELECT 
NegLoss.[OLTS Loss Value End1 WL1] AS [R>M 1310nm], 
NegLoss.[OLTS Loss Value End2 WL1] AS [M>R 1310nm], 
NegLoss.[OLTS Loss Value End1 WL2] AS [R>M 1550nm], 
NegLoss.[OLTS Loss Value End2 WL2] AS [M>R 1550nm], 
NegLoss.[Cable ID], NegLoss.[Overall Result], 
NegLoss.Closet, NegLoss.Date, 
NegLoss.[Main S/N]
FROM NegLoss
WHERE (
((NegLoss.[Cable ID]) Not Like "TRC*") AND (
((NegLoss.[OLTS Loss Value End1 WL1])<0) OR  
((NegLoss.[OLTS Loss Value End2 WL1])<0) OR 
((NegLoss.[OLTS Loss Value End2 WL2])<0) OR
((NegLoss.[OLTS Loss Value End1 WL2])<0)
))
ORDER BY NegLoss.[Cable ID];

I cant seem to figure out how to combine the two queries to give me a percentage. We have multiple serials per day. Not all days will have a neg loss event. Some results may have multiple events. Im not sure what other information you may need so let me know what I can do to help you out.

[June7]

This may be easiest done in report design. Report allows display of raw data records as well as summary calcs. Calculate a total in report and/or group footer section. Then in a textbox in detail and/or group section reference the group or report footer textbox to pull the total for use in percentage calc.

[espinobj]

I would have a large amount of totals here is an example of that data set. How would I essentially link the two sets of data? I was thinking of doing something that I do to find duplicate test results. I created a new table with a "Unique ID" So in this case I could append the date and serial together to create a unique ID with a total over all results. I could then do a separate query that makes its own table with a "Unique ID" with a total of the neg loss results then run a query to link the two and do the math. Would there be an easier way however?

Date	Main S/N	CountOfCable ID
6/28/2017	2534668	226
6/27/2017	2534668	14
6/23/2017	2534668	39
6/22/2017	2534668	33
6/14/2017	2587593	318
6/14/2017	2653550	914
6/13/2017	2534668	146
6/13/2017	2587593	955
6/13/2017	2653550	624
6/12/2017	2534668	1198
6/12/2017	2587593	839
6/12/2017	2653550	1446
6/9/2017	2534668	300
6/9/2017	2587593	167
6/9/2017	2653550	536
6/8/2017	2534668	1552
6/8/2017	2587593	557
6/8/2017	2653550	1968
6/7/2017	2534668	1030

[June7]

Don't see why my suggestion would not apply.

You want a percentage for each S/N of the total records that meet the 'negative' criteria?

Build report based on the filter criteria. Do calcs as described.