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?

 

[DesertTrip]

Bob,

I tried variations of this, added to the ends of each half of the Union query.

HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]))

Still getting those nasty alerts saying I am adding characters after the end of a SQL statement. Am I closer, though?

 

[scriverb]

Okay let's work though one issue at a time. The table tblCaseData has the fields [ProjectID] and [Misc Tasks] which are both being placed in the new column called Activity. I take it from your first post that this is just a code that relates to column one of some lookup table or tables. If this is correct give me the name or names of the lookup tables so that we can include the names of the activities. I will then include the appropriate description in the Activity column for each.

Then we can address the selection criteria process. Bob Scriver

 

[DesertTrip]

Yes, they are showing the first column results of two diferent lookup tables. The lookup tables are called "lkpLeaveCode" (need the 3rd column) and "lkpSpcProjects" (2nd column)

Leia

 

[scriverb]

Now I guessed at which table matched with which activity. The first select is rolling up ProjectID info so I added an inner join with lkpSpcProjects. The second Select is inner joined with lkpLeaveCode. Now since you only referred to their columns to be used I had to use ActivityDesc for both. That needs to be adjusted to the correct fields in each table. Also, in the inner join statement of each the field that is linked in each table on the lookup side must be adjusted to the field name corresponding in each table. I also added the Having statement which allows for selection of Month Year and Employee ID through a single prompt by ACCESS upon running the query. If I have made a mess of this for you please get back with a prompt so I may further explain the process. I will give you more information tomorrow as to why you were having trouble with your attempts. You were very close with just a couple of adjustments.

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS DateByMonth, [lkpSpcProjects]![ActivityDesc] AS Activity, tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours, Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage, Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpSpcProjects ON tblCaseData.ProjectID = lkpSpcProjects.ProjectID
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, [lkpSpcProjects]![ActivityDesc], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]))

UNION

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot AS DateByMonth, [lkpLeaveCode]![ActivityDesc] AS Activity, tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours, Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage, Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpLeaveCode ON tblCaseData.[Misc Tasks] = lkpLeaveCode.[Misc Tasks]
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot, [lkpLeaveCode]![ActivityDesc], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]));

Good luck. Bob Scriver

 

[DesertTrip]

I'm going to be playing with this for hours. I think I understood what you were saying and using just one half of the query as an example, I made the following changes (in blue).

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot
AS DateByMonth, [lkpSpcProjects]![ProjectTitle] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours,
Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage,
Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpSpcProjects.ProjectTitle ON tblCaseData.ProjectID
= lkpSpcProjects.ProjectID
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot,
[lkpSpcProjects]![ActivityDesc], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month
Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]))

Note I added a period before the title name in the inner join. I did the same type of updates to the bottom half. This has resulted in a "Syntax Error in Join Operation".

I tried also writing the inner join using brackets around the column title from the table as: INNER JOIN lkpSpcProjects.[ProjectTitle], but I got rid of those quickly.

When you get in tomorrow, let me know what I am doing wrong, again. I'll tinker with this and see what else I can learn not to do.

I am really embarrassed by how much time this is taking from you. I hope you know how very much I appreciate your patience and time and though it may not look like it, I really am learning alot in trying to nail this one statement.

Leia

 

[DesertTrip]

I wish this forum had EDIT so I could recycle the last post. After several hours (two books, searching Northwind queries, etc) I came up with this. I corrected my titles, added two periods (in the Injoin statement to add the column titles) and added brackets to ProjectID twice:

Based on what I read, this should be working...but I am still getting the syntax in join error. Can you see where my punctuation or argument name might be wrong?

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot
AS DateByMonth, [lkpSpcProjects]![ProjectTitle] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours,
Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage,
Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpSpcProjects.[ProjectTitle] ON tblCaseData.[ProjectID] = lkpSpcProjects.[ProjectID]
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot,
[lkpSpcProjects]![ProjectTitle], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month
Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]))

UNION

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot
AS DateByMonth, [lkpLeaveCode]![Def] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![HoursMisc]) AS Hours,
Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage,
Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpLeaveCode.[Def] ON
tblCaseData.[MiscHour_ID] = lkpLeaveCode.[MiscHour_ID]
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot,
[lkpLeaveCode]![Def], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month
Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]));

 

[scriverb]

Leia, think nothing of the time spent as long as you understand the process after we are done. It is difficult because you have to make some assumptions from both ends of the conversion. But, you have done well. Just a few minor adjustments:

INNER JOIN syntax:
FROM table1 INNER JOIN table2 ON table1.joinfield = table2.joinfield

Your statement:
FROM tblCaseData INNER JOIN lkpSpcProjects.ProjectTitle ON tblCaseData.ProjectID
= lkpSpcProjects.ProjectID

Corrected Statement removing red:
FROM tblCaseData INNER JOIN lkpSpcProjects ON tblCaseData.ProjectID = lkpSpcProjects.ProjectID

