I have set up a database that I want to function as a dataplate decoder for three model years for a specific make of automobile. The dataplate contains the Vehicle Identification Number (VIN) and a number of codes that define how the car was equipped.
The first 5 characters of the VIN denotes the model year (1st), the manufacturing plant (2nd), the body style (3rd & 4th), and the engine (5th). The remaining six digits denote the sequential production number. Several of these codes are the same from one year to another.
I have set up a table for each code (a total of ten). Each table that has duplicate information (all but three) has the model year, the code, and the description that the code denotes. Two of the remaining tables contain only the code and the description, as this information did not change during the three years this car was manufactured. The last remaining table has a code for the model year, and the 4-digit description of that year (i.e. Code=3, Description=1963).
I would like to be able to run a query where the user selects the characters in his dataplate from an array of drop-down lists, and the result shows the description of what each code means. For the most part, it will be a simple lookup process, but some of the codes will change depending on the model year, which is the first charachter of the VIN. The six-digit sequential production number is the only thing that is certain to be unique for each model year. I have no interest in saving the information, it will just be a reference tool.
Can anyone help with this? Thanks.