Select latest Address

[cright99]

Im trying to create an invoice but if there is more than one address. Then it keeps on creating more than one Account information. For example more than one quantity, description and unitprice. Ive tried to use MAX on AccountID but this dosn't seem to work. Any ideas?

Here is my SQL

SELECT Account.AccountID, Account.AmountOwed, Account.DateEntered, Account.PaymentType, AccountDes.Quantity, AccountDes.Description, AccountDes.UnitPrice, [quantity]*[unitprice] AS Amount, [Client Details].CustomerID, [Client Details].Surname, [Client Details].[Given Name 1], Address.Number, Address.Street, Address.Suburb, Address.City, Address.Country, Address.AddressID
FROM ([Client Details] LEFT JOIN Address ON [Client Details].CustomerID = Address.[Customer ID]) INNER JOIN (Account INNER JOIN AccountDes ON Account.AccountID = AccountDes.AccountID) ON [Client Details].CustomerID = Account.CustomerID;

 

[HiTechCoach]

You could try using a sub query. See Subquery basics

Another option is to use a sub report.

Do you have a field to determine which record to select?

 

[cright99]

Thanks for that. Im trying to figure it out (im not to good at sql) But still havent got any where yet

 

[ajetrumpet]

I am willing to bet that if you upload the database, someone will fix it for you..