I need to create a disjoint report, and I'm having trouble finding how to do it. I'm creating an invoice in an invoicing application for a trucking company that's a lot like the sample "Northwind" application. In my application, I've got an "invoices" table and an "invoice items" table, which correspond to the "orders" and "order items" tables in the Northwind application. But I've also got a "containers" table, keyed on invoice number like the "invoice items" table, and what I'd like to do is have the first detail column of the invoice have container numbers, and the remainder of the columns have "invoice items" data (description, quantity, price, etc.). There may be more containers than "invoice items," or there may be one container and multiple "invoice items." Not surprisingly, I ended up with a cartesian product with my first attempt, and that definitely won't do!

I know that what I need is a disjoint report, but I'm having a helluva time figuring out the syntax. And I just spent an hour at Barnes and Noble, and none of the SQL books had "disjoint" in the index! Can anybody help me out here?

Another possibility is to spread out the containers in the invoice number footer, so any suggestions as to how to "flatten out" the containers table so that I have one string with all of the container IDs (preferably separated by commas) would be appreciated as well.

Thanks!

Rich

On Wed, 30 Jun 2004 20:50:01 -0700, "rmiller1985"
<rmiller1985atearthlinkdotnet> wrote:

>I need to create a disjoint report, and I'm having trouble finding how to do it. I'm creating an invoice in an invoicing application for a trucking company that's a lot like the sample "Northwind" application. In my application, I've got an "invoices" table and an "invoice items" table, which correspond to the "orders" and "order items" tables in the Northwind application. But I've also got a "containers" table, keyed on invoice number like the "invoice items" table, and what I'd like to do is have the first detail column of the invoice have container numbers, and the remainder of the columns have "invoice items" data (description, quantity, price, etc.). There may be more containers than "invoice items," or there may be one container and multiple "invoice items." Not surprisingly, I ended up with a cartesian product with my first attempt, and that definitely won't do!

It sounds to me like you might want a Report with two Subreports (if
that's what you mean by "disjoint reporting"). There's no way to avoid
the cartesian join in a Query, but the Report object has a great deal
of flexibility, between its Sorting and Grouping feature and the
ability to create Subreports.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public