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.

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.

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.

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]

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]

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]