Greetings Knacksters.
Bit of a problem and support is not able to assist and suggested throwing it to the crowd, So here goes.
I have a database of soldier records from WW2, some 22,500 in size.
Many of these men were taken POW by German/Italian forces in the Middle-East.
As a generalisation most went through POW camps in Libya, then onto Italy and some ended up in German Stalags.
Now the problem. We have created a table of Italian and German POW Camps. The Italian Camps were numbered in conventional format but most German camps used roman Numerals and '-x" where x was letter of alphabet.
To have them displayed in a dropdown box I added a sequence number field so that they would sort numerically Italian then German camps.
This is working fine with one serious promblem.
Rather than storing and retrieving the POW Camp numbers the chap was interned at in the sequence it was entered, it reverts to the sort field order which is the “meaningless sequence number I mentioned before”.
So the values in the Camp Nos is no longer in sequence of which he was interned (i.e 57, 106, 66, XVIII-A HAS TURNED BACK INTO 57,66,106,XVIII-A) and therefore has become useless in terms of displaying the information.
Because you HAVE TO nominate a sort order field for the table it plays havoc with any report based on the order of entry.
The most obvious way would be to store the POW records as an extra file and adding fields for the dates of internment. But thats a load of extra work and we dont really have use for the dates other than if this is our only option.
I do note that by moving it externally it would mean less fields required in the main file as not every soldier was a POW however.
But as we have already compiled the information on file WITHOUT DATES other than years the solider was a POW (i.e 1941 - 1943) it would mean going back through the red cross files to confirm the dates and then entering them in all over again.
Any other ideas & thoughts ???
Cheers in advance