Limiting query results

  • Thread starter Thread starter Paige
  • Start date Start date
P

Paige

I have a db where I track student fees for sports,
activities, and such. Each student has four fields for
sport codes, as that is the highest number a student
could theoretically participate in. (There are 18
possible sports to choose from. They are stored in
another table along with their description, fee amount,
etc. I am only storing the code for each student.)

In my query, the user is prompted for the sport code that
they want to return students for. This query searches
all four sport fields for the student. It always returns
the correct information, so it works just fine. However
I do not want to return information for other sport codes
that are stored. (Example: 311 is the code for
football. Student A has 311 stored in Sport1. Student B
has 311 stored in Sport2 because they are also in another
sport coded 315, which was stored first in Sport1.) What
I would like to have happen is to only return fields that
contain the matching criteria. It is not possible for a
code to appear in more than one field, so that is not an
issue.

I know this should be a very simple process, but my
working knowledge in this aspect of Access simply isn't
up to snuff. Any ideas would be appreciated.

Thanks.
-Paige
 
Actually, what you want to do is not simple -- because of how you've
designed your table structure. By having a nonnormalized table (fields named
as Sport1, Sport2, etc., meaning that the fieldname itself conveys/stores
data information), your query must look at each of the four fields, but a
query cannot decide whether to include a field or not -- that is determined
by the query's design, which either does or does not contain a field.

If you had a table such as tblStudentSport:
StudentID
SportID

Then you could search a single field (SportID) to find the matching values
and then your query would look like this:
SELECT Students.StudentName, tblStudentSport.SportID
FROM Students INNER JOIN tblStudentSport
ON Students.StudentID = tblStudentSport.StudentID
WHERE tblStudentSport.SportID = [Enter the ID of the sport:];


Because of your nonnormalized table structure, you'll need to use union
query to put all the separate fields "together" and then run your desired
query. First, create a query (named qrySports) by opening a new query,
selecting no tables, and then closing the table selection window. Then click
on SQL icon (top left of toolbar) to go to SQL view. Type the following SQL
in the window (replace my generic names with your real names):
SELECT StudentID, Sport1 FROM TableName
UNION ALL
SELECT StudentID, Sport2 FROM TableName
UNION ALL
SELECT StudentID, Sport3 FROM TableName
UNION ALL
SELECT StudentID, Sport4 FROM TableName;

Save and close this query.

Then create a new query:
SELECT Students.StudentName, qrySports.Sport1
FROM Students INNER JOIN qrySports
ON Students.StudentID = qrySports.StudentID
WHERE qrySports.SportID = [Enter the ID of the sport:];

This last query should show you what you want to see.
--

Ken Snell
<MS ACCESS MVP>
 
Thanks Ken. I think I have made that mistake before, now
that you describe the "correct" solution. Thanks for the
workaround, it is appreciated (and works fine for what we
need it to do.) Next time will get it right from the get
go.

-paige

-----Original Message-----
Actually, what you want to do is not simple -- because of how you've
designed your table structure. By having a nonnormalized table (fields named
as Sport1, Sport2, etc., meaning that the fieldname itself conveys/stores
data information), your query must look at each of the four fields, but a
query cannot decide whether to include a field or not -- that is determined
by the query's design, which either does or does not contain a field.

If you had a table such as tblStudentSport:
StudentID
SportID

Then you could search a single field (SportID) to find the matching values
and then your query would look like this:
SELECT Students.StudentName, tblStudentSport.SportID
FROM Students INNER JOIN tblStudentSport
ON Students.StudentID = tblStudentSport.StudentID
WHERE tblStudentSport.SportID = [Enter the ID of the sport:];


Because of your nonnormalized table structure, you'll need to use union
query to put all the separate fields "together" and then run your desired
query. First, create a query (named qrySports) by opening a new query,
selecting no tables, and then closing the table selection window. Then click
on SQL icon (top left of toolbar) to go to SQL view. Type the following SQL
in the window (replace my generic names with your real names):
SELECT StudentID, Sport1 FROM TableName
UNION ALL
SELECT StudentID, Sport2 FROM TableName
UNION ALL
SELECT StudentID, Sport3 FROM TableName
UNION ALL
SELECT StudentID, Sport4 FROM TableName;

Save and close this query.

Then create a new query:
SELECT Students.StudentName, qrySports.Sport1
FROM Students INNER JOIN qrySports
ON Students.StudentID = qrySports.StudentID
WHERE qrySports.SportID = [Enter the ID of the sport:];

This last query should show you what you want to see.
--

Ken Snell
<MS ACCESS MVP>




I have a db where I track student fees for sports,
activities, and such. Each student has four fields for
sport codes, as that is the highest number a student
could theoretically participate in. (There are 18
possible sports to choose from. They are stored in
another table along with their description, fee amount,
etc. I am only storing the code for each student.)

In my query, the user is prompted for the sport code that
they want to return students for. This query searches
all four sport fields for the student. It always returns
the correct information, so it works just fine. However
I do not want to return information for other sport codes
that are stored. (Example: 311 is the code for
football. Student A has 311 stored in Sport1. Student B
has 311 stored in Sport2 because they are also in another
sport coded 315, which was stored first in Sport1.) What
I would like to have happen is to only return fields that
contain the matching criteria. It is not possible for a
code to appear in more than one field, so that is not an
issue.

I know this should be a very simple process, but my
working knowledge in this aspect of Access simply isn't
up to snuff. Any ideas would be appreciated.

Thanks.
-Paige
Click to expand...


.
Click to expand...
 
Back
Top