need help with Sum in a query

[RickK]

My query shows all records within a date range and other criteria. See below.
Currently it list each serial number (machine) on a separate line and then shows downtime for that incident and it also shows availability by percentage based off of coverage hours. Example if serial number 123 has 10 hours down and coverage hours are 20, then percentage is .5 or 50 percent and this works correctly. But if the serial number 123 has two or more calls it shows each call and the percentage of downtime per call.

I need to figure out how to combine any records with the same serial number and display the combined downtime , then the percentage of total downtime. Using the example above if I had two records for serial 123 one for 10 hours downtime and one for 5 hours downtime the total downtime would be 15 hours/ 20 hours available for .25 percentage. It should not add both percentage up and average them because it would end up at .62.5 which is incorrect for what I need.
So how could I combine all records by serial number?

Attached is a screen shot my DesignView and DataSheetView .

I hope this makes sense and thanks for any input
RK

 

[plog]

Using your datasheetview.jpg answer these questions:

1. How many records do you want to show up in your final result? 1 or 10?

2. If 10 records, do you want them all to have the same Availability & Percentage values?

 

[RickK]

Using your datasheetview.jpg answer these questions:

1. How many records do you want to show up in your final result? 1 or 10?

2. If 10 records, do you want them all to have the same Availability & Percentage values?

7, there were seven different serial numbers returned.

 

[plog]

Huh? Where does 'were' come from?

You provided a screen shot with 11 records (I miscounted before). I asked how many you expect. Are you telling me you expect 7 records returned by your query?

 

[jdraw]

There are 7 unique serial numbers.
You need (at least/as a start) a query that does a grouping by serial number.

 

[RickK]

I run a report /Chart using this query and if I select GroupBy in the row source for the percentage I end up with two or more bars for each serial number depending on how many were found in the query. See attached GroupBy and look for serial number 6-6\156 there is one for .5 and one for .965. I would like to combine these and show as one with a total percentage.
If I change it from GroupBy to Avg (see AVG attached), then it will combine them into one but it takes the two percentages .5+.965 and then divides to get the avG of .7325. The number I should get is .465 , which is the total amount of availability , 10 hours on one record and .7 for the other record so 10.7 hours down during a 20 hour period equals .465 availability.

Thanks
RK

 

[RickK]

Huh? Where does 'were' come from?

You provided a screen shot with 11 records (I miscounted before). I asked how many you expect. Are you telling me you expect 7 records returned by your query?

Sorry, typo, "I meant to say there are seven"

 

[RickK]

There are 7 unique serial numbers.
You need (at least/as a start) a query that does a grouping by serial number.

Bare with me , I am not that great in Access but ,with the help from folks here I am getting better.
I tried using group by in my query under the Serial number field , but that requires me to choose something for the rest of the fields in the query and nothing I selected would work. How would I do this?

 

[jdraw]

I think we need to see a sample database. Can you send a copy with some test data? Just enough to highlight the issue.

 

[RickK]

I think we need to see a sample database. Can you send a copy with some test data? Just enough to highlight the issue.

I will need to remove customer data out and then I will post up something later.

Thanks
RK

 

[jdraw]

A handful of records that shows multiple serial numbers and your report/form. You can use Porky Pig , Daffy Duque for customers.
Good luck.