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
|
|||
|
|||
how to limit number of instances of a specific value in a column
Howdy.
I want to limit the number of instances of a specific value in a column in a table. I.e.: A Group can contain many Locations In any one Group there can only be one Location of type "Main Office" In any one Group there can be multiple instances of Locations of type "Satellite Office" I can't just create a unique index on the Type column... How do I do this? Thanks for your help. |
#2
|
|||
|
|||
how to limit number of instances of a specific value in a column
Post your table structure with field names and data type.
"Danny" wrote: Howdy. I want to limit the number of instances of a specific value in a column in a table. I.e.: A Group can contain many Locations In any one Group there can only be one Location of type "Main Office" In any one Group there can be multiple instances of Locations of type "Satellite Office" I can't just create a unique index on the Type column... How do I do this? Thanks for your help. |
#3
|
|||
|
|||
how to limit number of instances of a specific value in a colu
Columns
Name Type Size FK_GroupID Long Integer 4 LocationID Long Integer 4 OldCustomerNumber Long Integer 4 LocationType Long Integer 4 LocationName Text 255 URL Anchor - Comments Memo - "KARL DEWEY" wrote: Post your table structure with field names and data type. "Danny" wrote: Howdy. I want to limit the number of instances of a specific value in a column in a table. I.e.: A Group can contain many Locations In any one Group there can only be one Location of type "Main Office" In any one Group there can be multiple instances of Locations of type "Satellite Office" I can't just create a unique index on the Type column... How do I do this? Thanks for your help. |
#4
|
|||
|
|||
how to limit number of instances of a specific value in a column
On Fri, 12 Jun 2009 09:19:02 -0700, Danny
wrote: Howdy. I want to limit the number of instances of a specific value in a column in a table. I.e.: A Group can contain many Locations In any one Group there can only be one Location of type "Main Office" In any one Group there can be multiple instances of Locations of type "Satellite Office" I can't just create a unique index on the Type column... How do I do this? Thanks for your help. This isn't at all easy to do with field or table validation rules; you will probably need some VBA code in a Form's Beforeupdate or Beforeinsert event to programmatically enforce this business rule. What's the context? Is this information being entered using a form, imported from an external source, or what? -- John W. Vinson [MVP] |
#5
|
|||
|
|||
how to limit number of instances of a specific value in a colu
Hey John.
I think you've answered my question already - that it'll need to be done with code. I thought maybe there was some kind of index or relationship that I wasn't familiar with. Yep, this happens in the context of a form, at time of data entry. I suppose I could make some goofy one-to-one relationship with a table called "Main Office" or something???? And then a one-to-many with a table called "Satellite Offices"???? Just seems like this should be handle via structure, not code... "John W. Vinson" wrote: On Fri, 12 Jun 2009 09:19:02 -0700, Danny wrote: Howdy. I want to limit the number of instances of a specific value in a column in a table. I.e.: A Group can contain many Locations In any one Group there can only be one Location of type "Main Office" In any one Group there can be multiple instances of Locations of type "Satellite Office" I can't just create a unique index on the Type column... How do I do this? Thanks for your help. This isn't at all easy to do with field or table validation rules; you will probably need some VBA code in a Form's Beforeupdate or Beforeinsert event to programmatically enforce this business rule. What's the context? Is this information being entered using a form, imported from an external source, or what? -- John W. Vinson [MVP] |
#6
|
|||
|
|||
how to limit number of instances of a specific value in a colu
I was think along the lines of using DCount with which I am not at all
familiar. DCount (GroupID & IIF([LocationType]= 0, 1, Null)) 2 Zero being the number for "Main Office" "Danny" wrote: Hey John. I think you've answered my question already - that it'll need to be done with code. I thought maybe there was some kind of index or relationship that I wasn't familiar with. Yep, this happens in the context of a form, at time of data entry. I suppose I could make some goofy one-to-one relationship with a table called "Main Office" or something???? And then a one-to-many with a table called "Satellite Offices"???? Just seems like this should be handle via structure, not code... "John W. Vinson" wrote: On Fri, 12 Jun 2009 09:19:02 -0700, Danny wrote: Howdy. I want to limit the number of instances of a specific value in a column in a table. I.e.: A Group can contain many Locations In any one Group there can only be one Location of type "Main Office" In any one Group there can be multiple instances of Locations of type "Satellite Office" I can't just create a unique index on the Type column... How do I do this? Thanks for your help. This isn't at all easy to do with field or table validation rules; you will probably need some VBA code in a Form's Beforeupdate or Beforeinsert event to programmatically enforce this business rule. What's the context? Is this information being entered using a form, imported from an external source, or what? -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|