Crtiteria From Table

[funderburgh]

I have a query that is selecting students from a student table, keyed with an id.

I want to only select students who are seniors in a certain year.

The grade level (i.e. senior, junior) is contained in another table with ID, Year and Grade Level fields and is related to the student table with a many to one relationship. Each year the student attends school their grade level for that year is entered.

I can't figure out how to code the criteria which would say "select the students whose grade level for *year* is equal to 12.

Any help is appreciated

 

[ajetrumpet]

use the QBE grid to make automatic joins. make sure the autojoin is turned on too in the options menu under TOOLS. join the two fields you spoke about, and the query should look smoething like this:

SELECT

table1.field1, table2.field1, etc, etc...

FROM table1 

INNER JOIN

table2 on field1 = field1

WHERE [yearfield] = "your criteria"

AND

[gradelevel] = "senior";

the grid will create the INNER JOINS automatically when you drag and drop fields from both tables in the grid to the field holders.

 

[funderburgh]

Thanks for the reply. That is not working for me. Also in this query are class records from a class table keyed to the student.

I am wanting to get class records for the students who were seniors in *year*

I am using 2007, so I don't have a TOOLS menu, but if I drag the grade level table into the QBE the relationship shows as it is set up between the tables.

The trouble is that the query then adds records to the result. None of the three relationship options available prevent this, i.e. add all records where id match, add all from table a and matching from table b or all from table ba and matching from table a.

I don't want to add records from the class level table to the query, I just want to use data from the table as criteria. This is where I am stuck.

 

[ajetrumpet]

upload your database and someone will look at it for you

 

[funderburgh]

Thanks but I am prohibited from doing that. It contains personal information on hundreds of students that is strictly confidential.

Thanks for your help

 

[boblarson]

Thanks but I am prohibited from doing that. It contains personal information on hundreds of students that is strictly confidential.

Thanks for your help

Then delete the information (I have a database reset tool for that) from the tables and upload the database shell.

OR

use my tool to scramble the data so no personal identifiable information is uploaded.

 

[funderburgh]

I should give Bob Larson half my pay.

In the process of creating a sample database to illustrate my problem, I solved it.

It pays to get simple.

Thank you

 

[boblarson]

I should give Bob Larson half my pay.

oooh, when do I get my check

In the process of creating a sample database to illustrate my problem, I solved it.

Glad to hear (although you might outline what you did so that others might learn if they suffer the same fate).

It pays to get simple.

Yep, that is true.