Query Sum Help

[Mikekim]

I have a list of players (TOT_KEY), there top 10 finishes (FINISHED), sum of winnings (SUMOFWINNINGS).
My problem is FINISHED contains numbers and letters, example 2-T, 3-T, 4-T through 10-T ETC.
I need to sum the SUMOFWINNINGS by each player (TOT_KEY).
I have exported the data to Excel and I am going to try and attach the Excel file.
Any help would be greatly appreciated. Once I get this I think I can figure out how to combine the queries to get the data I am after.


Thanks
Mike

[Bob Fitz]

I think it should be possible using InStr() and Left() functions

[andy49]

Something like below called from within SQL getposition numfinish: Getposition(finished)

function getposition (posstring as text) as integer
dim mypos() as string
if isnumber(posstring) = true then
getposition = posstring
exit function
else
mypos = split (posstring,"-")
getposition = mypos(0)
end if
end function

[ranman256]

if FINISHED = "2-T" , then in the query separate using: left(FINISHED,instr(FINISHED,"-")-1)

you should get : 2

[Mikekim]

Well I tried the examples but I am getting errors. Here is the SQL in Query6
SELECT SJGOLF.TOT_KEY, SJGOLF.FINISHED, Sum(SJGOLF.WINNINGS) AS SumOfWINNINGS
FROM SJGOLF
GROUP BY SJGOLF.TOT_KEY, SJGOLF.FINISHED;

Is it possible to attach the Access file? I'm new to this.
Mike

[andy49]

Feel free to attach it but zip it first. We'll happily take a look.

[Mikekim]

Here is the attached file.
I sure I'm taking the wrong approach. This came from Dbase III. I know what I'm doing there but getting it in Access is a real challenge.
Thank you for all your help.
Mike

[andy49]

could we have a copy of the linked table?

[andy49]

Try leaving out sjgolf.finished from all parts as well.

[Bob Fitz]

I think the SQL for query should be:

SELECT Sum(Left([FINISHED],InStr([FINISHED],"-")-1)) AS SumOfFinishedNum, SJGOLF.TOT_KEY
FROM SJGOLF
GROUP BY SJGOLF.TOT_KEY;

[Mikekim]

Here is the linked table
Told you I was new to this.....
Mike

[andy49]

If you only need a list of players and the sum of their winnings then,

SELECT SJGOLF.TOT_KEY, Sum(SJGOLF.WINNINGS) AS SumOfWINNINGS
FROM SJGOLF
GROUP BY SJGOLF.TOT_KEY;

[Mikekim]

If you only need a list of players and the sum of their winnings then,

SELECT SJGOLF.TOT_KEY, Sum(SJGOLF.WINNINGS) AS SumOfWINNINGS
FROM SJGOLF
GROUP BY SJGOLF.TOT_KEY;

This gives me the total winnings of all players my dilemma is I need the totals of only the players that finished in the top 10 places.
1, 2, 2-T, 3, 3-T, 4, 4-T, 5, 5-T, 6, 6-T, 7, 7-T, 8, 8-T, 9, 9-T, 10, 10-T.
The players that tied is what I don't seem to be able to pull accurately, because of the -T in the finished place.
Mike