I would like to select query criteria from data in another table

[johnb80]

I am working on a school database. One of the tables contains a list of
pupils and the teaching groups that they belong to. In order to asses their
progress we need to enter data regarding the level they are working at. I
need to be able to present all of the pupils in a particular group to a
member of staff by the staff member selecting from a drop down box the
teaching group they wish to view / ammend. The list of teaching groups can be
selected from a different file or preferably from the pupil data file. If
it's selected from the pupil data file there will be multiple entries of the
same teaching group so whatever method would have to list only one.

I assume using Access query is the right method and the selection data to be
put into criteria.

Any help gratefully received.

Regards - John

 

[JR]

You can use your pupil data file for the list of teaching groups and list
each group only once with the help of the distinct keyword.

SELECT DISTINCT [TEACHING GROUP] FROM [PUPILS DATA FILE] ORDER BY [TEACHING
GROUP]

use this as the source for your drop down and use the data in the drop down
for your query criteria for your report.

Hope this helps
JR

 

[johnb80]

Hi JR,
Many thanks for your prompt reply, as a newbie to access I entered in
criteria as you put in your mail, unfortunately it reports syntax error for
the sub query. I know it's going to be me, am I correct in entering your
suggested solution in the query criteria box in design view?

Once again many thanks.

Kind Regards - John
P.S. We are using access 2003 if that makes any difference.

You can use your pupil data file for the list of teaching groups and list
each group only once with the help of the distinct keyword.

SELECT DISTINCT [TEACHING GROUP] FROM [PUPILS DATA FILE] ORDER BY [TEACHING
GROUP]

use this as the source for your drop down and use the data in the drop down
for your query criteria for your report.

Hope this helps
JR

I am working on a school database. One of the tables contains a list of
pupils and the teaching groups that they belong to. In order to asses their
progress we need to enter data regarding the level they are working at. I
need to be able to present all of the pupils in a particular group to a
member of staff by the staff member selecting from a drop down box the
teaching group they wish to view / ammend. The list of teaching groups can be
selected from a different file or preferably from the pupil data file. If
it's selected from the pupil data file there will be multiple entries of the
same teaching group so whatever method would have to list only one.

I assume using Access query is the right method and the selection data to be
put into criteria.

Any help gratefully received.

Regards - John