# Query Issue

#### Excel_Query

##### New Member
I have been trying to figure out this out for a while now, PLEASE help out if you can

I have a table with 3 fields... tID, Name, Hours

tID is NOT unique in the table
tIDs Can have different names
Hours is what I will use to find the total hours

I want my query to group by tID only, and show the SumofHours
BUT... I want to show the name as well(without Grouping by it), I want my query to Show me the name that is MOST Recurring.

So lets say that tID '1' occurs four times, with the Names...
'Joey' 'John' 'Johan 'John' 'Joe'...
Grouping by tID '1' I would want to see 'John' under the name field (since its the highest by count)
Baisically I just want to create a list of Unique tIDs with the most likely correct name on its side

At first I thought I could just use the Max function from the drop down menu, but that only uses the one that is alphabetically a maximum... I need it to be a maximum by count..

Your help would be appreciated,

### Excel Facts

Move date out one month or year
Use =EDATE(A2,1) for one month later. Use EDATE(A2,12) for one year later.
what do you want to happen when there is a tie?

Choose either of them, it does not matter... :P

Thanks

you will need to use 3 queries to solve, replace Table1 below with your table name:

Q1:
SELECT
tID
, Count(Name) as Frequency
FROM
Table1
GROUP BY
tID
, Name;

Q2:
SELECT
tID
, Name
FROM
Table1
GROUP BY
tID
, Name
HAVING
Count(Name) = DMax("[Frequency]","Q1","tID = " & tID);

Q3:
SELECT
Table1.tID
, First(Q2.Name) as Primary_Name
, Sum(Table1.Hours) as Total_Hours
FROM
Table1 LEFT JOIN Q2 ON (Table1.Name = Q2.Name) AND (Table1.tID = Q2.tID)
GROUP BY
Table1.tID;

but is there away where i could consolidate those queries into one SQL statement (or query i guess)??

and how would it change if i had another "Date" field that i want to group by along with the "tID" field

Replies
6
Views
231
Replies
3
Views
293
Replies
2
Views
270
Replies
11
Views
431
Replies
3
Views
194

1,196,042
Messages
6,013,052
Members
441,746
Latest member
ArtemisAlex

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

### Which adblocker are you using?

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

### Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back