Text in a Number Field

[Guest]

I have a table that stores race results. These are mainly numbers, in this
case 1-22. But I'd also like to store values like DNS (Did Not Start), DNF
(Did Not Finish) etc, which of course isn't a number, in the same field, as
they are as much a result as 1, 2,3 etc.

I can do this but then can't use aggregate functions when querying the
field. I think I know the answer (No!) but is there a way around this or do I
have to do as I do now and store the text values in a seperate column? I know
it's a bad approach to store numbers & text in the same field, but having two
fields for what is essentially the same value is annoying!

 

[RoyVidar]

Matt D Francis wrote in message

I have a table that stores race results. These are mainly numbers, in this
case 1-22. But I'd also like to store values like DNS (Did Not Start), DNF
(Did Not Finish) etc, which of course isn't a number, in the same field, as
they are as much a result as 1, 2,3 etc.

I can do this but then can't use aggregate functions when querying the
field. I think I know the answer (No!) but is there a way around this or do I
have to do as I do now and store the text values in a seperate column? I know
it's a bad approach to store numbers & text in the same field, but having two
fields for what is essentially the same value is annoying!

To me, this is two different things -> two different fields.

The status of how one finished (or didn't finish) the run or race - and
the actual results. Only those finishing the run or race within the
regulations (stipulated number of laps, certain distance within time
limits ...), qualifies for a "numeric result", the rest, will have no
result, but some "textual description" of DNF, Laps 0, Excluded, Flag
.... (or use a numeric statusfield linked to a lookup table with the
textual desctiptions).

Then I think it might be a matter of presentation - for instance in the
actual report use an expression that shows the numeric result for those
who have such, and the textual expression for the rest.

 

[Duncan Bachen]

I have a table that stores race results. These are mainly numbers, in this
case 1-22. But I'd also like to store values like DNS (Did Not Start), DNF
(Did Not Finish) etc, which of course isn't a number, in the same field, as
they are as much a result as 1, 2,3 etc.

I can do this but then can't use aggregate functions when querying the
field. I think I know the answer (No!) but is there a way around this or do I
have to do as I do now and store the text values in a seperate column? I know
it's a bad approach to store numbers & text in the same field, but having two
fields for what is essentially the same value is annoying!

Every racer can only have either a place (1-# of racers) or a status
field of why they didn't race, correct?

Why not use negative values to store the did not race status type.
Then you can use a lookup table which has the negative values and their
corresponding text of status. Or, you could join to the Lookup table on
the StatusID with a left join, so it would only return the Status Desc,
if one existed in the other table.

In future functions, you can ignore the negatives. Your aggregate
functions (such as count) would still tell you every racer that had a
status, since it'd be filled in.

tblStatusLookup
StatusID Number
StatusDesc Text (e.g. "Did Not Finish", "Did Not Start"

 

[Guest]

Use a specific number to identify DNS DNF and possibly, DSQ that would be
well beyond any participants. I don't know what kind of event you are
scoring, but I know in yacht racing the usual rule is
DNS = # of starters + 1
DNF = # finishers + 1
DSQ = # entries + 1

So, do a conversion somewhere to an arbitrary number like:
DNS = 10,000
DNF = 10,001
DSQ = 10,003

Now, with those known values, your sorting will work and you can convert
them back to their text values for reports, etc.

 

[Guest]

All of the others have good suggestions but here is one more.
Use zero for DNS and 99 for DNF. Use an IIF statement for display.
IIF([YourField]=0,"Did not start", IIF([YourField]=99,"Did not finish",
[YourField] & " Place"))

 

[Guest]

Very similar to my suggestion. The difference is that having written scoring
systems for yacht racing and shooting competitions, I found that the results
needs to be sorted by finish position; Therefore assigning 0 for DNS would
sort an entrant that did not start the race or stage ahead of first place.
Also, in many events there will be more than 99 competitors, so that is not a
large enough number.
The World Championship Cowboy Action Shooting competition, for example, has
over 500 entries.

All of the others have good suggestions but here is one more.
Use zero for DNS and 99 for DNF. Use an IIF statement for display.
IIF([YourField]=0,"Did not start", IIF([YourField]=99,"Did not finish",
[YourField] & " Place"))

I have a table that stores race results. These are mainly numbers, in this
case 1-22. But I'd also like to store values like DNS (Did Not Start), DNF
(Did Not Finish) etc, which of course isn't a number, in the same field, as
they are as much a result as 1, 2,3 etc.

I can do this but then can't use aggregate functions when querying the
field. I think I know the answer (No!) but is there a way around this or do I
have to do as I do now and store the text values in a seperate column? I know
it's a bad approach to store numbers & text in the same field, but having two
fields for what is essentially the same value is annoying!

 

[Guest]

Thanks guys - lots of suggestions!

Using 0 or 99 was my initial thought but I'm using the figures very much as
a queryable statistics database, not just an archive. So as has been said, I
get weird dodgy results if there's numbers other than actual finish
positions.

I built this expression in the query to ignore the non numeric values:

FinPos: IIf(IsNumeric([TBL_RACE_RESULTS]![POS]),[TBL_RACE_RESULTS]![POS],"")

but I still can't use the aggregate functions (mismatched data type) . So I
then tried to convert the expression above into a number reuslt by adding
CInt or CLng at the start but with no luck. Should that be possible?

If not I'm just going to go with the Lookup table for use in Queries

Cheers

Matt

 

[John Spencer]

Try

FinPos: IIf(IsNumeric([TBL_RACE_RESULTS]![POS]),[TBL_RACE_RESULTS]![POS],Null)

If you use a zero length string, then Access is forced to make the column a text
column. A column of any type can contain null values.

 

[Guest]

Brilliant - that's it! Thank-you very much. Very usefull

Cheers

Matt

Try

FinPos: IIf(IsNumeric([TBL_RACE_RESULTS]![POS]),[TBL_RACE_RESULTS]![POS],Null)

If you use a zero length string, then Access is forced to make the column a text
column. A column of any type can contain null values.

Thanks guys - lots of suggestions!

Using 0 or 99 was my initial thought but I'm using the figures very much as
a queryable statistics database, not just an archive. So as has been said, I
get weird dodgy results if there's numbers other than actual finish
positions.

I built this expression in the query to ignore the non numeric values:

FinPos: IIf(IsNumeric([TBL_RACE_RESULTS]![POS]),[TBL_RACE_RESULTS]![POS],"")

but I still can't use the aggregate functions (mismatched data type) . So I
then tried to convert the expression above into a number reuslt by adding
CInt or CLng at the start but with no luck. Should that be possible?

If not I'm just going to go with the Lookup table for use in Queries

Cheers

Matt