in table2 i have
id, colour
1 red
2 blue
3 green
4 black
5 white
6 yellow
and so on
in table1 i have
name, favourite colour
person1 (red,white)
person2 (blue,green,black)
person3 (red)
My problem is that i want to have a checkbox to registrate with
favourite colours a person have. I manage so they can choice ONE
colour, but how to do if I want them to have the choice selecting 0-6
colours?? And how do i save this in the table??
Here's a possible way to do it:
tblColours
ColourID AutoNumber
ColourName Text
tblPeople
PID AutoNumber
LastName Text
FirstName Text
MiddleName Text
tblFavouriteColours
FCID AutoNumber
PID Long FK
ColourID Long FK
Ranking Long
tblColours
1 red
2 blue
3 green
4 black
5 white
6 yellow
7 and so on
tblPeople
1 Smith June A
2 Jones Jane Earl
3 Van Winkle Rita Ingersoll-Pearce
tblFavoriteColours
1 1 2 1
2 1 3 2
3 1 6 3
4 2 1 1
5 2 4 2
6 3 3 1
7 3 5 2
8 3 7 3
A way to collate this information is with a crosstab query:
TRANSFORM Ranking SELECT FirstName + IIf(FirstName IS NOT NULL, ' ', '')
+ MiddleName + IIf(MiddleName IS NOT NULL, ' ', '') + LastName AS
theName FROM (tblFavouriteColours INNER JOIN tblPeople ON
tblFavouriteColours.PID = tblPeople.PID) INNER JOIN tblColours ON
tblFavouriteColours.ColourID = tblColours.ColourID GROUP BY FirstName +
IIf(FirstName IS NOT NULL, ' ', '') + MiddleName +IIf(MiddleName IS NOT
NULL, ' ', '') + LastName, tblFavouriteColours.Ranking PIVOT
tblColours.ColourName;
theName [and so on] black blue green red white yellow
Jane Earl Jones Null Null Null Null 1 Null Null
Jane Earl Jones Null 2 Null Null Null Null Null
June A Smith Null Null 1 Null Null Null Null
June A Smith Null Null Null 2 Null Null Null
June A Smith Null Null Null Null Null Null 3
Rita Ingersoll-Pearce Van Winkle Null Null Null 1 Null Null Null
Rita Ingersoll-Pearce Van Winkle Null Null Null Null 2 Null
Rita Ingersoll-Pearce Van Winkle 3 Null Null Null Null Null Null
A better approach might be to query tblFavouriteColours directly.
JOIN's can be used as needed to substitute names for ID's. For example:
SELECT COUNT(*) AS FavouriteColourBlueCount, (SELECT COUNT(*) FROM
tblPeople) AS PeopleSampleCount FROM tblFavouriteColours WHERE Ranking =
1 AND ColourID = 2;
FavouriteColourBlueCount PeopleSampleCount
1 3
can also be done with:
SELECT Count(*) AS FavouriteColourBlueCount, (SELECT COUNT(*) FROM
tblPeople) AS PeopleSampleCount FROM (tblFavouriteColours INNER JOIN
tblColours ON tblFavouriteColours.ColourID = tblColours.ColourID) INNER
JOIN tblPeople ON tblFavouriteColours.PID = tblPeople.PID WHERE
Ranking=1 AND tblColours.ColourName='Blue';
That structuring of the tables allows you to have even more than 0-6
colours. I guess putting someone in tblPeople without entering a record
in tblFavouriteColours would mean 0 colours were chosen. Having
checkboxes in tandem with a listbox can be used to select an order of
colour favourites. I have done something similar with choosing fields
for flexible reports so post back if you need pointers on how to do
that. I remember that the technique is not very elegant but using that
user interface impresses users. One record will be created in
tblFavouriteColours for each colour selected to the listbox. If the
list of available colours will be changing often, consider using a
subform to hold the colours and their checkboxes.
James A. Fortune
(e-mail address removed)
