Help needed for a photo filtering query

[Guest]

Hi and thanks for any assistance

I have a database (Access 2002) which, as a component, has the ability to
link photos to client records with the ability to add categories like Ulcer,
diabetes, amputation, fungal infection etc (some beautiful looking pictures!).
I want to be able to have a multiselect list of all the categories so that
you can keep selecting categories and it filters the photos so that those
with all the selection critieria are displayed. Unfortunately I can't figure
out the query for multiple selections.
My tables are as follows:
tblPhotos tblPhotoBridge tblCategories
PicID (primary key) - PicID
Title Category -------- Category (primary key)
Path PhotoBridgeID


I hope that the above makes sense. Can someone please help me on how to
structure a query to filter the tblPhotos based on multiple selections of the
Category.

Cheers
Brett Kinross

 

[[MVP] S.Clark]

If you look at the code for ItemsSelected and ItemData, which are
collections associated to multi-select listboxes, you'll see how to traverse
the selected items, such that you can generate an inline SQL statement to
get something that looks like:

Select * from Tablename where Category IN ('catA','catB','catC')

This is in VBA, btw.

 

[Guest]

Thankyou for your reply.

Structuring SQL statement in VBA through the ItemsSelected property is not a
problem. The SQL statement you suggested is, unfortunately, not what I need.
With the SQL statement:

SELECT * FROM tblPhotoBridge WHERE Category IN ('CatA', 'CatB')

Lists all the records that are either CatA or CatB. This means that for
every item included the list gets longer. What I am requiring is that the SQL
statement would filter so that only records with BOTH CatA and CatB in the
tblPhotoBridge will be returned i.e. the more Categories selected, the fewer
records returned.

Cheers
Brett - Podiatrist

 

[[MVP] S.Clark]

Yes, that is much, much harder. This leads to Set Theory and Venn Diagrams
and the like.

Read this, and see if it helps.
http://www.mvps.org/access/queries/qry0016.htm

Browse the other topics, and see if one fits the bill.

I'm not the greatest when it comes to subqueries, only becuase I hate
debugging them, so I do it with a series of squeries in steps. Write data
to a table that has Cat1. Delete any that don't have Cat's 2 through n.
This leaves the set that contains all cats.

 

[Guest]

Thanks for your reply. What I had done in the end was use a maketable query
based on your original suggestion then did another query to that table which
did a count and only included those records that occurred the same number of
times that you had criteria selected. So if I had 4 criteria selected only
those records that occurred 4 times in the original query would be kept.
Works well so far.

Thanks again for your time

Brett