Select Just First Header/Detail Row

[EddieN1]

I have two tables with the typical Header/Detail relationship. In this instance the first detail for each header has special significance. I would like to write a query that lists a series of header/detail combinations, but only the first detail for each header. Thanks in advance, Eddie

[June7]

Show examples of raw data and desired output.

[EddieN1]

Not sure how to give an example, but I'll try... I'm printing a Purchase Order Summary report with one line per PO. Each line contains PO Number, Date, etc. plus I want to include the first detail whose field named Comment start with the string "Note:". Usually this is the first detail, but not always... sometimes there aren't any details like this at all. So, here are three sample lines:

PO Nbr Date Comments
1234 2/13/14 Note: See attach pdf file
1235 2/13/14 [blank... no "Note:" details]
1236 2/13/14 Note: Provide extra padding

Does this help? Thanks, Eddie

[June7]

Not quite. Is that the raw data or output example? You show 3 records, each is a different PO Nbr. Does this mean you want all 3 Comments to return? There would be other records for each PO Nbr that you don't want the comments from?

A table can be built in post with the Advanced post editor.

Or can attach files. Follow instructions at bottom of my post.

[EddieN1]

This is the output example. Typical one header with multiple details. I want to show the header fields as well as the Comment from the first detail whose Comment field begins with "Note:", otherwise blank.

[June7]

Still not fully understanding the data structure. There are two tables involved - primary and dependent? Can there be comments that do not begin with Note? Can there be more than one Note: for each PONbr/Date combination?

Maybe an aggregate (Totals) GROUP BY query will get you what you want.

Build a query that joins the two tables.

Select the PONbr and Date.

Create a field with expression:

NoteComment: IIf([Comment] LIKE "Note:*", [Comment], Null)

Click the Totals button on ribbon.

Set PONbr and Date to GROUP BY.

Set NoteComment to MAX.

Advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Also, should avoid reserved words as names. Date is a reserved word.