query many to many

[mjpr54]

I have a db for movies with some many to many relationships ie movie has several actors or several genres. How can I run a query that does not return all results for the criteria field, but still searches through them. For example I would like to be able to run query for all movies from 1972, but not have that movie listed 10 times because there are 10 different actors. Hope this makes sense. Thanks

 

[John Big Booty]

Use the Group By option available in a Totals Query.

 

[mjpr54]

Tried that....but if you group by first and movie is western and als drama if criteria is drama it will not show this movie....am I doing something else wrong? Thanks

 

[John Big Booty]

Are you able to post a copy of your DB?

 

[mjpr54]

I dont think so.....to large to attach I believe. Other options ?

 

[John Big Booty]

Make a copy of it and strip out the majority of the data leaving enough data to demonstrate what is happening, then do a Compact and Repair on it, then put it in a zip file and upload that.

 

[mjpr54]

thanks will see what I can do

 

[mjpr54]

Ok take a look at form SearchF....that feeds Total Query. I appreciate your help

 

[John Big Booty]

I suspect that the approach you are currently using is not always going to result in a meaningful response. As it relies on the user selecting a set of criteria that actually exist in the data and if he gets nothing he has no idea why.

Have a look at the sample here, I'm pretty sure that you could easily adapt it to produce a much more meaningful response.

 

[mjpr54]

Thanks again, your sample works great, but still not quite what I am looking to do.
Lets say the wife and I are trying to decide what movie from our collection we want to watch. I want to find a western with clint eastwood. Or find a PG rated comedy. Or that movie with tom hanks and meg ryan in it. Hope I am making sense .
Any ideas or am i just asking for too much?
Thanks

 

[John Big Booty]

Let me think about it

 

[mjpr54]

Thanks,
I appreciate your help

 

[John Big Booty]

OK here's how I'd approach the problem.

I've done it in '03 version simply because that's what I have a work.

It will probably need a little more polishing, but I believe it shows the principals of how to do what you are after.

 

[mjpr54]

Thank you sir, you have accomplished in a few hours what I have been working on for months. Guess it helps if you know what you are doing. I will polish it up and put it to use. Thanks again

 

[John Big Booty]

Glad it was what you were looking for