When to use text vs. Number for field

[bigalpha]

I randomly read on another website that your field type in a table should be 'text' unless you're doing math on the number'. Is this true?

 

[ChrisO]

No, it is little more than a guide.

If it was taken in any absolute sense it would require the AutoNumber to be text simply because people would not be doing calculations on an AutoNumber field.

If I had to recommend a data type for a field I would be asking how that field will be used and not restrict the answers to being used in calculations.

Chris.

 

[bigalpha]

My fields will be used as a label, though the data is numeric.

For example, "001" is Acid, "002" is Adhesive.

*edit*
The numbers we are using are going to be 3 digits with leading zeros; e.g. 001, 016, etc. There is a series of three numbers, e.g. 001-003-001 for each product. In this case, 001-003-001 is 'waste acids'-'waste hydrochloric acid'-'hydrochloric acid (d002)'.

While it doesn't matter if the table stores '1' or '001', I need to be able to show the leading zeros on forms and reports. Additionally, I will need to be able to concatenate the numbers in a query for output. For example, 001-003-001 would be 001003001.

Now that I think about it, I don't really like the way 'text' sorts numbers. Doesn't it sort them strangely, like "1, 10, 11, 2, 3, 4, 5, 6, 7, 8, 9"?

 

[Pat Hartman]

To start with the last question, text fields are left justified and that is why 11 sorts before 2 because the first 1 in 11 is less than 2. When you zero-fill numbers stored as text, that has the effect of right justifying them and so they sort the way you would expect numbers to sort.

If there is any possiblity that in the future you might need alpha characters in the code, store it as text with leading zeros. Otherwise, you can store it as an integer and zero-fill it for display using the Format(YourField, "000") function.

 

[ChrisO]

I must say I don’t understand this:-
001-003-001 is 'waste acids'-'waste hydrochloric acid'-'hydrochloric acid (d002)'.

001 = 'waste acids'
and
001 = 'hydrochloric acid (d002)'

It looks like the digit code is an index into a description table but for that to work the two descriptions would need to be in separate tables.



Also, D002 is an EPA Waste Code which can apply to many different chemicals not just hydrochloric acid. (It appears to be based on pH range.) So having 'hydrochloric acid (d002)' in one field is actually storing two pieces of information in that field.

And since 'waste hydrochloric acid' is already indexed by 003 and that 'waste hydrochloric acid' may not be in the D002 pH range (Corrosive is <= 2 pH or > 12.5 pH) then having a description of 'hydrochloric acid (D002)' would seem redundant.

It would appear to be better to store the chemical in one table and the waste code in another table. 'hydrochloric acid (D002)' can then be concatenated by chemical index 003 in the chemical table and whatever the index of D002 is in an EPA Waste Code table.

With that setup all the digit groups become indexes and so would be numbers even though no calculations are done on those numbers. The numbers can be displayed with leading zeros if required but they are still numbers and not text.

Others may have more input on the table structure but I don’t think things like 'hydrochloric acid (D002)' should appear in any single table field.

Chris.

 

[jdraw]

It isn't clear whether you are using some standardized code, or dreaming something up as you go. If there is a codification scheme that applies to your industry, why not use it. Why re-invent something that has been designed.

I've seen a lot of codification schemes that have fallen on to limited/no use because of some unforeseen condition --- then it's usually let's start putting in some alpha chars or similar. And when that starts going south, someone suggests --let's start with Z..... and it goes downhill from there...

????

 

[bigalpha]

To start with the last question, text fields are left justified and that is why 11 sorts before 2 because the first 1 in 11 is less than 2. When you zero-fill numbers stored as text, that has the effect of right justifying them and so they sort the way you would expect numbers to sort.

If there is any possiblity that in the future you might need alpha characters in the code, store it as text with leading zeros. Otherwise, you can store it as an integer and zero-fill it for display using the Format(YourField, "000") function.

Thanks for explaining the sorting with text vs. numeric data. Will the Format(YourField, "000") work only in forms and reports?

 

[bigalpha]

I must say I don’t understand this:-
001-003-001 is 'waste acids'-'waste hydrochloric acid'-'hydrochloric acid (d002)'.

