In Excel this would be a "lookup"!

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a table that lists the [MinimumEntries] and the number of prizes that
will be given for this number of entries [position], I also have a table of
Entries.

I now need to count up the number of entries and then work out how many
prizes should be awarded - eg <=3 entries get 2 prizes, <=5 get 3 prizes etc

so I need to some how link the 2 tables (there is a prizeID field so they
are linked primarily in this way) on a "less than or equal to" basis for the
counted number of entries and the [minimumEntries] so that I can get the
number of prizes

So far I have created a separate query to count the number of entries and I
am then trying to use a subquery to get the less than or equal to bit - this
isn't working and I am wondering if this is the right approach?

Here is my query - which currently comes up with the error message "At most
one record can be returned by this subquery" (- with out the subquery I get
450 records)

SELECT SheepCount.ClassId, Prizeqry.Position, SheepCount.NoEntries
FROM Prizeqry INNER JOIN SheepCount ON Prizeqry.Prize.Prizeid =
SheepCount.PrizeID
WHERE (Prizeqry.Position)=(select [position] from [prizeqry] where [minimum
Entries] < [noEntries] or [minimum Entries]= [noEntries])

Any ideas - or is there a better way of doing this???

Thanks Helen
 
Having posted this I now realise that I was making this far too complicated
and I have solved it by using simple criteria!
 
Back
Top