Hi All,

I have a query with 2 tables using a 1 to many relationship. The one side is PO information and the many side is the line items per PO. When I run the query, I get (as expected) multiple instances of the same PO's on the one side (due to the multiple line items on the many side). I am trying to figure out a way to be able to search fields on either side and return only one record per match (for example search for a particular line item and return only the first match found in each PO).

I tried making it a totals query and setting my line items field to First and Last but these limit my results (I am missing some matches). I also thought about making my line items table into a query and bringing it into my main query but couldn't make this work either.

Any help is appreciated.

Thanks,

Ken

This post has been edited by madmix: Apr 4 2012, 02:49 PM

Hi Ken,

I think the Totals query is the correct approach but maybe you'll need to play with the relationship you're using to get the missing records into the result.

If that doesn't work, the next thing to try is to use a subquery.

Just my 2 cents... (IMG:style_emoticons/default/2cents.gif)

Hi there,
What would the "first match found in each PO" be? Would it be a line item with the lowest price, that was entered onto the PO first, etc...? Or could it be any line item associated with a PO?

Hi theDBguy and guerillaunit,

Thanks for your posts. Yes the sub query approach worked. I had to put my search criteria for the line items in the sub query and set that totals property to Where. Then I brought in my linked field (PO Number) and set that to group by. So this query performs the search on all of my line items and returns only one linked record per PO, regardless of how many line item matches it finds.

Thank you,

Ken

This post has been edited by madmix: Apr 4 2012, 03:16 PM

Hi Ken,

(IMG:style_emoticons/default/yw.gif)

guerilla and I are happy to help. Good luck with your project.