Dismal Days w/Decimals 1

[Hookstrat]

I was asked to provide assistance to a co-worker who has a field in a table, and subsequent form, whith project identification numbers (named Project ID). She made the mistake of using numer for her data type even though the numbers will never be manipulated by any mathematical operations. When the datatype is switched from "number" to "text", the order of the records get completely messed up. Grouping in ascending order doesn't solve the problem either, because her ID system was not designed perfectly. Most of the project IDs don't have decimals, but projects with multiple "phases" have ascending decimals- 101,101.1,101.2,101.3. The problem arises when a project ID moves into the hundreth space (i.e.,101.10). Obviously, Access 2000 removes the trailing zero. I tried input masks but then the non-decimal numbers are given two zeros, and the one-decimal numbers are given an extra zero and then there are duplication errors. Is there a way to use an input mask, but when there is not a value entered in the decimal places, Access will just put in spaces instead of a zeros??? If not, what would be the easiest way to go about this? I dont want to have to break the news to her that she has to re-enter a few thousand records!!! Any help would be greatly appreciated! Thanks in advance.
Luke Hoekstra

 

[SBendBuckeye]

Luke,

A couple thoughts. I don't see how you would be getting duplicates unless she has xxxx. and xxxx.0 as 2 different keys. If I was involved with this, I would create a copy of the key portion of her table and add a text field and see if I could run the old key through a function that would calculate the new field. That way you can handle odd situations a little more easily.

Good Luck!

 

[Darrylles]

Hiya Hook,

The problem that you are encountering is due to Access alphabetically ordering the converted 'number' fields, we've all seen it in Windows Explorer where 'seemingly' larger numbers appear before smaller when files have 'numeric' filenames.
It is logical and correct behaviour.

e.g. 10
102
11

This is because the ASCII table is used to evaluate the values of each character of each string - column by column - making the first digit the most significant, the 2nd digit the second most significant etc. So 11 is 'bigger' than 102 - according to the ASCII table.
Useless info, but that's how it works.

Is it impossible to change your 'logic' to accomodate your situation?

e.g You revert back to the number field, but use the 'thousands' portion as .101, .102 etc?

If this is unrealistic, don't lose hope - this CAN be rectified - period. It's just a case of YOU specifying the EXACT rules for the format of the REQUIRED string after conversion from the current number field.

e.g. 101.1 MUST be 101.10

To make this work absolutely correctly, you MUST specify the 'rule' for EVERY possible format that you would want to see (not EVERY record mind - just possible formats).

If you can do that, then it's easily possible.

We can - step by step ensure that all current Project_ID's are converted to text in the format that you want.

I hope that someone kicks in here and says 'flick this switch and you've got it', but I doubt it.

You want to solve this, then I'll help you by using data conversion through Access queries.

Regards,

Darrylles "Never argue with an idiot, he'll bring you down to his level - then beat you with experience."

 

[cmmrfrds]

Any field should only have 1 meaning. This is part of normalizing the data. It would be better if project id and phase where separate fields. It will save problems and be easier to work with in the long run. Since you are still developing the application it is better to make the change now and be done with it. Remember any field in a table should have a single meaning, and depend on the key (of the table) the whole key and nothing but the key so help be Codd.

Example of possible tables.
Project table
Phase table
ProjectPhase table

It will be necessary to write a conversion program or so to build the new relationships.

 

[Hookstrat]

Thanks to all for the replies!! The reason she was getting duplicates is because of poor design. She didn't think about there ever being more than 9 phases. So when I changed the decimals to allow the the tenth phase (101.10) to be displayed, Access added a zero to the first phase (101.1)...thus there were two records for 101.10. Darryles, if I use your method should I not worry about the tables/forms and just worry about the subsequent queries?? Note: There are not going to be very many decimals in the hundreths place. But where would I enter the "101.1 MUST be 101.1" and "101.10 must be 101.10" and is that the right syntax?
Cmmrfrds: I really liked your solution, kicking myself for not having thought of that in the first place, but it wont solve the trailing zero problem unless she redesigns her identification system so that the first phase is 101.01, the second phase is 101.02, etc. If Darryles solution doesn't work or is too hard to manipulate I will definitely recommend that she splits up her table. One question: Why would I need to write a conversion program to build the relationships? Thanks again guys!
Luke Hoekstra

 

[cmmrfrds]

I was thinking a program to load from the current table a project table where the project id is created along with the associated data, then a phase table with a definition of all the possible phases, then a table with the many relationship where project id and phase are related. project id and phase would both be defined as integer and concatenated to display the format of 101.1, 101.9, 101.10 etc...

select (projectid + ":" + phase) as projectphase from ProjectPhase table.

entered as projectid = 101, phase = 1, or phase = 10 etc....

If a leading zero is needed these 2 id's could be defined and entered as string data.

 

[Hookstrat]

Thanks a lot cmmrfrds! I will try to implement some of your ideas into her database. I will also inform her of better ways to design a database from the onset, so that future problems like these can be avoided. Much appreciation to all that lended a helping hand.
Luke Hoekstra