Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removal of duplicate data in reports (2003)

    I am trying to produce a report but what l need to do is to put any remove any duplicate occurances of the site ID

  2. #2
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Removal of duplicate data in reports (2003)

    - Open your report is design mode
    - Left click on the upper left box and select sorting and grouping
    --- Under field expression, select Site ID
    --- Under Group Header, Select Yes (this adds a Site ID header portion to your report)
    - Go to your report in design mode and move SiteID to the SiteID header area.
    - Open the report and you should only see Site ID once and the remaining data below it. (The remaining data should be in the detail section.)
    - Format as necessary

    HTH

    Note: You may need to add the footer if you are inserting totals.
    Regards,

    Gary
    (It's been a while!)

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Removal of duplicate data in reports (2003)

    One last item, to remove duplicate occurences, in your dataset for the report, change the query from Select... to Select Distinct.

    This will remove the duplicates but will not fix and data problems that exist that are causing the duplicates.
    Regards,

    Gary
    (It's been a while!)

  4. #4
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of duplicate data in reports (2003)

    Many thanks for that, that

  5. #5
    4 Star Lounger
    Join Date
    Jul 2006
    Posts
    531
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removal of duplicate data in reports (2003)

    If l want to select the Site ID as distinct where do l put it into the query?
    This is the query:


    SELECT result1.*
    FROM result1;

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Removal of duplicate data in reports (2003)

    Select distinct fields from objects;

    Distinct comes after Select. This will select distinct records returned by the site id.

    To just select the distinct Site ids, try Select Distinct SiteID from wherever;
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

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