how to create a query.

[jaYPee]

I'm so sorry for posting this message again 'cause there's a lot of
error in my previous posting...

I'm having problem w/ my query on how to filter a record based on the
criteria i give.

here's the tables

students table
id
program

course table
courseid
id
program
course

for example if i have this data on my table

id students.program course.program course
1 BEED BSED FIL 2
2 BEED FIL 2
3 BEED ENG 2
4 BSED FIL 2

based on the above example. if i want to query a course field = FIL 2
and if program = BSED the result should be:

id students.program course.program course
1 BEED BSED FIL 2
4 BSED FIL 2

but if i query course field = FIL 2 and program = BEED the result
should be:

id students.program course.program course
2 BEED FIL 2

the point here is that if course.program field is not empty the
criteria is based in course.program but if it is empty the criteria is
based on students.program.

hope i explained it well.

[Jeff Leckrone]

Try this. In your query make a column that has the following as is heading:

Prog: IIf(IsNull([Courses].[Program]),[Students].[Program],[Courses].[Program])

This should give you the results you want when you put in the criteria.
jaYPee <hi******@yahoo.com> wrote in message news:<ij********************************@4ax.com>. ..

I'm so sorry for posting this message again 'cause there's a lot of
error in my previous posting...

I'm having problem w/ my query on how to filter a record based on the
criteria i give.

here's the tables

students table
id
program

course table
courseid
id
program
course

for example if i have this data on my table

id students.program course.program course
1 BEED BSED FIL 2
2 BEED FIL 2
3 BEED ENG 2
4 BSED FIL 2

based on the above example. if i want to query a course field = FIL 2
and if program = BSED the result should be:

id students.program course.program course
1 BEED BSED FIL 2
4 BSED FIL 2

but if i query course field = FIL 2 and program = BEED the result
should be:

id students.program course.program course
2 BEED FIL 2

the point here is that if course.program field is not empty the
criteria is based in course.program but if it is empty the criteria is
based on students.program.

hope i explained it well.

[James Fortune]

jaYPee <hi******@yahoo.com> wrote in message news:<ij********************************@4ax.com>. ..

students table
id
program

course table
courseid
id
program
course

for example if i have this data on my table

id students.program course.program course
1 BEED BSED FIL 2
2 BEED FIL 2
3 BEED ENG 2
4 BSED FIL 2

based on the above example. if i want to query a course field = FIL 2
and if program = BSED the result should be:

id students.program course.program course
1 BEED BSED FIL 2
4 BSED FIL 2

but if i query course field = FIL 2 and program = BEED the result
should be:

id students.program course.program course
2 BEED FIL 2

A simple technique that avoids IIF is:

SELECT students.id, students.program, course.program, course FROM
students INNER JOIN course ON students.id = course.id WHERE
((students.program='BEED') AND (course.program Is Null)) OR
((course.program='BEED'));

Including a criteria for course would look something like:

SELECT students.id, students.program, course.program, course FROM
students INNER JOIN course ON students.id = course.id WHERE
((students.program='BEED') AND (course.program Is Null) AND
(course.course='ENG 2')) OR ((course.program='BEED') AND
(course.course='ENG 2'));

Test Results using your data:

SELECT students.id, students.program, course.program, course.course
FROM students INNER JOIN course ON students.id = course.id
WHERE (((students.program)='BSED') AND ((course.program) Is Null) AND
((course.course)="FIL 2")) OR (((course.program)='BSED') AND
((course.course)="FIL 2"));

produced:

1 BEED BSED FIL 2
4 BSED FIL 2

SELECT students.id, students.program, course.program, course.course
FROM students INNER JOIN course ON students.id = course.id
WHERE (((students.program)='BEED') AND ((course.program) Is Null) AND
((course.course)="FIL 2")) OR (((course.program)='BEED') AND
((course.course)="FIL 2"));

produced:

2 BEED FIL 2

The technique is still the same if I have misread how you are doing
the join.

James A. Fortune

[jaYPee]

Thank you very much. it works.

I need to put the is null in the criteria...
:)

jaYPee

On 16 Nov 2004 13:06:59 -0800, ja******@oakland.edu (James Fortune)
wrote:

jaYPee <hi******@yahoo.com> wrote in message news:<ij********************************@4ax.com>. ..

students table
id
program

course table
courseid
id
program
course

for example if i have this data on my table

id students.program course.program course
1 BEED BSED FIL 2
2 BEED FIL 2
3 BEED ENG 2
4 BSED FIL 2

based on the above example. if i want to query a course field = FIL 2
and if program = BSED the result should be:

id students.program course.program course
1 BEED BSED FIL 2
4 BSED FIL 2

but if i query course field = FIL 2 and program = BEED the result
should be:

id students.program course.program course
2 BEED FIL 2

A simple technique that avoids IIF is:

SELECT students.id, students.program, course.program, course FROM
students INNER JOIN course ON students.id = course.id WHERE
((students.program='BEED') AND (course.program Is Null)) OR
((course.program='BEED'));

Including a criteria for course would look something like:

SELECT students.id, students.program, course.program, course FROM
students INNER JOIN course ON students.id = course.id WHERE
((students.program='BEED') AND (course.program Is Null) AND
(course.course='ENG 2')) OR ((course.program='BEED') AND
(course.course='ENG 2'));

Test Results using your data:

SELECT students.id, students.program, course.program, course.course
FROM students INNER JOIN course ON students.id = course.id
WHERE (((students.program)='BSED') AND ((course.program) Is Null) AND
((course.course)="FIL 2")) OR (((course.program)='BSED') AND
((course.course)="FIL 2"));

produced:

1 BEED BSED FIL 2
4 BSED FIL 2

SELECT students.id, students.program, course.program, course.course
FROM students INNER JOIN course ON students.id = course.id
WHERE (((students.program)='BEED') AND ((course.program) Is Null) AND
((course.course)="FIL 2")) OR (((course.program)='BEED') AND
((course.course)="FIL 2"));

produced:

2 BEED FIL 2

The technique is still the same if I have misread how you are doing
the join.

James A. Fortune