The lookup table lkpSpcProjects I assume has basicially two fields. 1. ProjectID(is the shorthand representation of the project description i.e. Pkg) 2. ProjectDesc(is the longhand description of the project description i.e. Packaging Systems) The above INNER JOIN statement is linking the two shorthand representations together and the Select returns the longhand description. The tables are setup this way so that the data that is stored in the many thousands of records in your tblCaseData.ProjectID takes up less disk space. So the SQL should really look like this:

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot
AS DateByMonth, [lkpSpcProjects]![ProjectTitle] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![Hours]) AS Hours,
Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage,
Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpSpcProjects ON tblCaseData.[ProjectID] = lkpSpcProjects.[ProjectID]
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot,
[lkpSpcProjects]![ProjectTitle], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month
Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]))

UNION

SELECT DISTINCTROW Format$([tblCaseData].[Date],"mmmm yyyy&quot
AS DateByMonth, [lkpLeaveCode]![Def] AS Activity,
tblCaseData.EmployeeNumber, Sum([tblCaseData]![HoursMisc]) AS Hours,
Count(*) AS Count, Sum([tblCaseData]![Mileage]) AS Mileage,
Sum([tblCaseData]![Mileage])/40 AS MileageCalc
FROM tblCaseData INNER JOIN lkpLeaveCode ON
tblCaseData.[MiscHour_ID] = lkpLeaveCode.[MiscHour_ID]
GROUP BY Format$([tblCaseData].[Date],"mmmm yyyy&quot,
[lkpLeaveCode]![Def], tblCaseData.EmployeeNumber
HAVING (((Format$([tblCaseData].[Date],"mmmm yyyy&quot)=[Enter Month
Year]) AND ((tblCaseData.EmployeeNumber)=[Enter Worker Number]));

I hope this helps you to understand. If I have made a wrong assumption about your lookup tables please explain. It may be that you just have a table with an autonumber and the longhand description. If this is so then exactly what is being stored in the table tblCaseData. The number of the lookup table record? Just let me know. Bob Scriver

 

[DesertTrip]

Great, Bob! VERY close!!! This was working perfectly until it asked for a parameter value for lkpLeavecode.MiscHour_ID.

I made no entries at the prompt and pressed enter which then brought up all my data correctly, except for the data from the second half: the MiscHour_ID records/data.

The ProjectID field is "bound" to column 1 of lkpSpecProjects.
That is formatted as a text field with manually assigned code numbers.

In the table CaseData, the MiscHour_ID field is "bound" to column 1 of lkpLeaveCode. That is formatted to autonumber.

I thought I might have been screwing things up by having one autonumber and one text. But when I just tested using the same format on both, it still didn't work. (I've been doing a lot experimenting on my test db all day)

Leia

 

[scriverb]

This is because of the second selects FROM. . . INNER JOIN statement:

FROM tblCaseData INNER JOIN lkpLeaveCode ON
tblCaseData.[MiscHour_ID] = lkpLeaveCode.[MiscHour_ID]

Your table tblCaseData has a field called [MiscHour_ID] which should match to a field in your lookup table lkpLeaveCode. I used the same name there because I didn't know what it was. What is the field name of the first column of the table lkpLeaveCode(the AutoNumber field)? Replace the lkpLeaveCode.[MiscHour_ID] with lpkLeaveCode.[AutoNumberFieldName]. That should do it.

You see this inner join is actually doing a lookup using the MiscHour_ID field data from tblCaseData to lookup the matching record in the lookup table lkpLeaveCode. Clear???

Make that adjustment and you should be complete.
Bob Scriver

 

[DesertTrip]

Yessssssss!!! It works! It works!!!

I am amazed by that solution. I don't understand (yet, but I will tear through it with your printed messages again tomorrow until I do!) how it is that the upper half goes

= lookuptable #1.[field the lookup table feeds into] and the second half of the union is
= lookuptable #2.[first column of lookup table]

Mindboggling. I would have never figured that out.

Thank you again for your patience, time and the sharing of your knowledge. The things you have shown me and explained are all printed and saved now both in my handbooks and on disc for future reference.

We can now lay this thread to rest. (Do you think it broke any records for length? LOL)

Leia

 

[scriverb]

Great!!! Fantastic!!!

It was fun and I am glad that you learned something. You seem so eager to dig in and understand rather than just getting an answer that works.
Bob Scriver

 

[DesertTrip]

Oh yeah! The more I learn, the less I have to ask! (I hate bugging people and asking for direction)

Before this project, I only used Wizards and never even heard of a Union report. I can now make a simple one all by myself. (*pats herself on the back*) That INNER JOIN will take more practice, but now I have seen what it can do and how it gets there....I'm ready to try more of those too. Very handy.

Now...until the next time! hehee
Leia

 

[scriverb]

Just a little note. Copy the first Select query from the union query and paste it into a new query's SQL. Now click on the design button. You will see be able to see what the INNER JOIN means by the join connection between the two tables. It is a way of working backwards from the final product which is the SQL code and seeing visually the tables their relationships and the columns of data and how they are created in the wizard.

Actually when I make a UNION query I start with a Wizard defined Select query and copy the SQL and paste it into a UNION query. Then I add the UNION line. Then I create another Select query with the wizard and copy that code into the UNION QUERY. Really quite easy if you think about it. You just have to make sure that the columns are named the same.

Good luck with this. Bob Scriver