How to summarize Access query data?

[DLK610]

I have a table and a resulting query with 1,400 records. I would like a
summary page for a given category. For example, there are 20 people and each
has a number of work orders they are responsible for. Each work orders has a
discrete identification number. I want two numbers per person: (1) the
number of orders they have (a count) and (2) the sum of all the "Remaining
Hours" each person has associated with their associated orders. (The
existing query takes the estimated hours and subtracts the actual hours with
the result being the "Remaining Hours.")

This is my first attempt at building an Access program. Any help is
appreciated.

 

[Duane Hookom]

Do you have some table structures you could share with us? Can we assume you
have a field in a related table that identifies the estimated hours by
employee and order?

You should be able to create a totals query that counts the number of work
orders per employee. Add this totals query to your report's record source.

 

[DLK610]

I have one table. One of the fields is the employee number; another field is
the order number; and another field is the remaining estimated hours per
order. I believe you are correct in that I should be able to create a totals
query - but I do not know how. Neither do I know how to add this totals
query to my report's record source.

David Kendall
Microsoft Access DKS (Don't Know Squat)

 

[Duane Hookom]

Can you provide some sample records and how you want to display these in a
report?

 

[DLK610]

How do I get them to you? I would rather not post them.

 

[Duane Hookom]

You can type or copy and paste them into a reply. If you don't want to expose
employee numbers, replace them with made up values.

 

[DLK610]

Priority Employee Order Est Work Actual work
4 531 9306515 100 0
4 531 9299677 40 0
4 531 9399001 2 0
4 531 9306515 100 0
4 531 9306951 20 0
4 531 9306951 10 0
4 531 9282050 200 0
4 531 9275971 0 177
4 531 9275971 300 400.5
4 511 9313639 160 0
4 531 9281967 500 40
4 531 9281968 40 547
4 531 9281968 40 311
4 531 9281968 160 132
4 531 9296292 10 126
4 531 9281968 4 3
4 531 9399002 5 0
4 531 9282053 200 36
4 531 9282053 30 40
4 531 9282053 180 180
4 531 9282053 240 190
4 531 9399003 3 0
4 531 9282053 240 160
4 531 9306952 20 0
4 531 9296293 10 112.5
4 531 9281968 160 152
4 531 9325631 10 0
4 531 9306953 10 0
4 511 9290107 16 0
4 511 9313640 160 128
4 511 9269263 0 0
4 511 9269263 0 0
4 511 9269263 20 5
4 511 9288955 5 0
4 511 9288955 5 0
4 511 9269263 0 0
4 511 9269263 20 5
4 511 9269263 40 0
4 511 9269263 5 0
4 511 9286622 10 0
4 511 9288954 5 0
4 511 9286622 10 0
4 511 9286622 6 0
4 511 9286623 6 0
4 531 9365303 10 20
4 531 9366423 10 0
4 511 9288954 5 0
4 531 9312941 10 0
4 510 9395685 20 0
4 531 9306952 10 0
4 531 9306953 10 0
4 531 9309118 20 0
4 511 9286623 6 0
4 510 9399007 5 0
4 511 9124860 10 0
4 511 9124860 10 0
4 511 9269263 0 0
4 531 9311234 5 0
4 510 9395685 20 0
4 531 9314249 40 0
4 511 9215259 40 16
4 511 9238618 8 0
4 511 9263223 8 0
4 531 9323008 80 0
4 531 9323008 20 0
4 511 9269262 40 0
4 511 9269262 5 0
4 511 9215045 30 0
4 530 9191004 40 0
4 514 9313764 0 0
4 514 9325283 0 0
4 514 9325283 0 0
4 514 9325286 0 0
4 514 9325286 0 0
4 514 9389940 0 0
4 514 9304150 0 0
4 514 9304150 0 0
4 514 9304042 0 0
4 513 9315772 0 0
4 530 9191004 16 0
4 514 9304150 0 0
4 530 9191005 16 0
4 530 9191005 40 0
4 530 9191006 16 0
4 530 9191006 40 0
4 530 9249717 0 0
4 530 9260637 80 0
4 530 9260638 80 0
4 514 9285632 0 0
4 530 9312949 20 0
4 514 9301743 0 0
4 514 9325286 0 0
4 514 9268514 0 0
4 514 9268514 0 0
4 514 9396084 0 0
4 514 9394491 0 0
4 514 9393399 0 0
4 514 9391696 0 0
4 514 9389940 0 0
4 514 9367650 0 0
4 514 9367649 0 0
4 514 9313765 0 0
4 514 9301743 0 0
4 514 9304150 0 0
4 514 9325283 0 0
4 514 9313765 4 0
4 514 9313765 20 70
4 514 9304042 0 0
4 514 9304042 0 0
4 514 9304042 0 0
4 514 9304042 0 0
4 514 9304150 0 0
4 514 9304150 0 0
4 513 9315772 0 0
4 514 9325286 2 0
4 511 9314491 20 0
4 511 9396085 15 0
4 511 9396085 15 0
4 511 9391705 6 0
4 511 9391705 6 0
4 511 9365902 40 0
4 531 9215720 8 0
4 531 9220499 200 0
4 531 9225529 60 0
4 511 9314491 5 0
4 514 9268514 0 10
4 511 9314491 6 0
4 513 9268517 0 56
4 511 9314077 10 0
4 511 9314077 10 0
4 511 9313644 160 48
4 531 9252831 20 0
4 531 9255722 10 0
4 531 9268518 80 148
4 531 9268518 80 121
4 531 9268518 40 10
4 511 9313643 160 10
4 511 9314491 6 0
4 513 9315772 40 154
4 513 9315772 0 0
4 511 9263223 8 0
4 513 9315772 0 0
4 530 9312950 20 0
4 530 9314492 40 16
4 513 9268517 0 0
4 513 9268517 0 0
4 513 9268517 0 0
4 513 9268517 0 0
4 513 9220496 0 0
4 513 9325783 20 0
4 530 9391695 80 0
4 530 9366422 20 0
4 513 9315772 40 180
4 530 9388050 20 0
4 530 9388456 40 0
4 513 9268517 60 69
4 513 9268517 120 223
4 513 9268517 400 305
4 513 9268517 400 469
4 530 9391045 40 0
4 511 9313642 160 47
4 513 9325783 20 10
4 532 9307496 60 0
4 532 9311461 10 0
4 532 9301651 2 0
4 532 9301651 2 0
4 532 9301652 6 0
4 532 9301652 2 0
4 532 9301652 2 0
4 532 9302114 40 0
4 532 9304821 40 0
4 532 9304835 10 0
4 532 9304835 2 0
4 532 9307492 40 6
4 532 9307493 10 0
4 532 9301650 2 0
4 532 9307495 24 6
4 532 9301650 2 0
4 532 9307497 60 0
4 532 9307498 60 0
4 532 9307499 40 0
4 532 9308279 10 0
4 532 9309294 10 0
4 532 9311062 10 0
4 532 9311391 10 0
4 532 9311399 10 0
4 532 9311400 10 0
4 532 9311405 10 0
4 532 9311450 10 0
4 532 9301637 6 0
4 532 9307494 40 0
4 532 9301646 6 0
4 532 9301637 2 0
4 532 9301637 2 0
4 532 9301638 6 0
4 532 9301638 2 0
4 532 9301638 2 0
4 532 9301639 6 0
4 532 9301639 2 0
4 532 9301639 2 0
4 532 9301640 6 0
4 532 9301640 2 0
4 532 9301640 2 0
4 532 9301642 6 0
4 532 9301651 6 0
4 532 9301642 2 0
4 532 9311462 10 0
4 532 9301646 2 0
4 532 9301646 2 0
4 532 9301647 6 0
4 532 9301647 2 0
4 532 9301647 2 0
4 532 9301648 6 0
4 532 9301648 2 0
4 532 9301648 2 0
4 532 9301649 6 0
4 532 9301649 2 0
4 532 9301649 2 0
4 532 9301650 6 0
4 532 9301642 2 0
4 576 9275889 0 0
4 532 9311452 10 0
4 576 9275879 0 0
4 576 9275880 0 0
4 576 9275881 0 0
4 576 9275882 0 0
4 576 9275883 0 0
4 576 9275884 0 0
4 576 9275885 0 55
4 576 9275886 0 0
4 576 9275887 0 0
4 576 9275888 0 10
4 576 9275888 10 0
4 574 9282003 0 0
4 576 9275888 52 0
4 574 9282002 0 0
4 576 9275970 1200 663.5
4 576 9275970 1200 817
4 576 9275970 60 42
4 576 9275970 80 70
4 576 9275972 0 2
4 576 9275970 0 0
4 576 9275970 0 0
4 578 9253572 5 0
4 506 9282052 0 0
4 506 9198085 40 0
4 506 9154982 20 0
4 506 9154982 20 5
4 576 9275888 52.5 48.5
4 510 9288899 4 0
4 532 9311463 10 0
4 532 9313472 80 198.5
4 532 9313472 80 20
4 532 9313472 10 4
4 532 9315012 10 0
4 532 9315013 10 0
4 532 9315025 10 0
4 532 9315049 10 0
4 532 9315134 10 0
4 532 9315137 10 0
4 532 9315878 10 0
4 532 9315878 4 0
4 574 9282051 0 0
4 510 9288899 4 0
4 532 9308276 10 0
4 510 9249047 4 0
4 510 9249047 4 0
4 510 9191265 40 0
4 509 9391694 0 0
4 509 9391693 0 0
4 509 9391692 0 0
4 509 9391691 0 0
4 507 9314643 12 0
4 532 9296207 0 0
4 532 9296208 0 0
4 570 9195301 0 0
4 574 9281969 0 0
4 532 9315878 4 0
4 510 9313645 300 166.5
4 532 9296208 20 0
4 532 9296209 8 0
4 532 9296209 8 0
4 532 9296209 8 4
4 532 9253862 2 0
4 532 9296209 8 0
4 532 9301587 2 0
4 510 9306674 15 0
4 532 9296208 20 12
4 532 9098458 10 0
4 532 9301636 2 0
4 531 9281968 0 0
4 532 9301587 6 0
4 532 9308280 10 0
4 532 9301587 2 0
4 532 9256533 10 0
4 532 9296209 8 0
4 532 9296207 10 39
4 532 9201186 4 0
4 532 9201186 10 0
4 532 9201186 4 0
4 532 9198248 10 0
4 510 9306674 0 0
4 510 9306674 0 0
4 510 9313641 300 196
4 532 9179569 10 0
4 532 9296207 10 56
4 532 9179569 10 0
4 532 9296207 10 6
4 532 9296207 10 0
4 532 9296207 10 0
4 532 9198184 10 1
4 532 9198086 40 0
4 532 9198084 40 0
4 532 9098458 4 0
4 510 9306674 15 0
4 532 9301589 6 0
4 532 9301630 6 0
4 532 9301633 2 0
4 532 9301630 2 0
4 532 9301631 6 0
4 532 9301631 2 0
4 532 9301633 6 0
4 532 9301631 2 0
4 532 9301632 2 0
4 532 9301634 6 0
4 532 9301634 2 0
4 532 9301632 6 0
4 532 9301630 2 0
4 532 9301589 2 0
4 532 9301633 2 0
4 532 9301589 2 0
4 532 9301635 6 0
4 532 9301635 2 0
4 532 9301632 2 0
4 532 9301635 2 0
4 532 9301636 6 0
4 532 9301636 2 0
4 532 9301634 2 0
3 506 9297881 0 0
3 506 9297881 0 0
3 506 9324098 0 0
3 511 9264770 8 0
3 510 9270679 40 10
3 514 9323439 0 0
3 514 9300183 20 0
3 514 9201056 0 0
3 510 9325393 5 0
3 514 9200827 0 0
3 514 9300183 0 0
3 514 9366291 0 0
3 511 9246165 8 0
3 514 9375442 0 0
3 511 9238204 4 0
3 511 9264689 8 0
3 514 9391078 0 0
3 514 9304838 40 0
3 510 9210847 10 0
3 510 9210847 30 0
3 510 9210846 10 0
3 510 9210846 40 0
3 510 9227716 2 0
3 510 9227716 2 0
3 514 9304838 40 8
3 510 9230452 2 0
3 514 9304838 10 7
3 514 9303098 0 0
3 510 9268360 24 0
3 510 9236630 80 30
3 510 9324304 20 0
3 514 9309596 0 0
3 510 9322037 40 96
3 510 9181170 8 0
3 510 9322037 60 108.5
3 510 9265117 20 0
3 510 9161920 5 0
3 510 9161920 20 0
3 510 9161920 20 0
3 510 9266778 4 0
3 514 9304838 10 8
3 510 9322037 40 5
3 510 9322037 20 95.7
3 514 9200193 0 0
3 510 9326016 4 0
3 514 9200193 0 0
3 506 9324098 0 0
3 506 9297881 45 20
3 506 9297881 165 31
3 510 9365209 20 0
3 506 9263211 0 0
3 506 9210761 15 0
3 510 9363674 10 0
3 514 9200198 0 0
3 514 9200198 0 0
3 510 9365209 10 0
3 510 9326016 4 25
3 510 9365209 20 0
3 514 9200198 0 0
3 514 9200198 0 0
3 514 9200198 0 0
3 514 9200198 0 0
3 514 9200198 0 0
3 514 9200827 0 0
3 514 9200827 0 0
3 514 9200827 0 0
3 514 9200827 0 0
3 514 9201056 0 0
3 514 9201056 0 0
3 514 9200198 0 0
3 514 9200189 0 0
3 514 9391570 0 0
3 514 9391573 0 0
3 514 9394482 0 0
3 511 9202116 20 0
3 514 9394493 0 0
3 514 9394620 0 0
3 514 9396828 0 0
3 514 9173651 0 0
3 514 9173651 0 0
3 514 9173651 0 0
3 510 9365209 10 0
3 514 9200189 0 0
3 514 9391369 0 0
3 514 9299672 0 0
3 514 9200193 0 0
3 514 9173651 0 0
3 514 9200193 0 0
3 511 9099930 12 0
3 511 9099930 12 0
3 510 9325393 5 0
3 510 9325867 10 0
3 510 9325867 10 0
3 510 9393813 5 0
3 510 9393813 5 0
3 514 9173651 0 0
3 511 9259400 16 0
3 510 9296201 12 0
3 511 9395139 0 0
3 511 9398207 0 0
3 511 9398466 20 0
3 511 9399000 6 0
3 513 9193139 10 0
3 513 9257804 15 0
3 513 9257804 15 0
3 511 9294162 10 0
3 511 9294162 10 0
3 511 9291522 6 0
3 514 9189160 5 0
3 511 9291522 12 0
3 511 9393405 20 0
3 510 9296201 12 0
3 510 9290005 30 0
3 511 9290108 16 0
3 513 9274483 0 0
3 510 9290005 40 0
3 513 9314978 20 0
3 513 9314978 20 0
3 513 9321959 0 0
3 513 9323012 40 0
3 513 9323012 40 0
3 510 9307393 40 0
3 510 9325867 10 0
3 511 9324793 10 0
3 511 9313396 10 0
3 511 9311236 10 0
3 511 9311236 10 2
3 511 9309730 16 0
3 511 9309619 10 0
3 511 9314981 5 0
3 511 9309598 20 0
3 511 9314981 5 0
3 511 9309598 20 0
3 511 9321993 10 19
3 511 9324308 30 0
3 511 9394490 5 0
3 511 9324793 10 0
3 511 9394490 5 0
3 511 9301305 8 0
3 511 9363780 1 0
3 511 9363781 1 0
3 511 9363782 1 0
3 511 9365305 30 50
3 511 9301305 8 0
3 511 9389114 15 0
3 511 9389114 15 0
3 510 9302326 10 0
3 511 9392800 16 0
3 511 9393405 20 0
3 510 9309310 40 0
3 511 9324308 200 45
3 514 9288747 400 52
3 510 9309169 40 0
3 511 9275045 12 22
3 510 9289999 10 0
3 514 9288747 0 0
3 514 9187097 12 0
3 514 9222845 0 0
3 514 9222846 0 0
3 514 9227843 0 0
3 514 9262374 60 10
3 514 9266756 0 0
3 510 9289999 40 0
3 510 9289999 5 0
3 510 9289999 40 0
3 510 9290001 40 6
3 510 9289999 40 60
3 510 9289999 40 40
3 510 9289999 0 40
3 510 9289284 40 0
3 510 9289284 40 0
3 510 9289235 40 0
3 510 9289235 40 0
3 510 9288969 8 0
3 510 9288969 16 0
3 510 9322037 160 192
3 510 9322037 60 104.5
3 510 9285327 4 0
3 514 9282298 0 0
3 513 9274483 0 0
3 514 9299527 0 0
3 513 9363687 20 0
3 513 9363687 20 0
3 513 9363787 20 0
3 513 9363787 20 0
3 513 9365815 150 0
3 513 9365815 150 0
3 513 9396829 80 0
3 513 9396829 80 0
3 510 9309311 40 0
3 510 9309313 40 40
3 511 9275045 12 0
3 510 9311239 20 0
3 510 9290003 0 0
3 511 9275072 4 0
3 511 9275072 4 0
3 510 9290003 0 0
3 513 9274483 0 0
3 513 9274483 0 0
3 513 9274483 0 0
3 513 9274483 0 0
3 513 9274483 0 0
3 510 9290003 0 0
3 511 9275045 8 2
3 510 9290003 0 0
3 510 9290003 0 0
3 510 9309313 40 50
3 531 9397443 0 0
3 531 9391283 20 0
3 532 9236531 30 0
3 532 9125701 10 0
3 532 9125701 10 0
3 532 9125701 40 0
3 531 9399188 8 0
3 532 9241457 10 0
3 531 9398113 5 0
3 532 9247080 30 0
3 531 9397108 20 0
3 531 9396677 10 0
3 531 9396325 8 4
3 531 9394466 5 0
3 531 9393626 10 0
3 531 9393624 20 5
3 532 9311392 10 2
3 531 9399187 8 0
3 532 9267436 2 0
3 530 9363678 4 0
3 532 9309168 40 40
3 532 9303491 10 3
3 532 9301153 2 0
3 532 9301153 2 0
3 532 9301153 6 0
3 532 9241457 10 0
3 532 9285631 10 0
3 531 9391283 20 0
3 532 9267436 4 0
3 532 9267436 4 0
3 532 9267436 6 0
3 532 9267274 2 0
3 532 9267274 2 0
3 532 9267274 6 0
3 532 9256699 20 8
3 532 9285631 5 0
3 530 9395030 20 0
3 531 9392804 8 0
3 530 9310689 0 0
3 530 9310689 0 0
3 530 9310689 0 0
3 530 9310689 0 0
3 530 9310689 0 0
3 531 9099814 16 0
3 530 9396019 10 0
3 531 9147072 20 8
3 530 9394467 4 0
3 530 9393462 1 0
3 530 9393461 1 0
3 530 9390345 10 0
3 530 9389155 80 0
3 530 9364922 0 0
3 530 9363678 4 0
3 530 9310689 0 0
3 531 9312263 10 0
3 531 9387212 10 5
3 531 9386649 20 0
3 514 9200827 0 0
3 531 9363784 8 0
3 514 9222845 0 0
3 531 9323875 20 0
3 531 9093631 4 0
3 531 9323610 60 0
3 532 9324051 1 0
3 531 9297650 20 10
3 531 9297650 100 83
3 531 9244796 20 0
3 531 9244795 40 0
3 531 9238209 60 62
3 531 9189810 10 29
3 531 9152614 8 0
3 531 9323610 80 0
3 532 9399148 10 4
3 532 9398170 1 0
3 532 9399162 1 0
3 532 9399161 1 0
3 532 9399160 1 0
3 532 9399159 1 0
3 532 9399158 1 0
3 532 9399164 1 0
3 532 9399148 10 4
3 532 9399165 1 0
3 532 9399148 60 0
3 532 9398994 1 0
3 532 9398176 1 0
3 532 9398175 1 0
3 532 9398174 1 0
3 532 9398173 1 0
3 532 9309168 220 221
3 532 9399148 40 0
3 578 9237711 20 95
3 578 9367641 10 5
3 578 9367640 10 0
3 578 9325767 2 180
3 578 9315787 60 0
3 578 9313124 8 0
3 578 9268297 40 0
3 532 9399163 1 0
3 578 9264428 10 10
3 532 9398009 1 0
3 578 9143076 10 0
3 577 9202961 0 0
3 577 9202961 0 0
3 576 9237391 0 0
3 574 9397444 0 0
3 532 9399167 1 0
3 532 9399166 1 0
3 578 9268296 40 0
3 532 9363779 1 0
3 532 9398172 1 0
3 532 9383902 0 0
3 532 9383902 0 0
3 532 9383901 0 0
3 532 9383901 0 0
3 532 9375439 0 0
3 532 9391071 20 0
3 532 9365210 60 8
3 532 9393295 4 0
3 532 9363778 1 0
3 532 9324419 1 0
3 532 9324418 1 0
3 532 9324416 1 0
3 532 9324415 1 0
3 532 9324414 1 0
3 532 9324413 1 0
3 532 9375439 0 0
3 532 9397922 1 0
3 532 9398008 1 0
3 532 9397929 4 0
3 532 9397928 4 0
3 532 9397927 0 0
3 532 9397926 1 0
3 532 9397925 1 0
3 532 9389322 40 0
3 532 9397923 1 0
3 531 9323876 0 0

Here is small sample. There is actually a fifth column that is "Remaining
Work" (Est - Act), but to find a formula, this will work. I would like a
one-page summary by Employee. Thanks.

 

[Duane Hookom]

The simplest method might be to first create a totals query by Employee and
Order:
== qtotEmpOrder ====
SELECT Employee, Order, Sum([Est Work]-[Actual Work]) AS RemainingHrs
FROM tblDLK610
GROUP BY Employee, Order;
=================

Then create another totals/group by query with SQL of:
==================
SELECT qtotEmpOrder.Employee, Count(qtotEmpOrder.Order) AS Orders,
Sum(qtotEmpOrder.RemainingHrs) AS RemaingHours
FROM qtotEmpOrder
GROUP BY qtotEmpOrder.Employee;
====================