Totaling and ranking results in queries

[dmellman]

Hello, All. This is my first post and I am using Access as a tool to link to data tables in a Pervasive database with ODBC, using queries and reports to extract and report molding press downtime and prioritize it. I am able to get the data with a query that selects rows of run data for each press and I can report that out.

I have not figured out how to generate a report or query that totals records by press (but does not show them), and then ranks the presses by the totals in descending order. Example data:



Press-AvailHrs-DownHrs-%DownTime*
13-8-2-25
13-8-1-12.5
13-8-0-0 (Overall % Downtime 12.5)
15-8-4-50
15-8-3.5-44
15-8-3-37.5 (Overall % Downtime 43.7)

*(this is an query expression=DownHrs/AvailHrs)

I want my report to list only two rows in descending order by Overall % Downtime, i.e.,

Press-Overall % Downtime
15-43.7
13-12.5

I was able to figure out how to make a ranking of descending
%Downtime by cascading queries, but I can’t get past this point.

I would greatly appreciate your help. (I searched the forum but could not find anything close.)

[NTC]

well to start - the press overall avg doesn't involve the per day avg value - since one can not avg an avg so we work with this data:
13-8-2
13-8-1
13-8-0
15-8-4
15-8-3.5
15-8-3

make a query that returns the data above - - and then modify it to be an Aggregate query (look that up in your textbook example - the epsilon E icon). And set the hours to be summed per press so you have this:

13-24-3
15-24-10.5

the next step then is to use this query as the source for a new query that addes the calculated avg value you need:

13-24-3-12.5
15-24-10.5-43.75

now you have a record source to use for a report - and do your ordering as part of the report feature....

Hope it helps.

[dmellman]

Thanks, NTC, for the fast reply. Yes, understood on averages of averages. So I tried earlier to do the epsilon function, which added the "Group by" line in my query. I set the AvailHrs and DownHrs columns to sum (did not touch the other columns) and when I ran the query, I got SumofAvailHrs and SumofDownhrs in each line of the query, but they only have the sums of each of those records, no aggregation (if that is correctly stated). Where does that come from?

[rpeare]

can you post a sample database, I suspect you are using a GROUP BY field that is forcing your data to expand (for instance a shift number or primary key field) and removing that field will properly summarize your data.

[dmellman]

OK. I tried a table with exactly that data and queried it, and it worked as you said it would, NTC, giving me the aggregated data.

However, I looked at the original table and can see no primary key defined, and no other obvious things in the table structure that I can see. And it does not work the same way. So something is different about the table, I guess. What should I look for?

[dmellman]

Here is the database (excerpted) with section of table and query that doesn't aggregate. Can you see what it is? Your assistance would be greatly appreciated.

[dmellman]

Hi to all in the forum! I would greatly appreciate your help in understanding why the the query and table in the database I posted here (https://www.accessforums.net/queries...ies-15907.html) won't aggregate the way a simple table that I made does. I had tried to follow the suggestions of the posters in regard to that problem and found that my table and query were not aggregating. Can anyone shed some light on this?

[June7]

The query is aggregating. Field UDL5 has unique values. Including this field in the Group By makes every record unique. Including field SumDefects in Group By also impacts the aggregate calc. Remove these two fields from the query and aggregate calc will be at the UDL6 level - 3 summary records.

[dmellman]

Now I see, June7. Thank you and thank you, also, NTC and rpeare for help. I benefited from each of your suggestions. I think I will be able to finish now.