Using a query to group (1 Viewer)

dmorgan20 · Apr 16, 2018

I am trying to group together a list of manager. The query works, its showing the managers name 3 times (3 people also on the team). I am clearly grouping incorrectly and would appreciate some help.

My Code:

Code:

SELECT DISTINCT [_tbl_Structure].Supervisor, Format(([field44]+[field30])/([field31]-([field34]+[field35]+[field38]+[field39]+[field42]+[field40]+[field41])),"Percent") AS Availability, Sum(tbl_Genesys_Daily.Field7) AS Calls
FROM _tbl_Structure INNER JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5
GROUP BY [_tbl_Structure].Supervisor, Format(([field44]+[field30])/([field31]-([field34]+[field35]+[field38]+[field39]+[field42]+[field40]+[field41])),"Percent"), [_tbl_Structure].CSM;

I know I need to name my fields, but for now I just want to sort this grouping problem.

slightly reduced code below that does group correctly but don't understand why the first isn't grouping the same:

Code:

SELECT DISTINCT [_tbl_Structure].Supervisor, Sum(tbl_Genesys_Daily.Field7) AS Calls
FROM _tbl_Structure INNER JOIN tbl_Genesys_Daily ON [_tbl_Structure].[User ID] = tbl_Genesys_Daily.Field5
GROUP BY [_tbl_Structure].Supervisor, [_tbl_Structure].CSM;

Minty · Apr 16, 2018

By grouping on the availability calculation, I'm guess there are three different values for that being returned?

If so then you'll need to make the calculation in another query and bring that back to your second query linked by Supervisor or manager, in a third query.

plog · Apr 16, 2018

There's no reason for both a DISTINCT and a GROUP BY. They do essentially the same thing. My advice is kill the DISTINCT (actually, I'd never use it).

Now, every unique permutation of values in the GROUP BY will produce a record. Since you are grouping by Supervisor and CSM, each permutation will get its own row. Since CSM is not in the SELECT when you look at the results there is no way to tell that the records returned are in fact unique.

Why do you need to GROUP BY CSM but not show it?

Pat Hartman · Apr 16, 2018

How can you work with a table that has meaningless field names? The more queries/code/etc you create before you fix the problem, the more work you make for yourself. So your first step is not to fix the grouping issue, it is to fix the column names.

As the others have alluded to, you are grouping on something you should not be grouping on. We might actually be able to point out the exact problem if we had any clue what the fields are. Once you fix the column names, you might see the problem yourself.

Using a query to group (1 Viewer)

dmorgan20

Registered User.

Minty

AWF VIP

plog

Banishment Pending

Pat Hartman

Super Moderator

Similar threads

Users who are viewing this thread