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

 

[ranman256]

if you want ALL Accts to show ,even if they do not have a comment,
then you must create an OUTER join.
in the query, join the 2 tables, then dbl-click the join line connecting them,
set the join type to all records in tAccts, Some in tComments.
bring down the fields you need,
save, run.

 

[Sypher7]

This has been viewed a few times with no comments yet so just to give some more info around what i need:

These are my (example) tables:

[TABLE="width: 243"]
<tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account-Number[/TD]
[TD]Account-Name[/TD]
[TD]In-Query[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Account 1[/TD]
[TD]Yes[/TD]
[/TR]
[TR]
[TD]AC002[/TD]
[TD]Account 2[/TD]
[TD]No[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[/TR]
</tbody>[/TABLE]


[TABLE="width: 191"]
<tbody>[TR]
[TD]Table 2[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Account-Number[/TD]
[TD]Comment[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Received-Documents[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Documents-Incorrect[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Account-Placed-in-Query[/TD]
[/TR]
[TR]
[TD]AC002[/TD]
[TD]Received-Documents[/TD]
[/TR]
[TR]
[TD]AC002[/TD]
[TD]Documents-Correct[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Received-Documents[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Documents-Incorrect[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]New-Documents-Still-Incorrect[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account-Placed-in-Query[/TD]
[/TR]
</tbody>[/TABLE]


This is what I want the report to look like:
[TABLE="width: 418"]
<colgroup><col><col><col><col></colgroup><tbody>[TR]
[TD]
[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]Received-Documents
Documents-Incorrect
Account-Placed-in-Query
[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[TD]Received-Documents
Documents-Incorrect
New-Documents-Still-Incorrect
Account-Placed-in-Query
[/TD]
[/TR]
</tbody>[/TABLE]

[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


This is what it actually looks like

[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]Received-Documents[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Account 1[/TD]
[TD]Yes[/TD]
[TD]Documents-Incorrect[/TD]
[/TR]
[TR]
[TD]AC001[/TD]
[TD]Account 1[/TD]
[TD]Yes[/TD]
[TD]Account-Placed-in-Query[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[TD]Received-Documents[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[TD]Documents-Incorrect[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[TD]New-Documents-Still-Incorrect[/TD]
[/TR]
[TR]
[TD]AC003[/TD]
[TD]Account 3[/TD]
[TD]Yes[/TD]
[TD]Account-Placed-in-Query[/TD]
[/TR]
</tbody>[/TABLE]













Thanks

 

[Sypher7]

if you want ALL Accts to show ,even if they do not have a comment,
then you must create an OUTER join.
in the query, join the 2 tables, then dbl-click the join line connecting them,
set the join type to all records in tAccts, Some in tComments.
bring down the fields you need,
save, run.

Hi Ranman, you must have been typing at the same time as me,

Not quite what i was after, apologies i forgot to say i only wanted the records from the first table if they are flagged as a query.

Please see my example i just posted.

thanks for you help.

 

[james_lankford]

the results you typed look lik a pivot table

I've never made a pivot table in access
I've only used Excel for that

but I'm sure you can do it in acccess somehow

 

[baderms]

You should do your query as

SELECT Table1.AccountNbr, Table1.AccountName, Table1.InQry, Table2.Comments
FROM Table1 LEFT JOIN Table2 ON Table1.AccountNbr = Table2.AccountNbr
WHERE Table1.InQry=True;

As for the report, all you have to do is base it on the query and group it on the accountnbr.

 

[Sypher7]

So basically what i found out is that yes i am after a pivot table and in Access 2010 you could do it but they removed the feature.

I have set up an excel pivot table that has a connection to the access table, i run my query to create the table then open the excel file which automatically updates on open.

Its a shame as i intended for anyone to just be able to open it and run the query but at least this is a workaround.

Didn't seem like it could be possible in access.

Baderms thanks for your suggestion i will keep looking into that.

 

[Micron]

A pivot table maybe, but I've never used them in Access either. Maybe this calls for the power of a report? In a report, you can group by Account number (this grouping level can include the account name and any other account related data) and each comment would be a record under the related account. The output would look like what you've displayed as a desired result. A query cannot display as you've posted because it can't leave holes where you have no data in your example.

I see that post #6 says "as for the report" but I can't find any prior mention of a report so I missed that.

 

[baderms]

It's in the initial request posting.

 

[xenou]

So, is the problem solved now?