If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
alpha AND numeric, best way to set up
Hi -
I want to establish a car registry with just enough exceptions to create problems. The VIN field can be "unique" -- almost. VINs are generally 1 to 4 digit numbers, to be reported out in numerical order, but with some duplicates, that can be made unique with trailing alpha characters. Here are some VINs unk101 -- (this is the 101st car found with an unknown original vin) 101 125 254 1254 1254Replicar These would all be different cars In my one (car) to many (owners, past and present), I would search for 1254, but need be able to see that there are TWO 1254 cars, from which I would have to chose the one to work with. Should I use a single TEXT field as my VIN and deal with the sorting issues? That is, I can ( but would rather not) add a leading zero to my 3 digit vins, and then strip it out in reports. Or should I try two fields that combined become "unique" - 80% of the VINS are not duplicated. While there likely are several ways to get the job done, I am looking for the one with the least issues later on in adding cars and owners and reporting sorted lists in the right order. Thanks in advance for any thoughts. Don |
#2
|
|||
|
|||
alpha AND numeric, best way to set up
My preference would be to use 2 fields:
- a Number field, properties: Size: Integer (since it's only 4 digits) Required Yes - a Text field, properties: Required Yes Allow Zero Length Yes Default Value "" This blocks nulls in both fields, but the VinSuffix (Text) field accepts a zero-length string (ZLS), and that's the value you get unless the user types something there. Create a unique index on the combination of the 2 fields. For any VinNumber, you can have only one record where the VinSuffix is a ZLS. It's easy to sort on the combination of the two, and to concatenate the two (e.g. to print on a report.) I think that would be more efficient than using one Text field and setting up your query to: ORDER BY Val([VIN]), VIN -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Don" wrote in message news Hi - I want to establish a car registry with just enough exceptions to create problems. The VIN field can be "unique" -- almost. VINs are generally 1 to 4 digit numbers, to be reported out in numerical order, but with some duplicates, that can be made unique with trailing alpha characters. Here are some VINs unk101 -- (this is the 101st car found with an unknown original vin) 101 125 254 1254 1254Replicar These would all be different cars In my one (car) to many (owners, past and present), I would search for 1254, but need be able to see that there are TWO 1254 cars, from which I would have to chose the one to work with. Should I use a single TEXT field as my VIN and deal with the sorting issues? That is, I can ( but would rather not) add a leading zero to my 3 digit vins, and then strip it out in reports. Or should I try two fields that combined become "unique" - 80% of the VINS are not duplicated. While there likely are several ways to get the job done, I am looking for the one with the least issues later on in adding cars and owners and reporting sorted lists in the right order. Thanks in advance for any thoughts. Don |
#3
|
|||
|
|||
alpha AND numeric, best way to set up
tblBillets tblEmployees
Slot(PK - autonumber) EmpID(PK) Instance Name Job Desc SlotID(FK) This was working fine, but alas, I was short-sighted and now need to split my billets for part-time workers. I found your response and thought this was the solution, but I hit a snag right away. I have several forms that have an unbound box which runs a query of the Billet table to load a particular record. I changed the query to find [Slot]&[Instance] and added this code to what I had in the AfterUpdate event (which had been working fine): strRight = Right(Me.cboSlot, 1) ctleft = Len(Me.cboSlot) - 1 strLeft = Left(Me.cboSlot, ctleft) If Not IsNull(Me.cboSlot) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst ("[Slot] = " & strLeft And "[Instance] = " & strRight) If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If I get a type mismatch error. I also see I will need to change the FK in tblEmployees. I am sure there is a better way to do this. Any suggestions? ..... -- Teach me to fish! Thanks for the help. Pax, M "Allen Browne" wrote: My preference would be to use 2 fields: - a Number field, properties: Size: Integer (since it's only 4 digits) Required Yes - a Text field, properties: Required Yes Allow Zero Length Yes Default Value "" This blocks nulls in both fields, but the VinSuffix (Text) field accepts a zero-length string (ZLS), and that's the value you get unless the user types something there. Create a unique index on the combination of the 2 fields. For any VinNumber, you can have only one record where the VinSuffix is a ZLS. It's easy to sort on the combination of the two, and to concatenate the two (e.g. to print on a report.) I think that would be more efficient than using one Text field and setting up your query to: ORDER BY Val([VIN]), VIN -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Don" wrote in message news Hi - I want to establish a car registry with just enough exceptions to create problems. The VIN field can be "unique" -- almost. VINs are generally 1 to 4 digit numbers, to be reported out in numerical order, but with some duplicates, that can be made unique with trailing alpha characters. Here are some VINs unk101 -- (this is the 101st car found with an unknown original vin) 101 125 254 1254 1254Replicar These would all be different cars In my one (car) to many (owners, past and present), I would search for 1254, but need be able to see that there are TWO 1254 cars, from which I would have to chose the one to work with. Should I use a single TEXT field as my VIN and deal with the sorting issues? That is, I can ( but would rather not) add a leading zero to my 3 digit vins, and then strip it out in reports. Or should I try two fields that combined become "unique" - 80% of the VINS are not duplicated. While there likely are several ways to get the job done, I am looking for the one with the least issues later on in adding cars and owners and reporting sorted lists in the right order. Thanks in advance for any thoughts. Don |
Thread Tools | |
Display Modes | |
|
|