Dear,
I am building a report based on outlets which the sales force visited the last 7 days; in this report I would like to see how the status of some fields (product availability) changed over the previous visit...
Currently I build two queries, with one being the outlets visited this week and secondly the full list of outlets in my database which I joined in a third query. However this is not very performaning very well for the moment (200.000 rows). My question is therefore if it is possible to use the output (selected outlets) of query 1 as a filter for query 2...
let me know if things are unclear. I'm still a relative novice so any clear help is highly appreciated!
Your requirement looks similar to another question that was posted in the C10 forum last month. Take a look at this: http://www.cognoise.com/index.php/topic,21611.msg65794.html (http://www.cognoise.com/index.php/topic,21611.msg65794.html)
I'm assuming your two queries, which I'll call Full and Partial, both include an identifier which is common to your table of visits and your table of all outlets. Let's call it Outlet_Id.
In your case you would just add a new filter to your Full query: [Full].[Outlet_Id] in ([Partial].[Outlet_Id])
This may of course need some adaptation according to your exact situation, but I hope you get the general idea.