MS Access Query to get unique rows based on a single column

Vote count:

0

I have a query as:

SELECT MAX(SubmissionLog.ID), AccountTypes.Description, AccountContacts.FirstName, AccountContacts.LastName, Accounts.Name, Accounts.StreetAddress, Accounts.MailAddress,SubmissionLog.EffectiveDate, SubmissionLog.ExpirationDate, StatusCodes.Description

FROM

AccountContacts RIGHT JOIN (StatusReasons RIGHT JOIN ([JILL_MG utah sub query] RIGHT JOIN (StatusCodes RIGHT JOIN (Carriers RIGHT JOIN (AgencyLocations RIGHT JOIN (Brokers RIGHT JOIN (Agencies RIGHT JOIN (SubmissionLog LEFT JOIN (AccountTypes RIGHT JOIN (BusinessTypes RIGHT JOIN Accounts ON BusinessTypes.ID = Accounts.BusinessTypeID) ON AccountTypes.ID = Accounts.AccountTypeID) ON SubmissionLog.AccountID = Accounts.ID) ON Agencies.ID = SubmissionLog.AgencyID) ON Brokers.ID = SubmissionLog.BrokerID) ON (AgencyLocations.LocationID = Brokers.AgencyLocationID) AND (AgencyLocations.AgencyID = Brokers.AgencyID)) ON Carriers.ID = SubmissionLog.WinningCarrierID) ON StatusCodes.ID = SubmissionLog.StatusID) ON [JILL_MG utah sub query].SubLogID = SubmissionLog.ID) ON StatusReasons.StatusReasonID = SubmissionLog.StatusReasonID) ON AccountContacts.AccountID = Accounts.ID

WHERE ( (AccountTypes.Description) Like "prospect" Or (AccountTypes.Description) Like "prev*") AND ( (SubmissionLog.EffectiveDate)>#12/31/2010#) AND ((StatusCodes.Description) Like "not*") AND ((Accounts.dbType_id) In (15)) )

GROUP BY AccountTypes.Description, AccountContacts.FirstName, AccountContacts.LastName, Accounts.Name, Accounts.StreetAddress, Accounts.MailAddress,SubmissionLog.EffectiveDate, SubmissionLog.ExpirationDate, StatusCodes.Description

ORDER BY Accounts.Name

It gives submission details based on Account Names. But we can have Account names repeated as we can have many submissions for an Account with different Effective Dates. But here i need to modify thr query so that I can get Account details with he most recent Effective date(having greatest value of SubmissionLog.Id) irrespective of unique combinations of Account Name with other columns. I jus want Account name with the recent effective date so that there is only one row per Account Name. I have used MAX(SubmissionLog.Id) to filter but due to unique combinations with other columns the Account Name is getting repeated as it has more than one combination with different values with other columns. Any workaround on this guys..??

asked 18 secs ago

Dpanshu

1

MS Access Query to get unique rows based on a single column