group/total records in a field with matching data? 1

[DesertTrip]

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?

 

[scriverb]

Let's call the table tblProj. Then try this:

Select tblProj.Date, tblProj.Project, Sum(tblProj.Hours) as Hours
FROM tblProj
Group By tblProj.Date, tblProj.Project;

Give that a try. This hasn't been tested as it is air code so we may have to adjust a little.

Bob Scriver

 

[DesertTrip]

Hi again ScriverB! Thanks for taking hte time to try and help me out. Not to sound extremely ignorant (but I am), I noticed several table references in your suggestion.

I have one table that feeds the one query.

The table feeding the query is tblCaseData.
The query is called qryHoursforIEVS
The fields in the query are Date, EmployeeNumber,ProjectID and Hours.

So, I would then write this (possibly) as:

SELECT tblCaseData.Date, tblCaseData.EmployeeNumber, Sum(tblCaseData) as Hours
FROM.ProjectID
Group By tblCaseData.Date,tblCaseData.EmployeeNumber,tblCaseData.ProjectID

If so...that didn't work. I got an error that said the Micorsoft Jet Database engine cannot find the input table or query 'ProjectID'. Make sure it exists and that the name is spelled correctly. It is spelled correctly and it still shows in the originating table AND in the query spelled the same.....let me go try and find out what I did wrong and if you can think of anything else, let me know.

 

[DesertTrip]

Scriver - this is the sql that is currently working (and finding the ProjectID without problem) in my query and that is (unfortunately) giving individual records for each.

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS [Date By Month], tblCaseData.EmployeeNumber, tblCaseData.ProjectID, Sum(tblCaseData.Hours) AS [Sum Of Hours], Count(*) AS [Count Of tblCaseData]
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, tblCaseData.EmployeeNumber, tblCaseData.ProjectID, Year([tblCaseData].[Date])*12+DatePart("m",[tblCaseData].[Date])-1;

Would this help you to visualize where to adapt it to group the ProjectID's that match?

 

[scriverb]

DesertTrip: I think you are getting closer. Sorry, about the double-post earlier. My first one had the wrong table name. Tried to stop it but it got thru.

You first attempt was unsuccessful because you hadn't changed the SQL to reflect your table name. You last post had a few errors in it. Give the following a try and see if this is what you are looking for. Now the way this is setup it should give you what you want but if there are other months records in the same table you will have to modify this to include a criteria line selection for the month that you are interested in. I can help you with that so let me know. Also, the calculated field(column 2) I left in and gave it a name of YearMonth but I don't know what that is all about. If it is necessary leave it in but if not take it out. I also sorted the records ProjectID and EmployeeNumber ascending.

