Display the record with the lowest val.. 2

[thatguy]

Hey there folks--

I've been racking my brain over this one for a Long time now and I am at my end... I have a one-to-many parent/child relationship that's being displayed in a report. Trouble is: I only need the child record with the lowest value in one field. For example, the parent record could have 5 related child records, in each child record field ("Qty&quot there could be values of: 100, 10, 500, 50, 1000. I need some way to pull ONLY the child record with Qty = 10. The report works fine to display all child records, but I can't figure out a way to say: "sort the child report band by the field Qty and just show the first record".

Anyone have a suggestion on this one? Please??

Thanks
-- michael~

 

[scriverb]

Try creating a query of the table and Group By your Master Link Fields. Also, perform a Min([Qty]). Now use this query as the the Control Source for the SubReport. You Linked fields will now only pick up the one record that is the lowest.

If this sounds like a viable solution I can help you with more specifics.

Bob Scriver

 

[GingerR]

in your subform detail, go to the recordsource. click to the right on the little builder button (with three dots). it will open a query design grid.
bring down all your fields.
sort ASCENDING on the QTY field.
then in the grey part above the grid, right-click and choose PROPERTIES.
in the TOP VALUES propery, put a 1.

 

[thatguy]

Both good suggestions - thanks and stars to each!


-- michael~