need query (thinking) help

[Gary Walter]

Hello group,

I just cannot think this morning!

I have a table that contains a "course history"
of books used for each Term in each course
of those Terms.

A simplified example might be the following:

tblControlInfo

ID Term ISBN Title Course
1 P03 1 in P03 only a
2 P03 1 in P03 only b
3 P03 2 in both terms c
4 P03 2 in both terms d
5 P04 3 in P04 only e
6 P04 3 in P04 only f
7 P04 2 in both terms g
8 P04 2 in both terms h

The same book might be used in separate courses
in the same Term, so an ISBN might occur more
than once in a Term.

Obviously, this is a simple example and there
are "years" of Terms in the table.

A user will fill a table "tblInTerm" through a
form with Terms where the user wants to return
*all the books common to the Terms entered*
in a report.

If tblInTerm were:

ID Term
1 P04
2 P03

they would want to see:

ID Term ISBN Title Course
3 P03 2 in both terms c
4 P03 2 in both terms d
7 P04 2 in both terms g
8 P04 2 in both terms h

tblInTerm could contain only one Term,
or it could contain *many* Terms.

Without asking me to normalize my data
(I know ....#$%@!&), is there a general,
single query that would handle all cases?

It looks to me like I will have to always
reconstruct a query, "grouping by ISBN
returning ISBN's where all Terms in tblInTerm
are in the group," then return all fields from
tblControlInfo joined with tblInTerm on
Term and joined to the group-by-query on ISBN.

Maybe I am missing some simple thing?

Thanks,

Gary Walter

 

[Michel Walsh]

Hi,


I am not sure I follow completely, but I would start with


SELECT isbn
FROM ControlInfo INNER JOIN InTerm
ON ControlInfo.Term=InTerm.Term
GROUP BY ControlInfo.isbn
HAVING COUNT(*)=(SELECT COUNT(*) FROM InTerm)




That should return the ISBNs that qualify to be in each InTerm. That is the
same problem that Employee having Skills, and you would be looking for
employee having all the skills from a list of wanted skills.



Hoping it may help,
Vanderghast, Access MVP

 

[Gary Walter]

Thank you for the response Michel.
(my post never showed up here on my
computer, so I had no link to "reply to"
until now)

The problem with "Count" (initially) was that given
an InTerm with say 3 Terms, an ISBN might
be used by 3 (or more classes) in just one of
those Terms, and not in the other 2 Terms.
So its count would be 3 = count of Terms
in InTerm, but shouldn't be returned since
it wasn't used in the other 2 Terms.

I didn't have time yesterday at work to get
back to this problem, but I did (I believe)
work it out mentally to some satisfaction
by "divide-and-conquer."

qryCandidates

SELECT
C.Term, C.ISBN, C,Title, C.Course
FROM ControlInfo C INNER JOIN InTerm I
ON C.Term = I.Term

qryTermISBN

SELECT Q.Term, Q.ISBN
FROM qryCandidates Q
GROUP BY Q.Term, Q.ISBN

qryISBNinAllTerms (now the "count")

SELECT TI.ISBN
FROM qryTermISBN TI
GROUP BY TI.ISBN
HAVING COUNT(*)=(SELECT COUNT(*) FROM InTerm)

Then the final query joins qryCandidates
with qryISBNinAllTerms on ISBN.

It all seems so clear now, but I was really
doubting myself yesterday.

I haven't fully thought through the second part of the
"real problem" yet (there will also be a "NotInTerm"
table), but things are more in focus today thankfully.

Thanks again Michel for taking the time to respond.

If I had just had a post to respond to, I would have sent
an "ignore" message so you would not waste your time.

That does not mean I do not appreciate (as always)
any perspective you bring to the table!

Gary Walter

 

[Michel Walsh]

Hi,


You are right, have you tried :


SELECT isbn
FROM
(SELECT DISTINCT Term, ISBN FROM ControlInfo) As x
INNER JOIN InTerm ON x.Term=InTerm.Term

GROUP BY x.isbn
HAVING COUNT(*)=(SELECT COUNT(*) FROM InTerm)




Vanderghast, Access MVP




(...)

The problem with "Count" (initially) was that given
an InTerm with say 3 Terms, an ISBN might
be used by 3 (or more classes) in just one of
those Terms, and not in the other 2 Terms.
So its count would be 3 = count of Terms
in InTerm, but shouldn't be returned since
it wasn't used in the other 2 Terms.

(...)

 

[Gary Walter]

Yup.

That be what I grappled with to think out.

ControlInfo is such a large table though,
I did think it was worth it to include InTerm
inside the Distinct subquery, thinking (rightly
or wrongly) that applying DISTINCT over
a smaller set would be quicker. Our main FE
db computer is only a "disk-thrashing" Celeron
433 Mhz/192MB (and part of that memory is
used by onboard video!).

SELECT isbn
FROM
(SELECT DISTINCT C.Term, C.ISBN FROM ControlInfo C
INNER JOIN InTerm ON C.Term=InTerm.Term) x

GROUP BY x.isbn
HAVING COUNT(*)=(SELECT COUNT(*) FROM InTerm)

Thank you again for your responses.

Gary Walter