SELECT DISTINCTROW Format([tblCaseData].[Date],"mmmm yyyy&quot AS [Date By Month], Year([tblCaseData].[Date])*12 & DatePart("m",[tblCaseData].[Date])-1 AS YearMonth, tblCaseData.ProjectID, tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS [Sum Of Hours], Count(*) AS [Count Of tblCaseData]
FROM tblCaseData
GROUP BY Format([tblCaseData].[Date],"mmmm yyyy&quot, Year([tblCaseData].[Date])*12 & DatePart("m",[tblCaseData].[Date])-1, tblCaseData.ProjectID, tblCaseData.EmployeeNumber
ORDER BY tblCaseData.ProjectID, tblCaseData.EmployeeNumber;

Bob Scriver

 

[DesertTrip]

Hmmmm. It says there is invalid bracketing of [Count of tblCaseData]

I can do criteria for months. That one I know!

I am just not familiar with Count(*) AS [Count Of tblCaseData] so I didn't even know what to try to fix it. This project is the first where I ventured out beyond the Wizards and into the SQL (out of neccesity).

Suggestions on the bracketing issue?

Also - if we get this to work, is there a PO box I can send you a Pizza Hut gift cetificate? You and yours deserve dinner for this one!!

 

[DesertTrip]

PS: Disregard the stupid year end clause I lifted off my test query.

I tried this:
SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS [Date By Month], tblCaseData.EmployeeNumber, Sum(tblCaseData.Hours) AS [Sum Of Hours], Count(*) AS [Count Of tblCaseData]
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, tblCaseData.EmployeeNumber, tblCaseData.ProjectID;

When I ran the query, it DID total all the hours per worker per month....one record each per Project type (YAY!!!!!). We are ALMOST THERE!!!!

BUT! (oops) Now, there is no project types showing in my query. The user will have to guess what the hours were for. Any idea where the project type went of to?

 

[scriverb]

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS [Date By Month], tblCaseData.ProjectID, tblCaseData.EmployeeNumber, Sum(tblCaseData.Hours) AS [Sum Of Hours], Count(*) AS [Count Of tblCaseData]
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, tblCaseData.EmployeeNumber, tblCaseData.ProjectID;

This should get you there as you left out the tblCaseData.ProjectID, .

Let me know if this rolls things up for you.

Bob Scriver

 

[DesertTrip]

Thanks, scriverB.

This wouldn't let me run the query and it gave me an error message that says, "Invalid bracketing of name '[sum of Hours]'.

I ran help on this and it states that either that phrase can't have brackets around it or the brackets are mismatched. They look okay to me. Any idea on what is causing that?

This project is the most difficult and obnoxious thing I have ever been assigned.

Leia

 

[scriverb]

It works perfectly for me but let's try a little something different. It seems that your version of ACCESS is having trouble with the column names that have spaces in them so let's adjust the names you have given your columns:

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS DateByMonth, tblCaseData.ProjectID, tblCaseData.EmployeeNumber, Sum(tblCaseData.Hours) AS SumOfHours, Count(*) AS CountOf_tblCaseData
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, tblCaseData.ProjectID, tblCaseData.EmployeeNumber;


I have run the names together with Caps for the first letter of each word to make it easily readable. I also put an underscore in the Count column for readability. This should eliminate the square bracket problems and give you a rollup of the data as you requested.

Bob Scriver

 

[DesertTrip]

It works!!!

All those naysayers who told me it couldn't be done were wrong. Yessssss!! Woohoooo!!! My boss will be so pleased. She was quite insistant that I find a way to group/total the hundreds of itemized records for each worker.

So - the offer is still up for that Pizza Hut gift certificate. Unless of course, that falls under soliciting or some kind of rule here, in which case I then retract my offer and will then make an equitable donation to this site on your behalf.

I really appreciate the time and information you share here. Our local college offers no Access or SQL classes and my Access books are usually useless.

Thank you!

Leia

 

[DesertTrip]

It works!!!
Yessssss!! Woohoooo!!! My boss will be so pleased. She was quite adamant that I find a way to group/total the hundreds of itemized records for each worker, even though others told me it couldn't be done.

So - the offer is still up for that Pizza Hut gift certificate. Unless of course, that breaks some kind of rule here, in which case I then retract my offer and will make an equitable donation to this site on your behalf.

Seriously, this is the second time you rescued me and I really appreciate the time and information you share here with others. Our only nearby college offers no Access or SQL classes and my Access books are usually useless.

Thank you!
Leia

 

[DesertTrip]

The site said the first post was time expired and did not post.....so I rewrote and reposted. I guess the site alert was wrong!! LOL! Sorry about that.

 

[scriverb]

Leia, no need for the offer of the gift certificate. I enjoy helping here at this site and sharing with you the knowledge and skills in ACCESS that I have acquired over a good number of years in the business.

Your excitement in getting a workable final product is all the reward that I need. Talk to you in your next question. I will look for you.

Bob Scriver

 

[DesertTrip]

Okay, I can accept that.

BTW: Can that process be used on several fields in a query or is it best done to one...and then run seperate queries? I tried doing this in a larger query and before I waste more time on it, I just want to see if it is at all possible. My first attempts went back to individual itemized records.

Leia

 

[scriverb]

If you mean performing a group by process and counting the like records, it is best to do them seperate. Then you can combine the efforts in a Union Query or by linking the resulting recordsets together with a common "Link" field.

Be more specific with your problem and I can try to put it together for you to demo what I mean. Bob Scriver

 

[DesertTrip]

n the new query I am again using the same fields as above, PLUS, I added two additional fields ("misc tasks" and "misc hours&quot that also need the data to roll up.

The problem is that I was trying to apply the same approach that worked in the smaller query, to roll up both the [Project ID] with it's [hours], and the [Misc Tasks] and it's [Misc Hours], in the SAME query.

Obviously that isn't working.

BUT! I did manage to create the two separate queries that are both working great using the solution you offered. So, from there I will try and link the data of the two working queries together instead of trying to use one larger query.

Union queries? I will check them out in my books tomorrow and see what I find. If that doesn't work, I'll research common "field links". I have the whole day off to play and explore.

Thanks!
Leia

 

[scriverb]

Here is the SQL for a UNION query to combine the data into one recordset. You will notice that I modified the names of a couple of columns to a more generic name. This must be done because each of the seperate parts of the UNION queries are rollup up different pieces of data and a common name is needed.

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS DateByMonth, [tblCaseData]![ProjectID] AS Activity, tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours, Count(*) AS Count
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, [tblCaseData]![ProjectID], tblCaseData.EmployeeNumber

UNION

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS DateByMonth, [tblCaseData]![Misc Tasks] AS Activity, tblCaseData.EmployeeNumber, Sum([tblCaseData]![Misc Hours]) AS Hours, Count(*) AS Count
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, [tblCaseData]![Misc Tasks], tblCaseData.EmployeeNumber;


Let me know if this is close to what you wanted to do with this data. Bob Scriver

 

[DesertTrip]

It works! WOW!

I had been pouring through books and although gaining some understanding of Union theories, I was getting nowhere. I just logged on line to take a break and your solution solved this in seconds flat. I changed a few titles, used my book for instructions on building one from scratch and then dropped in the code you provided. Too cool! I learned quite a few things today. Thank you!

Question: The Union created a field now called "Activity" which is really great. But, it is pulling from the source column of the two lookup tables that provide the activity types. This is resulting in numbers instead of titles, which doesn't happen in my regular queries.

I have no idea how to change the source column for these from column 1 to column 2. Can it be done in a union query?

If not, that is fine too. I will use it "as is" in my final report and just provide keys to the managers! LOL

Thanks once again!
Leia

 

[DesertTrip]

LAST QUESTIONS ON THIS (I hope..)

I am a little confused on modifying this with a criteria and expression since it is straight SQL. I need to add the two last items (which I falsely assumed I could just add in Design Mode once the hard stuff was figured out): One is [Enter Month of Report] so the managers can choose the report period they desire.

The other is using the field [Mileage] to calculate the third and last possible hours total. In my other (basic and functional) queries, it is written as: tblCaseData.Mileage, [Mileage]/40 AS Expr1

I am assuming that in the Union, I will be writing both of these additions on both halves of the Union's code. Correct? Using just one half of the union as an example, here is what I have tried (unsuccessfully)

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot
AS DateByMonth, [tblCaseData]![ProjectID] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours,
Count(*) AS Count,[tblCaseData].[Mileage], [Mileage]/40 AS Expr1,
FROM tblCaseData
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot,
[tblCaseData]![ProjectID], tblCaseData.EmployeeNumber
WHERE (((tblCaseData.EmployeeNumber)=[Enter Worker Number]));

First, I got errors on the WHERE statement saying "characters found after end of SQL statement".

When I deleted both if the Where statements, I then got errors for the Mileage expression saying I tried to excute a query that "doesn't include the specified expression 'Mileage' as part of the aggregate function".

I am guessing I am trying to stick them into the wrong places. Ideas?