Query challenge

  • Thread starter Thread starter Mark Burns
  • Start date Start date
M

Mark Burns

Problem: Students need books, but the book list for a course changes over
time.
How to identify which students need to buy which (new) books?

2 tables:
[Student Supplies List](SSL) - contains the books list assigned by program
of study
[Student Supply Purchasing List](SSPL) - contains student IDs and purchasing
data for books for student's program of study

Sample Data:
[SSL]
<rec>
<Program> MA
<BookID> 221
<Book Price> $34.95
<Book Title> "How to Blah Blah..."
<etc.,.>
</rec>
<rec>
<Program> MA
<BookID> 222
<Book Price> $14.95
<Book Title> "How to Blah de Blah..."
<etc.,.>
</rec>
<rec>
<Program> MA
<BookID> 295
<Book Price> $24.95
<Book Title> "How to whatever..."
<etc.,.>
</rec>

[SSPL]
<rec>
<Program> MA
<StudentID> "34-234"
<BookID> 221
<Ordered> True
<Date Ordered> #1/1/03#
<etc.,.>
</rec>
<rec>
<Program> MA
<StudentID> "34-234"
<BookID> 222
<Ordered> True
<Date Ordered> #3/1/03#
<etc.,.>
</rec>

How do I construct a query to show me that Student 34-234 needs to still buy
the new book ID 295?
I tried a double-right-join on [SSL],Program -> [SSPL].Program and
[SSL].BookID ->[SSPL]->BookID, but Access XP coughs up an ambiguous outer
joins error message. ??
 
Hi Mark,

1) query to find books each student needs

qryAllBooksNeeded:

SELECT
DISTINCT
SSPL.StudentID,
SSPL.Program,
SSL.BookID,
SSL.[Book Price],
SSL.[Book Title]
FROM SSPL
INNER JOIN
SSL
ON
SSPL.Program = SSL.Program;


2) query to find books each student needs:

SELECT
Q.StudentID,
Q.Program,
Q.BookID,
Q.[Book Price],
Q.[Book Title]
FROM
qryAllBooksNeeded AS Q
LEFT JOIN
SSPL
ON
(Q.StudentID = SSPL.StudentID)
AND
(Q.Program = SSPL.Program)
AND
(Q.BookID = SSPL.BookID)
WHERE (((SSPL.BookID) Is Null));
 
Back
Top