Grouping & Summing identical fields in a report

[DesertTrip]

I am not sure if this should be in reports or in queries. But, I am suspecting I can fix this on the report level so I will give it a shot here.

I am building a employee productivity report that includes data from query field that is a lookup table/drop menu.

This lookuptable/drop menu offers 5 list choices.

On my worker productivity report, the field labeled "ProjectID" currently shows (for example):

4 records for "Overpayments" totaling 10 hours
3 records for "Automation Project" totaling 6 hours
6 records for "Fraud Budgeting" totaling 8 hours

How/where can I fix this to sum the individual lookup table options as:

1 record of "Overpayments" totaling 10 hours
1 record of "Automation Project" totaling 6 hours
1 record for "Fraud Budgeting" totaling 8 hours

My goal is to show a summary of the type of hours worked. Not an itemized daily list.

I don't speak SQL/VB, but if you can tell me where to go and step by step what to write, I can follow pretty well. In fact, if you can just give me the name of this procedure, I can then have better luck trying to find it in my worthless books.

Thanks in advance;
Leia

 

[DesertTrip]

PS: The hours are from a different field in the same record

 

[nberryman]

Use the Report Wizard. Select the Query and folow the steps. You will be asked if you want to Group By and fields, add the field there and check the Grouping Options button.

Next you will be asked for a Sort Order, check the Summary Options Button and the rest should be straight forward.

Let me know if you have any problems

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[DesertTrip]

Egads - It might be THAT simple? LOL. I'll give it a shot this afternoon at work and let you know. Thanks!

 

[DesertTrip]

Nberryman, I tried using that grouping options button several different ways, but it is still sorting as before. I am getting an entry in my report for every single record per option from the lookup table. I am still getting the itemized/individual records for each. I just don't know how to make that particular type of field do this. I am wondering if Imight not have explained it well. (VERY possible)

Let's say my original table has three fields: [Date], [Project], [Hours]
[Project] is a lookup/drop menu with 3 options: Prep, Packaging, Advertising
My query includes these fields and I want to make a monthly Project Hours Report off of hte data that does not show every single record.

In my report, instead of showing the following for

Total for May 2002:
_________________
Prep 3 hours
Prep 3 hours
Prep 3 hour
Prep 1 hours
Packaging 4 hours
Packaging 2 hours
Packaging 4 hours
Advertising 4 hours
_________________

I want it to show:

Total for May 2002
_________________
Prep 10 hours (4 records)
Packaging 10 hours (3 records)
Advertising 4 hours (1 record)
_________________

My dilema is making one multiple choice field do this in a report. Help?? Can I break down the three possible answers of a single lookup field into a report like this?

 

[nberryman]

Open the report in Design view.

You will see several different sections headers footers and details move the fields into different sections and the preview the report. Repeat until it look as you wish.

Once you have what you want close up the detail section.

Neil Berryman
IT Trainer
neil_berryman@btopenworld.com

 

[DesertTrip]

That was the first thing I tried......but as the many options available are all in one individual field, I couldn't break them out on the report.

I went ahead and posted this request in the SQL forum and it appears that this is a SQL/Query problem....and that it will be a code that solves this simple sounding but difficult problem.

Thank you for your time in trying to help an idiot.

It is much appreciated.