Problems with uneven spacing

[ccgreenman]

I'm so close to figuring this out, but have hit a brick wall.

I've got a query of two lists of things by group:

Apple 1 :: Group A :: Orange 1
Apple 2 :: Group A :: Orange 2

The groups can contain only one "type" of Apple and only one "type" of Orange, but because of the way I have my joins if group A only contained Apple 1 but contained Orange 1 through 10, Apple 1 gets listed 10 times.

I want to create a report that has:

Group Header (sorted by group)
______________________________
List of Apples | List of Oranges

and I want the lists to not have any extra spaces in them.


The problem I'm getting is even if I turn on hide duplicates, because Orange 2 is a different record than Orange 1, the list of apples repeats within the group as many times as numbers of Oranges, because they are repeating in different details under the group header.

I go stumped banging my head against that one, and because I don't expect the list to change very often, I tried the following...

I created a new table with two columns - Group ID, and List of Oranges, then copied the groupings of oranges from different cells into one cell (memo type).

The new query doesn't have the duplicate issues. The new problem I'm having is with uneven spacing in the report. It looks like this:

Group
_____________________________
Apple 1 | Orange 1
Orange 2
Orange 3
Apple 2
Apple 3

Because all the Oranges are in one field, and each apple is in a separate field.

There are WAY TOO many apples to combine them into individual fields like I did with the oranges.

Any idea at any stage of this mess to do a different way?

I appreciate any help you can give me and I apologize that the post is so long and complex.

Thanks!!
Cori