Many to Many Headaches

  • Thread starter Thread starter TonyT
  • Start date Start date
T

TonyT

I have a small database for keeping track of Images with the basic structure;

tblImg
ImgID
ImgName
ImgPath
ImgSettingNum
ImgLocationNum
etc etc

tblSetting
SettingID <-> ImgSettingNum
SettingDesc

tblLocation
LocationID <-> ImgLocationNum
LocationDesc

tblSubject
SubjectID
SubjectDesc

tblLnkSubj
LnkSubjectNum <-> SubjectID
LnkImgNum <-> ImgID

I can't seem to create a query that will give me a recordset containing
(locationDescA OR locationDescB) AND (SettingDescA OR SettingDescB OR
SettingDescC) <= this much is no problem AND (SubjectDesc AND SubjectDesc
AND.....) where SubjectDesc is drawn from a multiselect listbox giving an
unknown length of selections.

I have been trying with a subquery, which works fine for SubjectDesc OR
SubjectDesc....., but can't get my head round SubjectDesc AND
SubjectDesc..... from the link/junction table, seems I need a way to have an
array in a field.

Any pointers?
 
Tony, I'm not sure about which are the key fields here, but you probably
want a unique index in the link table on the combination of fields. For
example, in tblLukSubject, make a unique index on SubjectID + ImgID (so one
image can't have 2 records for the same subject.)

If you do that, you can then handle your ANDs by counting the number of
matching records. For example, to find the images that have subject 4 AND
subject 5 AND subject 9 (i.e. the images associated with all 3 subjects):
SELECT tblLnkSubj.ImgID
FROM tblLnkSubj
WHERE SubjectID IN (4,5,9)
GROUP BY tblLnkSubj.ImgID
HAVING Count(tblLnkSubj.ImgID) = 3;

Suqueries are great, and here's more on that:
http://allenbrowne.com/subquery-01.html

But the idea of grouping counting the number of matches is also useful.
 
thanks Allen,

That does indeed give me all the correct records containing all images with
ALL the subject matters in, however I need that combined into a single query
(for the recordsource of a search form) so I can also sort the Location/s and
Setting/s whose FK's are in the tblImg & that's the bit I really can't figure.

(excuse my naming convention of having PK's as xxxxxxID and FK's as
xxxxxxNum just seemed simpler when I started out giving me less confusion in
queries.)

The Link table has a separate PK autonumber field (due to a db design
change), but the Insert code prevents duplicate combinations of LnkImgNum &
LnkSubjectNum.

TonyT..
 
Okay: so you can use the GROUP BY query, in combination with your subquery
idea.

Place the subquery in the WHERE clause if you want it evaluated before
grouping, or the HAVING clause if it operates on the results after grouping.
 
thanks for help so far Allen, but I'm still not getting it. Both the Location
/ Setting Part should be able to return multiple records, as should the
Subject part (as per your suggestion of the crosstab query). Which precludes
the use of a subquery surely? Unless there is a way of returning an array of
the subjectID's to a query based on the tblImg including the array. Or would
I be better off iterating through a recordset in vba and creating the array
and searcg criteria as I go......would be slow with even a modest amount of
images I'd have thought?

TonyT..
 
Okay, I have not gone through this in detail: I don't follow the purpose.

You could use a subquery as well as GROUP BY. Use the WHERE clause if the
criteria is not on the aggreated field; or the HAVING clause if it is
dependent on the results.

Another alternative would be to stack your queries. For example, create
another query that uses the GROUP BY query as an input "table", and use the
subquery there. Either that, or the other way around.

If you are really stuck, you can create a table to hold the aggregated
results (populating it by executing an Append query statement), and then
build queries that use the data in the temp table.

Lots of choices.
 
thanks Allen for all your help,

I was stuck, and reverted to what I knew I could do, so I populated a hidden
listbox with the results of the Images returned from your crosstab query,
then iterated through that to create an array and used IN (1,34,56,78,n,n) in
the forms recordsource SQL created on the fly with the other search criteria.

thanks for helping me in the right (or one of many possible :/ ) directions.

TonyT..
 
Back
Top