Display Latest Record in the Cross Tab Summary

  • 0
  • 1
  • Question
  • Updated 2 years ago
  • Answered
I have a report that cross Tab summarize the quote of my Suppliers. However, it only displays the minimum or maximum quote. I would like for it to display the latest quote.

How can I do this?


Photo of Roel

Roel

  • 0 Points

Posted 3 years ago

  • 0
  • 1
Photo of Matthew Neil

Matthew Neil

  • 31,698 Points 20k badge 2x thumb
This can be accomplished through a summary field, then passing that summary as a lookup, then using that lookup as a condition for your summary.  Sounds confusing, but it really just 3 steps.

Step 1:  Create a summery field to summarize the "Maximum" Record ID# from your child table.  Might create a field called [Maximum Record ID#]

Step 2:  Pass that new summary field [Max Record ID#] as a lookup field back to the child table.  it might call the field something like [Quote - Maximum Record ID#]

Step 3:  Create another Summary field of your dollar value, but make sure you place this condition;  "Only summarize values when [Record ID#] is equal to the value in the field [Quote - Maximum Record ID#]

let me know if you get stuck.
Photo of Kipp

Kipp

  • 258 Points 250 badge 2x thumb
Hi Matthew, your explanation is very clear.

I'd like to achieve the same thing but I can't rely on the record ID# as the identifier for the latest record, instead I need to reference a date field. I need QB to determine which is the maximum child record based on the date in the date field and return the record ID# of that record with the most recent date. I have managed to create a summary field that returns the date of the record that has the latest date but am as yet unable to translate that in to a record ID#.

The reason for this is that we are back-filling data so we can't rely on the highest record ID# being the most recent value.
Kipp,
no problem,
1. Summary of the maximum date.  Call it [Most Recent Date]
2. Lookup up down to detail records.
3. Summary of Maximum Record ID subject to the extra condition that the [Record ID#] equals the value in the field [Most Recent Date].
Photo of Kipp

Kipp

  • 258 Points 250 badge 2x thumb
I nearly managed to replicate this and then realised the date field I am using as a reference is not so in some instances there would be more than one Record ID# for the same date. As result we are taking a different approach on this one but thank you very much for your help.