Selecting only one active record from multiple records

[M.L. Abram]

Hello all,

I do not know if this question is regarding table design, queries, or
programming. Below, I have given a table design using Access 2003.
Fields 'Product' and 'Color' are primary keys and 'Active'
is a Boolean data type. What I am trying to accomplish is having only
one record selected active out of the 'Product' field for each
type. If possible, have the user be able to do this task while viewing
the data within a continuous form.

Product Color Active
-----------------------------------------------
Shirt Blue
Shirt Yellow
Shirt Green True
Shirt Red
Shirt Black
Shirt Pink
Shirt White
Pants Blue
Pants Black True
Pants White
....

Thoughts or ideas?

TIA

M.L. Abram

 

[Joseph Meehan]

Hello all,

I do not know if this question is regarding table design, queries, or
programming. Below, I have given a table design using Access 2003.
Fields 'Product' and 'Color' are primary keys and 'Active'
is a Boolean data type. What I am trying to accomplish is having only
one record selected active out of the 'Product' field for each
type. If possible, have the user be able to do this task while
viewing the data within a continuous form.

Product Color Active
-----------------------------------------------
Shirt Blue
Shirt Yellow
Shirt Green True
Shirt Red
Shirt Black
Shirt Pink
Shirt White
Pants Blue
Pants Black True
Pants White
...

Thoughts or ideas?

TIA

M.L. Abram

I would suggest a button on the form that when clicked will set all the
active fields for that product to false then set just the selected field to
True.

Somehow I suspect this is going to be a problem with additional editing
unless it is not possible for a change to be made as long as you need that
choice to remain.