Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2009

    Unanswered: Multiple criteria to lookup value.


    In essence what I am trying to do is to create a lookup in SQL that will look at two fields in a query (qry_RaceResultsWithRankandRaceType) the fields are Rank and SchemeID. Then the lookup will look into the table (tbl_PosistionPoints) and find a record where Posistion is equal to rank and SchemeID is equal to SchemeID in the query, where there is a matching record the points field will be taken from tbl_PosistionPoints and inserted into the query under the field name racepoints.

    I have some code that tries to do this but I keep having multiple errors with it, and it wont let me save it as a query so here it is:

    Select qry_RaceResultsWithRankandRaceType.ParticipantID,
           qry_RaceResultsWithRankandRaceType.[Scheme ID],
    From   qry_RaceResultsWithRankandRaceType Inner Join tbl_PosistionPoints On
               qry_RaceResultsWithRankandRaceType.[Rank]= tbl_PosistionPoints.Posistion And
               qry_RaceResultsWithRankandRaceType.Scheme ID = tbl_PosistionPoints.Scheme ID;
    Im not sure if this is correct, and maybe I am going about this the wrong way, I originally wanted to use a Dlookup but I dont think it would work and I dont know where to start with it.

    I've attatched the database.

    I'd appreciate any help anyone can offers, thankyou.

    Attached Files Attached Files

  2. #2
    Join Date
    Jun 2007
    Maitland NSW,Australia
    If you are using SUM to calculate the total points then you must have a GROUP BY expression in your query. Something like this
    SELECT qry_RaceResultsWithRankandRaceType.ParticipantID, qry_RaceResultsWithRankandRaceType.RaceID, qry_RaceResultsWithRankandRaceType.Rank, qry_RaceResultsWithRankandRaceType.SchemeID, Sum(tbl_PosistionPoints.Points) AS SumOfPoints
    FROM qry_RaceResultsWithRankandRaceType INNER JOIN tbl_PosistionPoints ON (qry_RaceResultsWithRankandRaceType.Rank = tbl_PosistionPoints.Posistion) AND (qry_RaceResultsWithRankandRaceType.SchemeID = tbl_PosistionPoints.SchemeID)
    GROUP BY qry_RaceResultsWithRankandRaceType.ParticipantID, qry_RaceResultsWithRankandRaceType.RaceID, qry_RaceResultsWithRankandRaceType.Rank, qry_RaceResultsWithRankandRaceType.SchemeID;

  3. #3
    Join Date
    Jan 2009
    Thankyou Poppa Smurf that works perfectly, much appreciated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts