Results 1 to 6 of 6
  1. #1
    New Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Removing duplicate data from report (2002)

    Have a report that uses a query that combines data from three tables. Needed to suppress the field for an order that contains the freight cost. Tried using the visible no to suppress the data. That works as far as showing on the report, but the value is added to the total cost for each line item on the order. Next I added a function to the query to zero out the duplicate freight. This works on the query, but now I do not get the freight added to the total cost. It does show on the report, and only once if there are multiple line items. Need to know how to get the freight to show only once and also be added to the total for an order.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicate data from report (2002)

    I'm unclear on the situation you are presenting. You say there are three tables; what are they? In which table is the freight cost being held?

    I can offer the way that I typically represent an Order to LineItem situation. Since 1 Order has many LineItems these are two tables with a 1 to many relationship. If your FreightCost is applied on each Order, then it should be a data field in the Orders table. That way, when you total the LineItems, you can add the Order.FreightCost once. Right now it seems as if you are suggesting that it shows up on each LineItem and I wonder if that isn't because the field has been placed on the many side of the relationship.

    I would think that you shouldn't have to continually remove the FreightCost if the design is appropriate.
    HTH

  3. #3
    New Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicate data from report (2002)

    The Freight in only contained in the Order record, but when the query is run then the freight field is associated with each line item for the order. When displaying the the detail for a line item the freight is there along with the quantity and cost for the item. See the attached file for a before and after view of the report.

  4. #4
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicate data from report (2002)

    I don't see an attachment.
    Have you tried using a subreport for LineItems? The main report comes from the Order table (not a qry that links the Order and LineItem tables) and in the detail section place the subreport which comes from the LineItem table. They link on something like OrderNumber. If you need help with this post back.

  5. #5
    New Lounger
    Join Date
    Mar 2001
    Location
    Texas
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Removing duplicate data from report (2002)

    Don't know why the attachment was not there. As for a sub report, no don't think it would work. This is a report that has a running sum on it.
    Attached Files Attached Files

  6. #6
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    USA
    Posts
    379
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Some one else on this?

    After looking at the attachment, I believe that your running sum could be represented by a sum in a group footer and then a subreport should work. But if you want to do it another way, possibly someone else may have a suggestion more in line with your question.

Posting Permissions

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