Access multiple table query

[columbo1977]

Hi All



Hopefully someone can help me cause i'm pulling my hair out....

I have a main form which holds client data (name address etc)
I then have an accounts form where statement infromation is entered, this form may hold several statements for the same person.

What I need is to mkae a query that shows the client details and onyl the most up to date statement the field for that is [Account_DateOfLastStatement]

I cannot get this to work everytime I try I get about 5 rows of data, all the main form data duplicated and a seperate line for each statement.. please help

Here is a simple example, when I run this I get several records instead of just the latest staement date:

SELECT tblData.ClientAISNumber, tblData.Client_Title, tblData.Client_Forname, tblData.Client_Surname, tblAccountStatus.Account_Balance, tblAccountStatus.Account_DateOfLastStatement
FROM tblData INNER JOIN tblAccountStatus ON tblData.ClientAISNumber = tblAccountStatus.ClientAISNumber
WHERE (((tblData.ClientAISNumber)=999999));

Please let me know if you need anymore information.

Thanks

Graham

[rpeare]

What I would do first is build a query that shows a customerID and the most recent statement date (max([statementdate]) in a query). Then link your customer table to this query, then create a link from this 'max' query to the detail table joining on the customerID and the 'max' statement date that should give you the most recent information if you add the fields you want from the detail table.

If this is gibberish please post a sample database with fake data in it and I can demonstrate.

[columbo1977]

Hi

I know what you mean and I have done that (it worked) But....

As soon as I add any other field from the Accounts table instead of just showing the latest statement it adds in all of the starements for that client?

G

[rpeare]

If you can post a sample database I can take a look, just make a copy of your database and take out or redact any privacy information then zip it up and post it here, then I can take a look at what you've done wrong but without a sample it's hard to give you direction especially with no SQL code to work with.