DesertTrip
Technical User
I tried this in Reports and I'm still looking for an answer. Someone at work suggested I try the SQL/query forum.
So - Is there a SQL code that can identify and group/total all individual records that have matching data in one of the fields? Maybe a simple expression?
In my Query, there is one field that has multiple choice possibilities.
As an example (only) let's say my Query has three fields: [Date], [Project], [Hours]
[Project] is a lookup/drop menu field with 3 optional entries: Prep, Packaging, Advertising
This query has a total of 14 records. I want to make a monthly Project Hours Report off of this data, but I don't want itemized record data. I want grouped totals for each of the three different options possible within that field. (Did I word that okay?)
Instead of showing the following when I build my report off this Query,
Total for May 2002:
Project Type Hours
Prep 3 hours
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Packaging 2 hours
Packaging 2 hours
Packaging 2 hours
Packaging 3 hours
Packaging 1 hours
Advertising 4 hours
I want the report to show only one record each, that totals all the individual records:
Total for May 2002:
Project Type Hours
Prep 10 hours
Packaging 10 hours
Advertising 4 hours
Can this be done? Is it done in the query level? I can't find anything in my books or here in the archives when I searched but that might be because I have no idea what to call this procedure. ANY suggestions would be greatly appreciated. Help?
So - Is there a SQL code that can identify and group/total all individual records that have matching data in one of the fields? Maybe a simple expression?
In my Query, there is one field that has multiple choice possibilities.
As an example (only) let's say my Query has three fields: [Date], [Project], [Hours]
[Project] is a lookup/drop menu field with 3 optional entries: Prep, Packaging, Advertising
This query has a total of 14 records. I want to make a monthly Project Hours Report off of this data, but I don't want itemized record data. I want grouped totals for each of the three different options possible within that field. (Did I word that okay?)
Instead of showing the following when I build my report off this Query,
Total for May 2002:
Project Type Hours
Prep 3 hours
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Prep 1 hour
Packaging 2 hours
Packaging 2 hours
Packaging 2 hours
Packaging 3 hours
Packaging 1 hours
Advertising 4 hours
I want the report to show only one record each, that totals all the individual records:
Total for May 2002:
Project Type Hours
Prep 10 hours
Packaging 10 hours
Advertising 4 hours
Can this be done? Is it done in the query level? I can't find anything in my books or here in the archives when I searched but that might be because I have no idea what to call this procedure. ANY suggestions would be greatly appreciated. Help?