MAX and Duplicate

  • Thread starter Thread starter Sharon
  • Start date Start date
S

Sharon

How do I get rid of duplicates after selecting MAX. Exp:
There are three exception rates for a record. Two of
these rates are duplicate and max. 7% and 7% and 5%. I
only want one 7% record to appear.

WHERE (((tblExcptRate.ExcptRate)=(SELECT Max([ExcptRate])
FROM tblExcptRate AS X Where X.[account] = [tblExcptRate].
[Account])));
 
Where 2 values are tied, give Access some way to determine which one to
return. For example, sort on the primary key value of the subquery:
ORDER BY tblExcptRate.ID
 
Please expand. . .

First, I determine which ExceptRate is the highest. Then,
I need to get rid of any duplicate ExceptRates for an
account. I don't care which one. The key for
tblExcptRate is an account number

Acct# ExceptRate
11123 7%
11123 7%
11123 10%

RESULTS: 1 record for Acct# 11123 ExceptRate = 7%


-----Original Message-----
Where 2 values are tied, give Access some way to determine which one to
return. For example, sort on the primary key value of the subquery:
ORDER BY tblExcptRate.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I get rid of duplicates after selecting MAX. Exp:
There are three exception rates for a record. Two of
these rates are duplicate and max. 7% and 7% and 5%. I
only want one 7% record to appear.

WHERE (((tblExcptRate.ExcptRate)=(SELECT Max ([ExcptRate])
FROM tblExcptRate AS X Where X.[account] = [tblExcptRate].
[Account])));
Click to expand...


.
Click to expand...
 
Okay: where is the duplicate coming from?
Is it a problem from the subquery (my previous assumption)?
Or is the duplicate coming from the main query?

If the main query, open your query in design view.
Open the Properties box.
Set the Unique Values property to Yes.

Alternatively, add the DISTINCT predicate to the SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Please expand. . .

First, I determine which ExceptRate is the highest. Then,
I need to get rid of any duplicate ExceptRates for an
account. I don't care which one. The key for
tblExcptRate is an account number

Acct# ExceptRate
11123 7%
11123 7%
11123 10%

RESULTS: 1 record for Acct# 11123 ExceptRate = 7%


-----Original Message-----
Where 2 values are tied, give Access some way to determine which one to
return. For example, sort on the primary key value of the subquery:
ORDER BY tblExcptRate.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

How do I get rid of duplicates after selecting MAX. Exp:
There are three exception rates for a record. Two of
these rates are duplicate and max. 7% and 7% and 5%. I
only want one 7% record to appear.

WHERE (((tblExcptRate.ExcptRate)=(SELECT Max ([ExcptRate])
FROM tblExcptRate AS X Where X.[account] = [tblExcptRate].
[Account])));
Click to expand...
Click to expand...
Click to expand...
 
Yes, DISTINCT is what I want. The duplicate is coming
from the table - which is a combination of many tables.
Now, How do I combine both the MAX and DISTINCT funtions
to my code? Please be specific as I am new to VB.

SELECT tblExcptRate.account, tblExcptRate.smple_sz,
tblExcptRate.ExcptRate INTO tblExcptRateMax
FROM tblExcptRate
WHERE (((tblExcptRate.ExcptRate)=(SELECT Max([ExcptRate])
FROM tblExcptRate AS X Where X.[account] = [tblExcptRate].
[Account])));

-----Original Message-----
Okay: where is the duplicate coming from?
Is it a problem from the subquery (my previous assumption)?
Or is the duplicate coming from the main query?

If the main query, open your query in design view.
Open the Properties box.
Set the Unique Values property to Yes.

Alternatively, add the DISTINCT predicate to the SQL statement.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Please expand. . .

First, I determine which ExceptRate is the highest. Then,
I need to get rid of any duplicate ExceptRates for an
account. I don't care which one. The key for
tblExcptRate is an account number

Acct# ExceptRate
11123 7%
11123 7%
11123 10%

RESULTS: 1 record for Acct# 11123 ExceptRate = 7%


-----Original Message-----
Where 2 values are tied, give Access some way to determine which one to
return. For example, sort on the primary key value of
Click to expand...
the
subquery:
ORDER BY tblExcptRate.ID

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

How do I get rid of duplicates after selecting MAX. Exp:
There are three exception rates for a record. Two of
these rates are duplicate and max. 7% and 7% and 5%. I
only want one 7% record to appear.

WHERE (((tblExcptRate.ExcptRate)=(SELECT Max ([ExcptRate])
FROM tblExcptRate AS X Where X.[account] = [tblExcptRate].
[Account])));
Click to expand...
Click to expand...


.
Click to expand...
 
"DISTINCT" goes right after SELECT, before the field list:

SELECT DISTINCT tblExcptRate.account, ...
 
Back
Top