Excel-VBA, MS Access with INNER JOIN

[luffy88]

Hello,
I have a problem with importing data from ms access to excel with <acronym title="visual basic for applications">VBA</acronym>. I use "ADO Connection" to connect excel with acces.
I have 2 tables in ms access. I want to get the newest comment from table p_comment for products. I tried it with inner join, but i get all comments.
so how can i get only the newest/latest comment for a product?
thank you for your help.

sql= "SELECT p_comment.ID, p_comment.p_id, p_comment.user, p_comment.comment, p_comment.c_date FROM product INNER JOIN p_comment ON product.ID = p_comment.p_id"




p_comment
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]p_id[/TD]
[TD]user[/TD]
[TD]comment[/TD]
[TD]c_date[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]test user[/TD]
[TD]test comment[/TD]
[TD]19.02.2017[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]1[/TD]
[TD]test123[/TD]
[TD]comment1[/TD]
[TD]19.02.2017[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]2[/TD]
[TD]test1[/TD]
[TD]comment3[/TD]
[TD]19.02.2017[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]2[/TD]
[TD]test1234[/TD]
[TD]commen4[/TD]
[TD]19.02.2017[/TD]
[/TR]
</tbody>[/TABLE]


product:
[TABLE="width: 500"]
<tbody>[TR]
[TD]ID[/TD]
[TD]p_name[/TD]
[TD]p_date[/TD]
[TD]p_desc[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]test[/TD]
[TD]01.02.2017[/TD]
[TD]test[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]hallo[/TD]
[TD]01.02.2017[/TD]
[TD]test[/TD]
[/TR]
</tbody>[/TABLE]

 

[Joe4]

You would need to do an Aggregate Query in order to do that. Your query simply returns all the records.
See:
Access query to select rows that are the MAX of one column but unique on another - Stack Overflow
https://www.techonthenet.com/access/queries/max_query2.php

However, in looking at your example, how are you qualifying what is the "newest comment"?
They all seem to have the same c_date.