开发者

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;
0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