Results 1 to 6 of 6
  1. #1
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Duplicate Data in Report (A 97)

    In my db there's a form that allows users to enter a Project and it's corresponding Products (in a subform so that multiple products can be selected for each project). The problem with my report is that for each Project where there is more than one Product, the report duplicates the entire Project's information as many times as there are Products. For example: In Project A there are Products 1, 2, and 3. The report will display Project A (with Product1 , 2, 3), Project A (with Product 1,2,3), Project A (with Product 1, 2, 3). If there were only two Products assigned to Project A, the report would only duplicate it twice. Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Data in Report (A 97)

    You'll have to tell us a bit more about how your report is set up.
    1. <LI>What is the Record Source of the report?
      <UL><LI>The Projects table - with products displayed in a subreport based on the Products table (I suppose). If so, how are the main report and subreport linked (i.e. what are the Master and Child Linking fields)?
      <LI>A query based on the Projects and Products table. If so, can you post the SQL of this query? (You can see the SQL if you open the query in design view and select View/SQL.)
    <LI>What are the Sorting and Grouping settings of the report?[/list]With this information, Loungers reading this thread will have a better chance of helping you.

  3. #3
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Data in Report (A 97)

    A bit more explanation:
    You are exactly correct about the products subreport info coming from a Products Table. The subreport is linked to the main report by ProjectID (an autonumber field) in which the child and master linking fields are set to this ProjectID.

    The SQL for the query based on the Projects and Products table is very simple in that it just pulls ProjectNumber and ProductName from the Products Table and is linked also by ProjectID.

    The report is sorted by date then by person responsible for the project

  4. #4
    Lounger
    Join Date
    Mar 2003
    Location
    Gainesville, Florida, USA
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Data in Report (A 97)

    You can create a group header for your project information by selecting the Project field in the sorting and grouping option of your report. The detail product information will remain where it is.
    You can refer to "Group Header" section of your Access online help.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Duplicate Data in Report (A 97)

    If the record source of your main report is a query based on the Project AND Products table, it will display as many records for each ProjectID as there are products for this ProjectID. You can check this by opening the query directly.
    Since product info is displayed in a subreport, you shouldn't use the query as record source for the main report - use the Projects table instead.

  6. #6
    Lounger
    Join Date
    Jun 2002
    Location
    RTP, NC, USA
    Posts
    40
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Duplicate Data in Report (A 97)

    Thanks Hans...you hit the nail on the head! I had some sort of misunderstanding about the way these things are supposed to be designed. I had the same information pulling into the query the main report runs from, as in the subreport's query...so indeed, duplicate data was returned.

Posting Permissions

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