-2

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
J. Steen
  • 15,470
  • 15
  • 56
  • 63
Samiksha
  • 1
  • 1
  • 2

2 Answers2

1

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.)

Community
  • 1
  • 1
Dave DuPlantis
  • 6,378
  • 3
  • 26
  • 30
  • Sorry, I mistaken to write down that I want all records but the receipt number comes several times but that receipt number i want to display once rather than several times; – Samiksha Jul 14 '11 at 09:10
  • @Samiksha, you need to have a way to specify **which** of the duplicated records to show. This is where an example of your table would be helpful: if you can show us several records with the same receipt number and tell us which record you want to display, we can probably figure out what to suggest for you. – Dave DuPlantis Jul 14 '11 at 13:00
  • Here is the table given below: Registration_No Payment_Date Charges Receipt_No T-11 1/2/2011 200 101 T-12 1/3/2011 300 102 T-13 1/5/2011 400 105 T-13 1/5/2011 600 105 Here in the above table Receipt_No "105" displayed two times but I want to display it once any value with charges 400 or 600. i.e. I want Receipt_No 105, 101 and 102. – Samiksha Jul 14 '11 at 13:22
  • 1
    @Samiksha, please edit your question to include this information; it will be much easier to read as part of the question, and it will help other people who may see the question and have a way to solve your problem in case we can't. – Dave DuPlantis Jul 14 '11 at 13:24
  • Now, above in my question T-11 is Registration_No, 2/2/2011 is Payment_Date, 200 is charges and 105 is the receipt_No. – Samiksha Jul 14 '11 at 13:50
  • The above query is working but not in case where charges are same; – Samiksha Jul 15 '11 at 07:56
  • @Samiksha, a simple Access query cannot give you what you need. Without a unique identifier in your rows, there is no way for Access to return only the first row in a tie. Using `TOP 1` actually means "top 1 **plus ties**". To retrieve only the first record, you would have to use VBA and loop through the recordset manually, or if you want the output in a report, wrap code around the detail section to hide all but the first instance of a receipt number. – Dave DuPlantis Jul 15 '11 at 13:22
0

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;
Taryn
  • 242,637
  • 56
  • 362
  • 405
  • Is there any other method to retrieve unique as using DISTINCT I got error message as Invalid descriptor index. How can I deal with it? – Samiksha Jul 13 '11 at 10:52
  • Here is my query: String query1="select * from Demand where Reg_No like '"+rno+"%' "; But in Demand table there are several same no. of receipts so i want to display unique so here is another query as String query4="select DISTINCT Receipt_No FROM Demand"; But i think i should combine both of these queries to get result; so how can I do it? I want all the data from Demand table except repeated receipt numbers. – Samiksha Jul 13 '11 at 12:10
  • Try your query1 this way as a test initially select distinct * from Demand to see if it will run without the where clause – Taryn Jul 13 '11 at 13:03
  • I used: "select distinct * from Demand where Reg_No like '"+rno+"%' "; But it retrieves all the records; instead of unique; – Samiksha Jul 13 '11 at 13:57
  • try removing the where clause and see if you get only the unique records – Taryn Jul 13 '11 at 13:59