Results 1 to 9 of 9
  1. #1
    Join Date
    May 2006
    Posts
    20

    Unanswered: Suppress duplicate records

    Hi,

    I have following Microsoft Access query results sorted on Customer Name.

    ============================
    CustomerName-----OrderNumber
    ============================

    ABC--------------1245
    ABC--------------1000
    XYZ--------------1100
    XYZ--------------1200
    OPQ--------------1800

    I want the query not to repeat duplicate CustomerName and give result like

    ============================
    CustomerName-----OrderNumber
    ============================

    ABC--------------1245
    ------------------1000 'No repeat customer name of ABC but only blank cell
    XYZ--------------1100
    ------------------1200 'No repeat customer name of XYZ but only blank cell
    OPQ--------------1800


    Please advise how to stop duplicate CustomerName.

    Thanks,
    Rakesh

  2. #2
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    Hi rakeshprkh

    Just to make it quick why not just send the data from the query to a report?

    you can setr the preferences to group by customer name - that way all the order numers will follow nicely


    gareth



    "keep it simple! thats how!"

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    Your Query should start:

    SELECT DISTINCT CustomerName From yourTableName

    .
    Environment:
    Self Taught In ALL Environments.....And It Shows!


  4. #4
    Join Date
    May 2006
    Posts
    20
    Dear Gareth,

    Thanks. In fact the result is going to be used on GridView on aspx page where I have to restrict duplicate CustomerName.

    Thanks,
    Rakesh

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Personally I would do everything you can to see if the gridview can do this - really it is a presentation problem.

    However, if it can't or you really want to go down this route then here is one technique. I have not checked it for suitability for Access (it is written in T-SQL - you will need to translate. I will skim through and point out some hints).
    http://weblogs.sqlteam.com/brettk/ar...0/10/7987.aspx

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ok - derived this from Brett's blog since most of the complication was to do with totals:
    Code:
    SET NOCOUNT ON
    CREATE TABLE myTable99([name] varchar(15), [date] datetime, Amount int)
    GO
     
    INSERT INTO myTable99([name], [date], Amount)
    SELECT 'Imran', '2005-08-01', 5000 UNION ALL
    SELECT 'Imran', '2005-09-01', 5000 UNION ALL
    SELECT 'Imran', '2005-10-01', 7000 UNION ALL
    SELECT 'Raja', '2005-08-01', 5000 UNION ALL
    SELECT 'Raja', '2005-09-01', 7000 UNION ALL
    SELECT 'Raja', '2005-10-01', 7000
    GO
     
    SELECT COALESCE(xxxName,'') AS [NewName]
        , a.[date]
        , a.Amount 
    FROM  myTable99 a 
      LEFT JOIN 
     (SELECT [name] AS xxxName, Min([date]) AS [date]
            FROM  myTable99 
        GROUP BY [name]) AS xxx ON 
      a.[name] = xxxName 
      AND a.[date] = xxx.[date]
    ORDER BY 
     a.[name]
     , a.[date]
    GO
     
    SET NOCOUNT OFF
    DROP TABLE myTable99
    GO
    Works with SQL Server. Coalesce = Nz in Access.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    May 2006
    Posts
    20

    Suppress repeat Customer Name

    Thank you Sir. It seems too complex and I'm
    not sure whether it can work in Access. As a
    second thought I think of following:

    IIF(FirstTimeName <> companyname, FirstTimeName, "-")

    FirstTimeName is a variable to hold companyname
    that appears firsttime on the table. To use that
    variable I need to give some initial value to
    FirstTimeName variable like FirstTimeName = "xyz".
    But is it possible to define variable and variable value
    like this in Microsoft Access. Pls advise.

    Thank you Sir,
    Rakesh

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Rakesh

    The logic is not actually that complex.

    The query creates a derived table (you could make this a totally separate query if you like to "partition it off" in your own mind). For your data, this query would get just one ordernumber per customername e.g.
    Code:
    SELECT CustomerName AS NonRepeatName, MIN(OrderNumber) AS FirstOrderNumber
    FROM MyTable 
    GROUP BY CustomerName
    You then left join this to your table (so you get all the results from your table but only the matching rows from your query above) on OrderNumber = FirstOrderNumber. That way NonRepeatName only appears once against the lowest order number for each customer - it does not repeat.

    There is one alternative to this that I can think of. It involves use of Iif but is probably more complex than the above. The variable idea wouldn't work I am afraid.

    Basically - this is trynig to force a square peg into a round hole. SQL is versatile but isn't really well suited for formatting like this. Again I would recommend you investigate GridViews and see if this can be done at the client.

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    May 2006
    Posts
    20
    Thank you. Meanwhile, I have post a thread for the suggestion getting the result in GridView.

    kind regards,
    Rakesh

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •