Comparing Rows

[spetroleum]

I need to compare rows returned in a group and the return a single row for the report of information taken from multiple rows in said group. how can I accomplish this?

Example:

Group Info

Stock ID Event ID Qty Received
130000T 1234 100
130010T 1234 800
130070 1234 900

Results Desired:
130070 1234 100


The lowest quantity received will always be returned and the Stock ID of the largest quantity will be returned.

 

[lbass]

You could just insert a group on event ID, sort the records by qty received ascending, and then copy the stockID and event ID into the group footer. For the minimum, just insert a minimum on the qty field at the group level. Then suppress the detail and group header.

-LB

 

[spetroleum]

That worked, but now I need to group by the returned stock id. any ideas?

 

[lbass]

You should have mentioned that to begin with. Please explain in more detail the report you want as an end product. Why do you need to group on this? Is that you need to do a sort on the results? Or do you need some kind of summary?

-LB

 

[spetroleum]

I have to group for tax reporting. It will also include a summary of the qty for the grouping also.

 

[lbass]

Why do you have to group for tax reporting? I am asking, because you could easily get a summary of the qty for the desired rows without grouping.

Also explain at what level you need the qty summary. It would probably help if you outlined the fields you are currently grouping on.

-LB

 

[spetroleum]

Here is the scenario.
We currently blend two products into one. Because Ar. only charges taxes on one product we have to report on the untaxed product. The database stores all lines in the same table so it is impossible(that I know of) to write a simple report.

Blended product
130000T 8,585 Greenwood MS 46515B 148.00
130010T 8,585 Greenwood MS 46515B 835.00

Final Product
130070 8,585 Kosciusko MS 46515B 983.00

Results Needed:
130070 130000T Greenwood MS Kosciusko MS 46515B 148

 

[spetroleum]

I will then need to Group off the 130000T field and sum the qty for the group

 

[spetroleum]

details grouped by event id

130000T 8,585 Greenwood MS 46515B 148.00
130010T 8,585 Greenwood MS 46515B 835.00
130070 8,585 Kosciusko MS 46515B 983.00

 

[lbass]

This is all doable, but you didn't answer any of my questions. I think you are assuming you need a group to get the summary value across event IDs and you don't. You are only showing one event ID, but if you were showing two, then I guess you would want to be summing 148 with some other value. But at what level? Grand total?

-LB