001 = 'waste acids'
and
001 = 'hydrochloric acid (d002)'

It looks like the digit code is an index into a description table but for that to work the two descriptions would need to be in separate tables.

Each level of numbers is in a separate table. The numbers get more specific as you go from left to right.

001 is is a general 'waste acids' category.
003 narrows 'waste acids' down to hydrochloric acids
001 narrows 'hydrochloric acids' down to HCl acid that has a D002 code.

Also, D002 is an EPA Waste Code which can apply to many different chemicals not just hydrochloric acid. (It appears to be based on pH range.) So having 'hydrochloric acid (d002)' in one field is actually storing two pieces of information in that field.

And since 'waste hydrochloric acid' is already indexed by 003 and that 'waste hydrochloric acid' may not be in the D002 pH range (Corrosive is <= 2 pH or > 12.5 pH) then having a description of 'hydrochloric acid (D002)' would seem redundant.

It would appear to be better to store the chemical in one table and the waste code in another table. 'hydrochloric acid (D002)' can then be concatenated by chemical index 003 in the chemical table and whatever the index of D002 is in an EPA Waste Code table.

With that setup all the digit groups become indexes and so would be numbers even though no calculations are done on those numbers. The numbers can be displayed with leading zeros if required but they are still numbers and not text.

Others may have more input on the table structure but I don’t think things like 'hydrochloric acid (D002)' should appear in any single table field.

Chris.

To clarify, I'm not using the 001-003-001 number to call out any data from any other tables. The numbering scheme is used solely for identification of different hazardous waste types within our facility (Army Installation). So, 001-003-001 will ONLY mean waste hydrochloric acid with a D001 code.

This coding scheme is only part of a larger scheme. We are the waste-end, and another program is developing a material-end numbering scheme that ties into our numbers. The effect is that we can track a particular type of material through from it's virgin form, through a process (that creates wastes) and to its waste form. The front end scheme will have their own 9 or 12 digit code.

To use a different example from the numbering scheme:
002-003-001 is Waste Adhesives - Flammable - D001; PGII
002-003-002 is Waste Adhesives - Flammable - D001; PGIII
002-003-003 is Waste Adhesives - Flammable - D001, D035; PGII

 

[bigalpha]

It isn't clear whether you are using some standardized code, or dreaming something up as you go. If there is a codification scheme that applies to your industry, why not use it. Why re-invent something that has been designed.

I've seen a lot of codification schemes that have fallen on to limited/no use because of some unforeseen condition --- then it's usually let's start putting in some alpha chars or similar. And when that starts going south, someone suggests --let's start with Z..... and it goes downhill from there...

????

This coding scheme is not being dreamed up as I go. There is no real coding scheme that is industry standard; however, this coding scheme has been implemented at a different installation successfully for a number of years. It's only being slightly tweaked to fit our needs.

 

[jdraw]

I did some work with ECCMA and UNSPSC --seems you have considered any existing codes.

Good luck with your project.

 

[Pat Hartman]

