Tricky Inventory Number System PART TWO
HI all, i've posted about the above before but that thread has got so long and complicated (and this new post is about an earlier issue that I thought best to starting a new one)...
I am building an inventory system that shoukd match full name and date. Therefor add 1 to the number system when these two fields match. i have set up a related table (also based on artwork - which is where the entries are made).
I then have an inventory number with the following calculation.
Left(SURNAME;5) & DATE & "_" & Right ("00" & Inventory Number ;3)
The field "inventory number" does the following:
If ( IsEmpty (ARTWORK_SAME_ARTIST::s_Max_Inventory_Nr) ; 1 ; ARTWORK_SAME_ARTIST::s_Max_Inventory_Nr +1 )
The summary field "s_Max_Inventory_Nr" is the maxiumum of inventory number.
I am getting some strange results.
Basically when you enter a new name the following happens - when the artist name is the same the number stays on 001.
When you enter a date it jumps up to 29 for some reason. Even same artist name and same date stays on 29.
I know the ideal is to have artists in a related table and use their key but this database is not set up like that. Just want to keep is fairly simple but make it work.