best way to write (recursive?) query

[Duncan A. McRae]

Hi there:

I must write a report for a dance competition, and a set-only approach
escapes me.

routines, dancers, scores:
- routine n <-- --> n dancers
- routine 1 <-- --> 3 scores
- rank routine by avg(score) desc, where no dancer is ranked twice.

That last one is the kicker: for each routine ranked, subsequent
routines with ranked dancers must be ignored.

Using a cursor or a loop, it's simple enough to write this in T-SQL
for SQL Server. I'm not as familiar with Access, so I'm hoping that
there's a set-based approach that Access's query engine can
understand. Any suggestions? If yes, please respond to the newsgroup
(the email associated to this post is essentially /dev/null).

Thanks;
Duncan

 

[Duncan A. McRae]

Anyone?

 

[Michel Walsh]

You can use a recordset and VBA to loop over it.

If routine number is really a number specifying the order, seems a query can
be used, without looping. Basically, routine_n with dancers, d, will do
NOTHING if any of these dancers is also in one of the dancer is in one of
the routine_i with i<n?


SELECT *
FROM routines AS n LEFT JOIN routines AS i
ON n.dancer=i.dancer AND n.number > i.number


should not return any record to be of some interest. Indeed, if there is at
least one record returned, that would mean there is a dancer, in routine_n,
also in routine_i with i < n.


SELECT DISTINCT m.*
FROM routines AS m LEFT JOIN previousSavedQuery AS p
ON m.number = p.number
WHERE p.number IS NULL



Note that this statement using a left join is just an alternative (faster
execution than the following) statement:

SELECT m.*
FROM routines AS m
WHERE NOT EXISTS (SELECT * FROM previousSavedQuery AS p
WHERE m.number = p.number)





Hoping it may help,
Vanderghast, Access MVP

 

[Michel Walsh]

Another simpler solution:


SELECT n.number
FROM routines AS n LEFT JOIN routines AS i
ON n.dancer=i.dancer AND n.number > i.number
GROUP BY n.number
HAVING COUNT(i.number) = 0


should list all numbers (routine numbers) having to be considered.



Vanderghast, Access MVP