Create Query Linking Two Tables Together

[Sypher7]

Hello,

I have two tables. One labelled "Records" and one labelled "Comments".

I both tables I have an "Account Number"

However in the records table, there will only ever be one record for each account. In the comments table there can be any number of records for that account.

The idea behind this is that everytime a new action is taken on an account a new comment is logged, using a continuous subform i have it showing a comment history.

I need to create query which brings back certain fields from the records table but also the comment from the comments table.

So far I have got it to work with the exception that each comment shows as a new record in my finished report.

I am after a way of combining all of the comments into one field and then pulling it through so that each account will only show up one in my finished report.

Can anyone suggest a way of doing it please as I am a bit stumped.

Thanks

 

[Micron]

I see. I think I also see that post #8 contains the answer (grouping) although the result would only be very close to what you've posted that you want.
[TABLE="width: 500, align: left"]
<tbody>[TR]
[TD]Account Number[/TD]
[TD]Account Name[/TD]
[TD]In Query[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Account 1[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Received-Documents
Documents-Incorrect
Account-Placed-in-Query[/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Received-Documents
Documents-Incorrect
New-Documents-Still-Incorrect
Account-Placed-in-Query[/TD]
[/TR]
</tbody>[/TABLE]