You'd have to have a 5th component as well, the location of the image each combination can be found on your network.
If you have that this is a relatively easy problem
Assume you have these tables:
Code:
TblTypes
TypeID TypeName
0 Truck
1 Sedan
2 Convertible
TblVehicles
VehicleID VehicleName
01 Dodge
02 Chrysler
03 Ford
TblImages
ImageID VehicleID TypeID ImagePath
1 01 0 Path 01XX0
2 01 1 Path 01XX1
3 01 2 Path 01XX2
4 02 0 Path 02XX0
5 02 1 Path 02XX1
6 02 2 Path 02XX2
7 03 0 Path 03XX0
8 03 1 Path 03XX1
9 03 2 Path 03XX2
TblIncomingCodes
IncomingCode
01540123456789
01971123456789
02341123456789
03272123456789
I made the incoming codes a table just for my ease.
If you make this query first:
Code:
SELECT Left([incomingcode],2) AS VID, Mid([incomingcode],5,1) AS TID, Left([incomingcode],2) & "XX" & Mid([incomingcode],5,1) & "XXXXXXXXX" AS Code
FROM TblIncomingCodes;
Name it Qry_Level1
then create this query
Code:
SELECT Qry_Level1.Code, TblImages.ImagePath
FROM Qry_Level1 LEFT JOIN TblImages ON (Qry_Level1.TID = TblImages.TypeID) AND (Qry_Level1.VID = TblImages.VehicleID);
It should give you the results you want.