hopefully quick question

[pbuscio]

I am trying to show the occurences of two names together regardless of order. In other words Jim and Joe and then Joe and Jim would be considered 2 occurences.

So sort of like this

Field1 Field2 Result
Jim Joe W
Joe Jim L
Jim Joe W

then a query

FieldA FieldB Win Lose
Jim Joe 2 1

Any ideas?

Thanks

 

[pbuscio]

I am trying to show the occurences of two names together regardless of order. In other words Jim and Joe and then Joe and Jim would be considered 2 occurences. So sort of like this Field1 Field2 Result Jim Joe W Joe Jim L Jim Joe W then a query FieldA FieldB Win Lose Jim Joe 2 1 Any ideas? Thanks

Or actually

FieldA Win Lose
Jim-Joe 2 1

 

[fx3]

You could have this as part of two queries...... query 1 and 2...

first query, calculates the scores and flips if necessary.. second one tallies up...

query1

SELECT IIf([a]>,[a],) AS contestant_a, IIf([a]>,,[a]) AS contestant_b, IIf([r] & ([a]>),1,0) AS wins, IIf([wins]=1,0,1) AS losses
FROM Table1;

query2

SELECT Query1.contestant_a, Query1.contestant_b, Sum(Query1.wins) AS SumOfwins, Sum(Query1.losses) AS SumOflosses
FROM Query1
GROUP BY Query1.contestant_a, Query1.contestant_b;



-------
http://acsdb.com