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:
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.
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;