I would use the Format(yourfield,"000) in the query rather than the form/report and that way, it could be used for sorting also.

 

[bigalpha]

I did some work with ECCMA and UNSPSC --seems you have considered any existing codes.

Good luck with your project.

Can you please explain why you think the code contained within those two sites would be superior to the system being designed here?

After taking a cursory look through UNSPSC codes, there's no way those codes would be useful for us. The code doesn't contain most of the wastes that we generate, and the code is exceptionally long. Additionally, the code itself is not categorically linked to other items of the same type.

As far as ECCMA, I couldn't find a sampling of the codes they use.

As I mentioned earlier, the system we will be using has been used extensivly and successfully other places.

 

[bigalpha]

I would use the Format(yourfield,"000) in the query rather than the form/report and that way, it could be used for sorting also.

Excellent, this worked great! Thanks for the tip about putting it in the query! Took me a few minutes to figure out that I format the field name in the "Field" row. I always forget that I can do stuff up there.

Thanks again!

 

[ChrisO]

Well this seems to clarify things for me:-

>>This coding scheme is only part of a larger scheme.<<

In this case I don’t think it matters if the codes are formatted numbers or text.
The reason for making a choice of either one is not based on the ability to do calculations on the codes. And the sorting can be done on either numbers or text so that should not represent a problem either.

I would base the choice of numbers or text on the reliable transfer of data between the systems. It’s that reliable data interchange method that may prove to be the key to the entire system.

Perhaps it would be prudent to test the data interchange methods before making a decision as to what storage method to use. Examples of data interchange methods may be CSV file transfer or scanned Bar codes, we don’t know. Nor do we know if the transfers between systems would require encryption. In deed, the Army may force a data interchange standard, we don’t know.

So the data type of storage will be based on many things, not just the ability to do calculations on the raw data as per your first question.

Chris.

 

[bigalpha]

Perhaps it would be prudent to test the data interchange methods before making a decision as to what storage method to use. Examples of data interchange methods may be CSV file transfer or scanned Bar codes, we don’t know. Nor do we know if the transfers between systems would require encryption. In deed, the Army may force a data interchange standard, we don’t know.

So the data type of storage will be based on many things, not just the ability to do calculations on the raw data as per your first question.

Chris.

This system that we're implementing is going to be built from the ground up - there will be zero interfacing with any other system. In fact, I believe that the Access DB will be the only system that will be in place.

 

[jdraw]

Can you please explain why you think the code contained within those two sites would be superior to the system being designed here?

After taking a cursory look through UNSPSC codes, there's no way those codes would be useful for us. The code doesn't contain most of the wastes that we generate, and the code is exceptionally long. Additionally, the code itself is not categorically linked to other items of the same type.

As far as ECCMA, I couldn't find a sampling of the codes they use.

As I mentioned earlier, the system we will be using has been used extensivly and successfully other places.

I didn't say they were better, I thought you had considered other codes and went the way you are going based on facts. And from my reading that the system has been used for a number of years, it seems you have done some review of options.

I was trying only to suggest don't dream something up for your local use if you have to fit into a bigger picture.

As for ECCMA, I recall people from the Air Force and others participating in codification and Electronic Commerce activity (about 9-10 yrs ago). There was tremendous frustration with the various incompatible systems that exist and became highlighted in NATO and international exercises.

 

[bigalpha]

I was trying only to suggest don't dream something up for your local use if you have to fit into a bigger picture.

Nope, there is no where else for us to integrate into. We're pretty low tech around these parts.

 

[ChrisO]

From post #8.
>>This coding scheme is only part of a larger scheme. We are the waste-end, and another program is developing a material-end numbering scheme that ties into our numbers. The effect is that we can track a particular type of material through from it's virgin form, through a process (that creates wastes) and to its waste form.<<

From post #15
>> This system that we're implementing is going to be built from the ground up - there will be zero interfacing with any other system. In fact, I believe that the Access DB will be the only system that will be in place.<<

Juxtaposition please.

Chris.

 

[RainLover]

To me if it looks like a Number then it is probally a Number. Treat it as such.

You have three component that form the end code. Each of which should be stored in separate Fields. Each one of those should be lookups to another table for Normalisation purposes.

Sorting searching etc can then be done on any of the three components or any combination of the three.

Concatenation and Formating should be done last as part of the Record Source for the Form or Report.

Best wishes for which ever way you go. The one important point I have made is to do with the Normalisation of the table. I am surprised that ChrisO was the only person to pick up on this important issue.

 

[bigalpha]

From post #8.
>>This coding scheme is only part of a larger scheme. We are the waste-end, and another program is developing a material-end numbering scheme that ties into our numbers. The effect is that we can track a particular type of material through from it's virgin form, through a process (that creates wastes) and to its waste form.<<

From post #15
>> This system that we're implementing is going to be built from the ground up - there will be zero interfacing with any other system. In fact, I believe that the Access DB will be the only system that will be in place.<<

Juxtaposition please.

Chris.

Sorry.

I work in a Division that has about two dozen programs. My program (Waste) and the other program (Process Mapping) will each be using this same type of system to identify where materials are, what process they go through and what waste they turn into. They use the first half of the string, and we use the second half of the string.

There will only be one database with all of the data in it for everyone to cross reference. There is no other stand-alone, Army specific, or commercial application that will be interfacing with our data.