Calculation Help

[Guest]

I have the following code....
What I need it to do is in the FeaturePoints if they have a DNF or DNS it
should give them 1 point for the DNF and 0 Points for a DNS

SELECT LateDriver.Class, LateDriver.[Car#], LateDriver.DriverName,
RaceDetail.[Car#], RaceDetail.Class, RaceDetail.RaceDate, RaceDetail.Heat,
RaceDetail.HeatPlace, RaceDetail.Feature, RaceDetail.FeaturePlace,
RaceDetail.Consy, RaceDetail.ConsyPlace, 6-[HeatPlace] AS HeatPoints,
31-[FeaturePlace] AS FeaturePoints, [HeatPoints]+[FeaturePoints] AS WeekTotal
FROM LateDriver LEFT JOIN RaceDetail ON LateDriver.[Car#] = RaceDetail.[Car#];

 

[John Vinson]

I have the following code....
What I need it to do is in the FeaturePoints if they have a DNF or DNS it
should give them 1 point for the DNF and 0 Points for a DNS

Where and what is a DNF or a DNS? What if there is some OTHER value in
this field - how many points then?

John W. Vinson[MVP]

 

[Guest]

The DNF and DNS are in a lookup [FeaturePlace] so you can only select what is
given to you (1 ,2, 3.....30,DNF,DNS), you can come in either 1st place all
the way down to 30th unless you DNF (do not finish) prior to the half way
point you would then get 1 point, if you DNS (do not start) you get 0 points.
This section of the code..." 31-[FeaturePlace] AS FeaturePoints" also if I
want to be able to track the place past 5 in the [HeatPlace] I want it to
return 0 for any place greaterthan 5th place...this section of
code....6-[HeatPlace] AS HeatPoints

 

[John Vinson]

The DNF and DNS are in a lookup [FeaturePlace] so you can only select what is
given to you (1 ,2, 3.....30,DNF,DNS), you can come in either 1st place all
the way down to 30th unless you DNF (do not finish) prior to the half way
point you would then get 1 point, if you DNS (do not start) you get 0 points.
This section of the code..." 31-[FeaturePlace] AS FeaturePoints" also if I
want to be able to track the place past 5 in the [HeatPlace] I want it to
return 0 for any place greaterthan 5th place...this section of
code....6-[HeatPlace] AS HeatPoints

Is [FeaturePlace] a Lookup Field? If so, its actual contents are
probably a numeric ID assigned by Access.

You'ld do better to have a lookup table with two fields - the
FeaturePlace, a text field with values 1, 2, 3, ..., 30, DNF, DNS and
another field with the score for that place. You could then simply
join this table by FeaturePlace and pick up the score directly from
the query.

John W. Vinson[MVP]

 

[Guest]

[FeaturePlace] and [HeatPlace] are both lookup fields that look up data in a
table I created (not a table access created). The problem is that sometimes
they both will have double points.

The DNF and DNS are in a lookup [FeaturePlace] so you can only select what is
given to you (1 ,2, 3.....30,DNF,DNS), you can come in either 1st place all
the way down to 30th unless you DNF (do not finish) prior to the half way
point you would then get 1 point, if you DNS (do not start) you get 0 points.
This section of the code..." 31-[FeaturePlace] AS FeaturePoints" also if I
want to be able to track the place past 5 in the [HeatPlace] I want it to
return 0 for any place greaterthan 5th place...this section of
code....6-[HeatPlace] AS HeatPoints

Is [FeaturePlace] a Lookup Field? If so, its actual contents are
probably a numeric ID assigned by Access.

You'ld do better to have a lookup table with two fields - the
FeaturePlace, a text field with values 1, 2, 3, ..., 30, DNF, DNS and
another field with the score for that place. You could then simply
join this table by FeaturePlace and pick up the score directly from
the query.

John W. Vinson[MVP]

 

[John Vinson]

[FeaturePlace] and [HeatPlace] are both lookup fields that look up data in a
table I created (not a table access created). The problem is that sometimes
they both will have double points.

I do not know what "double points" means.

Please remember - YOU are familiar with your project. We are not. My
telepathy has been on the blink lately...

Please reread my suggestion. Lookup fields are of VERY limited
utility, and probably are incapable of doing what you want. I'd
suggest having a Points table (instead of a Lookup Field); this table
could have *an additional field*, a field that you do not now have,
storing the points for that selected ranking.

John W. Vinson[MVP]

 

[Guest]

I will give it a try..thanks

The DNF and DNS are in a lookup [FeaturePlace] so you can only select what is
given to you (1 ,2, 3.....30,DNF,DNS), you can come in either 1st place all
the way down to 30th unless you DNF (do not finish) prior to the half way
point you would then get 1 point, if you DNS (do not start) you get 0 points.
This section of the code..." 31-[FeaturePlace] AS FeaturePoints" also if I
want to be able to track the place past 5 in the [HeatPlace] I want it to
return 0 for any place greaterthan 5th place...this section of
code....6-[HeatPlace] AS HeatPoints

Where and what is a DNF or a DNS? What if there is some OTHER value in
this field - how many points then?

John W. Vinson[MVP]