In our database we have 2000 clients. Each client can have more than one project.
I have a query in a DeskI report that limits the projects displayed for each client based on a project criteria. So, if the client has 5 projects, lets say 4 meet the criteria so we would see this:
Client A
Project 1
Project 2
Project 3
Project 4
Of the 2000 clients 1700 of them have one or more projects that meet the “criteria”.
Here’s the tricky part:
I want to be able to display ALL 5 projects for the 1700 clients, the 5th being the one that doesn’t meet the criteria.
If I setup a 2nd query to get all the projects, using an “IN LIST” for the client name, I exceed the 1000 limit. I can’t use a subquery because I need the project criteria to select the client name and you can’t have more than one object in the subquery.
Parent query might have a list of clients and projects. The condition would be client “in list” and then you get a sub-query.
The sub-query has as the result object the client ID. It has a sub-query for project_id in list and yet another sub-query.
The last sub-query returns a list of projects that fit your criteria.
The queries execute from the inside out, so first you will get a list of all projects that fit your criteria, then you will get a list of clients associated with those projects, and ultimately you will get a list of all projects (not just those that fit the criteria) for clients selected.
The problem appears to be that the criteria I need to restrict the projects requires more than one object in the subquery (which it won’t let me do). Otherwise I’m getting a Cartesian product message.
Once you build the sub-query, you can apply conditions on as many items as you want. :-? So unless you need more than the project id (or client id) to reference a project, I’m not sure I follow. Maybe if you can provide more specifics about the issue it would help.
(BOB member since 2008-07-29)