Field Formatting

[adyf]

I have a number field in a questions and answers table that has data such as:

7.1.065

7.1.103

This means the 65th and 103rd question of unit 7.1 etc

When a candidate answers a question a record is created in another table but the data is displayed as:

7.1065

7.1103

Is there a reason why it is removing the second decimal point? I understand that a second decimal point isn't normal as such but I was hoping to keep with this format as it's meaningful to me. If I have to change the field to text I expect I won't be able to sort them numerically which works as things are now.

[David Jondreau]

Because 7.1.065 is not a number. Change the field type to Text.

[comment]

Or - preferably, IMHO - stick to the 'one fact per field' rule.

[adyf]

Because 7.1.065 is not a number. Change the field type to Text.

This does the trick, thank you. I have to ask, will an ascending text sort be as accurate as a numerical sort taking into account my data?

Or - preferably, IMHO - stick to the 'one fact per field' rule.

Such as 7.1 and 65?

[David Jondreau]

This does the trick, thank you. I have to ask, will an ascending text sort be as accurate as a numerical sort taking into account my data?

No. If you have more than 9 units, then you'll have a sorting issue as 10.1 comes before 7.1 in text. You'll have to change it to 07.1.

Such as 7.1 and 65?

You should definitely be storing the data (Unit, question, etc) in separate fields. Display is another question.

[comment]

Such as 7.1 and 65?

I would guess 7 and 1 and 65 - but I am not familiar with your structure. Normally, in a hierarchy, a child needs to remember only its immediate parent; the grandparent is known through the parent, etc.

Moreover, all questions should have a unique QuestionID, regardless of their parent unit - and this is the field you want to use as the matchfield for linking to the Answers table.

[adyf]

- and this is the field you want to use as the matchfield for linking to the Answers table.

Mmmm........Do I have a fundamental flaw in having the question and answer fields within the same record in the same table?

[comment]

Do I have a fundamental flaw in having the question and answer fields within the same record in the same table?

Didn't you say earlier that:

When a candidate answers a question a record is created in another table

What is the other table then, if not Answers?

In any case, the real question is whether more than one person answer the same question. If the answer is yes, then you have a one-question-to-many-answers relationship - and this means two tables.