For each ID in table 1, I have multiple records in table 2 that have item-level detail for orders.

 

So from table 1 I need some data, and then from table 2, I need other data, both summed.

 

The problem is that for an ID, lets say ID 1, if there are 3 corresponding values in table 2, and I want to sum a field in ID 1, the cross-tab triples the dataset, pulling in that same value 3 times once for each corresponding value in table 2. But table 1 only has it once, so its only meant to exist once.

 

In normal crystal I can use groups to group these together and then move the details to the group footer. But not sure how to handle this with cross-tabs, any ideas?

The problem with using max is that I need to sum the IDs by date ranges. So I need to essentially sum the max of the IDs values, if that makes any sense.

 

I can pull it all out manually and do it, but I have to repeat this report dozens of times a month every month so want to get it as automated as possible.