Yes/No Querry

  • Thread starter Thread starter Fred Peters
  • Start date Start date
F

Fred Peters

I'm setting up an awards database. I'll have 30 people and 50 awards. I
need a querry to select LastName and only pull up the checkboxs that are
checked yes. For instance, Jon has 6 awards out of the 50 possible. I only
want to see Jon's name and the 6 that are checked yes and not show the other
44 awards that aren't checked.

Thanks in advance for your help
 
I believe your table structure is totally wrong (I think I recently posted
this to another question you asked somewhere).
I would setup a table of People, a table of possible awards (50 records at
the moment), and a table of PeopleAwards. The PeopleAwards table is called a
Junction table and will contain one record for each award that is given to
an individual. You can add more awards (more than 50) without changing table
structures, forms,...

Your answer becomes a simple select query of these tables.
 
Thankyou. I understand the concept so far. I just finished last Tuesday a
two day class for Access. So, I'm not the brightest color in the colorbox.
Anyway, I appreciate the help you're giving me.
My question now is, I now have tblPossibleAwds, tblPeopleAwds, and Employees.
I have the relationship 1 to many for them. The tblPossibleAwds have all
the unchecked awards in it. The question now is how do I, once checked in
tblPossibleAwds, get them in tblPeopleAwds? Just a simple querry?..
Again thanks for the help. Fred
 
This is what your sturcture should look like.
Employees--
EmpID - autonumber - Primary Key
LName - text
FName - text
etc

tblPossibleAwds--
AwdID - autonumber - Primary Key
AwardTitle - text

tblPeopleAwds
AwdID - number - long interger
EmpID -number - long interger

Set one-to-many relations from Employees.EmpID to tblPeopleAwds.EmpID.
Set one-to-many relations from tblPossibleAwds.AwdID to tblPeopleAwds.AwdID.
 
Thanks Karl. All of this is implemented with main and subforms like the
Orders and Order Details in Northwind.
 
I did like you said. I have three separate tables. I padded the first record with 3 yes c=
hecks and the 2nd record I padded with 10 checks yes.

tbl_Employees - EmployeeID =3D(AutoNumber) plus Lname, Fname etc.

tbl_rctrAwards - AwardsID =3D (AutoNumber), EmployeeID =3D(Number) plus the 50 aw=
ards yes/no checkbox

tbl_rctrAwardsYes - EmployeeID =3D(Number) and AwardsID =3D(Number)

Relationship: tbl_Employees 1 to many tbl_rctrAwards

Relationship tbl_rctrAwards 1 to many tbl_rctrAwardsYes

I then did a simple querry called it qryYesAwardResult
=

tbl_rctrAwardsYes with EmployeeID and AwardsID tbl_rctrAwards with all the 50 awa=
rds.

When I run it, I get nothing.

Help please=E2=80=A6I just hope I=E2=80=99m not bugging you too much. I suppose I ha=
ve a heard head and it takes a little more beating to get it in me. Thanks again.
 
My structure does not use checkboxes.

The tblPossibleAwds like this--
AwdID - autonumber - Primary Key
AwardTitle - text

The data in it would be --
1 Never late
2 Happy
3 Never sick
4 Shined shoes

The data in tblPeopleAwds would look lkie --
AwdID - number - long interger
EmpID -number - long interger

1 1
3 1
1 2

This says employee 1 has awards 1 and 3. Employee 2 has award 1.

For data entry use a main form for employees and subform, datasheet, for the
awards the employees have.
 
Back
Top