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
|
|||
|
|||
automatically create primary key by concatonation?
In my table tblLandLocations I want to create a field that I would use as a
primary key, but that takes information entered into other fields and then concatonates them into a string which I would then use as a primary key. I'm not sure if this is even possible, since each record would need to refer to itself to create the entry for the primary key field. Example. Three fields: [LandownerID], [LandLocation], [ManagementUnit] In my form, the user chooses the landowner by name from a combo box, and the LandownerID (an autonumber) is stored in this table. They enter a new information for [LandLocation] and [ManagementUnit]. What I want, is to create an unique identifier from this, say LandLocationID: [LandownerID] & "_" & [LandLocation] & "_" [ManagementUnit] This would then serve as a primary key (or at the very least, just another field but with no duplicates allowed) to check that no land locations per landowner and management unit have been entered more than once. Is this possible? Or would I have to use a query to concatonate the strings, and go from there? (and where do I go from there?) Thanks for any help, I hope it's not too confusing! |
#2
|
|||
|
|||
automatically create primary key by concatonation?
Sienna
Step back for a second... If you are saying that you have (?3) fields that, taken together, provide unique identification for each row, then you DON'T need to save all three a second time ("concatenated into a string") just to use them as a unique identification. Instead, in design view, highlight all three fields, then click the primary key button to make the combination of the three your primary key. Regards Jeff Boyce Microsoft Office/Access MVP "Sienna" wrote in message ... In my table tblLandLocations I want to create a field that I would use as a primary key, but that takes information entered into other fields and then concatonates them into a string which I would then use as a primary key. I'm not sure if this is even possible, since each record would need to refer to itself to create the entry for the primary key field. Example. Three fields: [LandownerID], [LandLocation], [ManagementUnit] In my form, the user chooses the landowner by name from a combo box, and the LandownerID (an autonumber) is stored in this table. They enter a new information for [LandLocation] and [ManagementUnit]. What I want, is to create an unique identifier from this, say LandLocationID: [LandownerID] & "_" & [LandLocation] & "_" [ManagementUnit] This would then serve as a primary key (or at the very least, just another field but with no duplicates allowed) to check that no land locations per landowner and management unit have been entered more than once. Is this possible? Or would I have to use a query to concatonate the strings, and go from there? (and where do I go from there?) Thanks for any help, I hope it's not too confusing! |
#3
|
|||
|
|||
automatically create primary key by concatonation?
On Fri, 5 Jun 2009 13:09:01 -0700, "Jeff Boyce"
wrote: Instead, in design view, highlight all three fields, then click the primary key button to make the combination of the three your primary key. Or, instead of making a composite primary key, use an AutoNumber for your primary key, and include these three fields in a unique index to prevent duplicates. Armen Stein Microsoft Access MVP www.JStreetTech.com |
Thread Tools | |
Display Modes | |
|
|