Query to find and display unique records in ms-access?
I am using MS-ACCESS. I have a table with field as Receipt_No. In this field there are many times repeated values. I just want to display this repeated values only once rather than displaying it to several times. Here is my table:
Registration_No Payment_Date Charges Receipt_No
T-11 8/7/2011 200 105
T-12 8/7/2011 200 106
T-13 7/12/11 200 107开发者_开发问答
T-14 12/7/2011 200 108
T-15 12/7/2011 400 108
Here in Receipt_No field 108 appears 2 times i want to display it only once as:(charges either 200 or 400. But Receipt_No should display once): Please help me..
Registration_No Payment_Date Charges Receipt_No
T-11 8/7/2011 200 105
T-12 8/7/2011 200 106
T-13 7/12/11 200 107
T-14 12/7/2011 200 108
If you want to display only the records in your table with a receipt number that appears exactly once, use this query:
select * from Demand
where reg_no in (
select reg_no
from Demand
group by reg_no
having count(*) = 1
)
With the clarifications you've provided, it looks like what you want is more like in this question, where you want to return all fields, but only one record per receipt number. Here is a variation on the accepted answer:
select * from demand
inner join
(
select
receipt_no,
min(charges) AS min_charges
from
demand
group by
receipt_no
) sq
on demand.receipt_no = sq.receipt_no
and demand.charges = sq.min_charges
Note that this is still not exactly what you want: if there are two or more records with the same values for receipt_no and charges, this query will return them all.
Part of the problem is that your table is not well-defined: it does not appear to have a field that is unique for every record. With such a field, you can modify the query above to return a single row for each receipt_no. (Another part of the problem is that there seems to be something missing from the business requirement: usually, we would want to report the total charges from a receipt, or each charge from a receipt.)
Not sure exactly what you need in your query since you didn't provide many details but using SELECT DISTINCT
Omits records that contain duplicate data in the selected fields. To be included in the results of the query, the values for each field listed in the SELECT
statement must be unique.
see MS Access Docs for more detail
But as an example the following query would select all LastNames but it would remove duplicate values.
SELECT DISTINCT LastName
FROM Employees;
精彩评论