Type mismatch in expression

[rajubabuno1]

Hi all,

I'm using DB to run a small poker league, here's my setup.

Tables:
players - player_id (autonumber), player_name (text)
tourn - tourn_id (autonumber), venue (text), entry_fee (number, currency), tourn_date (date/time, medium date), n_entrants (number, integer<>0)
status - status_id (autonumber), player_name (from players table), tourn_id (from tourn table), position (number, integer<>0), winnings (number, currency)

I'm trying to create a query, that groups by player name, the count of tourn_id, victories (count of position=1), cash_prizes (count of winnings>0), sum of winnings, sum of entry_fee, net winnings (sum of winnings - sum of entry_fee)


With the help of someone else, I get this, but I keep getting error msg "type mismatch in expression"

SELECT P.player_name, Count(T.tourn_id) AS CountOftourn_id, Sum(S.winnings) AS SumOfwinnings, Sum(T.entry_fee) AS SumOfentry_fee, (SELECT Count(position) FROM status WHERE position =1
AND status.player_name= P.Player_Name) AS victories, (SELECT Count(winnings) FROM status WHERE winnings>0
AND status.player_name= P.Player_Name) AS Cash_prizes
FROM tourn AS T INNER JOIN (players AS P INNER JOIN status AS S ON P.player_name=S.player_name) ON T.tourn_id=S.tourn_id
GROUP BY P.player_name;

What's wrong with the above?

Thanks in advance

Raju

 

[nateobot]

Usually you get that error when you try to join number fields with text fields. I would verify in the design of the tables that the columns you are joining are the same datatype.

 

[llkhoutx]

Without seeing the data it's hard to tell.

The message means that you're treating a datatype incorrectly; maybe summing a text field.

 

[rajubabuno1]

If i posted it, or emailed it, could you have a look?

 

[llkhoutx]

I'll look at whatever you post or send.

 

[rajubabuno1]

No need - problem sorted!