Query Issue

[Excel_Query]

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,

Ayyad

 

[nullZero]

what do you want to happen when there is a tie?

 

[Excel_Query]

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

Thanks

 

[nullZero]

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;

 

[Excel_Query]

thx for replying

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


Ayyad