🚀 go-pugleaf

Hello,

First of all I would like to know if this is the right
board where to post this request for help...

I'm not very good at SQL and I wonder if it's possible
to run a SQL which fetches the info that I want.

If this is not the right board  please let me know and

I will post it where you tell me to do it...

Otherwise, please read on...  ;-)


------------------------------------------------------


I'm building a "tests application" based on VB and an
access DB.

The users enter their guesses which are stored in the
DB...

Once all the users have entered their answers I'd like
to show a ranking of the users ordered by the number
of
right answers...

If it's possible with just one SQL (it may have nested
SQLs),
if not... a couple of related Recordsets may do...



Right now I run a SQL, from this initial Recordset I
calculate
the points for each user in the VB program... then
store
this info in an extra field (Points) in the DB...

...finally I run a second SQL with an "order by
Points".

As I find this solution too messy I ask for help...

Any suggestion will be welcome.



If you feel that my theory is feasable, here you are
detailed
info about what I got...



The DB description:

Table1
--------------------    Possible values
Name Text
FieldA Text (A1,A2,A3,A4)
FieldB Text (B1,B2,B3,B4)
FieldC Text (C1,C2,C3,C4)
FieldD Text (D1,D2,D3,D4)
FieldE Text (E1,E2,E3,E4)
FieldF Text (F1,F2,F3,F4)
FieldG Text (G1,G2,G3,G4)
FieldH Text (H1,H2,H3,H4)



Example of possible data in the DB:


Name FieldA FieldB FieldC FieldD

1    A1     B3     C4     D2
2    A3     B1     C1     D4
3    A1     B1     C2     D3
4    A2     B2     C4     D2
5    A3     B1     C3     D3
6    A4     B3     C2     D4
.    .      .      .      .
.    .      .      .      .
.    .      .      .      .



Let's say the correct answers are:

     A3     B1     C1     D4


and that we give 1 point for every right answer...

Based on these correct answers the SQL should fetch
the
records ordered by the points gained:


Name FieldA FieldB FieldC FieldD "POINTS"

2    A3     B1     C1     D4     8
5    A3     B1     C3     D3     5
1    A1     B3     C4     D2     2
3    A1     B1     C2     D3     2
6    A4     B3     C2     D4     2
4    A2     B2     C4     D2     1
.    .      .      .      .      .
.    .      .      .      .      .
.    .      .      .      .      .
.    .      .      .      .      .

Please reply to
Ramon.Martinez-Cazon@ree.ericsson.se

Thank you for your attention.

Kind regards.

RAMON