Display LOCATION of chosen DVD video without creating 28 queries.

[Aztecfan63]

I have 2 Tables in my Access database.
Videos: 700 records Titles, categories, locations, etc.
Categories: 28 records
My plan is to physically store the DVDs by category in different places in my home.



I would like to create a program in Access to search for a DVD to watch.
My thought was to present a screen in MS Access where the user could first select a Category.
Then using that selected Category select a movie and Location from the available DVDs listed.
Finally, Print a report of all DVDs in that Category.
Suggestions?
Thanks, AZtecfan63

[CJ_London]

Thoughts:

films can have more than one category.
You might have 2 copies of the dvd in different locations

[June7]

What you describe sounds like what is known as "cascading or dependent combobox". Common topic.

For ideas on building a search form, review http://allenbrowne.com/ser-62.html. It does not employ cascading combobox but should not be difficult to modify.

[pbaldy]

See if these help:

http://www.baldyweb.com/CascadingCombo.htm

for the report:

http://www.baldyweb.com/wherecondition.htm

[ArviLaanemets]

In case every video belongs only to single category, and a every video is stored in single location, you can have the category (and location) field(s) in videos table as is declared in your post #1. Let's assume the table for videos is structured like:
tblVideos: VideoID, VideoTitle, VideoCategory, VideoLocation
Create a form fVideos, and add a combo for searching for video by VideoID of selection made. Design the Row Source of this combo as query like:
SELECT VideoID, VideoCategory & ": " & VideoTitle FROM tblVideos ORDER BY 2 ASCENDING
Now when you activate this combo, all videos are listed ordered by video category at first, and then by video name. You can either scroll down to start of wanted category, and to search from there videos of this category, or you can start with typing the category name, and you are moved to start of this category in selection list. With less than 1000 videos divided between about 30 categories, I don't see any need for using cascading combos!
Btw, if the locations are determined by category, and you have only single exemplars of videos, then tho proper table for location must be tblCategories, not tblVideos!

In case a video can belong to several categories, you need an additional table:
tblVideoCategories: VideoCategoryID, VideoID, CategoryID/Category (depending how is the table tblCategories structured).
And the RowSource query for search combo in videos form will be something like:
SELECT v.VideoID, c.Category & ": " & v.VideoTitle FROM (tblVideos v LEFT JOIN tblVideoCategories vc ON vc.VideoID = v.VideoID) LEFT JOIN tblCategories c ON c.CategoryID = vc.CategoryID ORDER BY 2 ASCENDING
Searching will work exactly same as above, but when you want the location displayed too, and you don't have a separate copy of video for every location, then getting possible locations displayed gets more complicated.