Results 1 to 6 of 6
  1. #1
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Report Formulas with Grouping (2000)

    Hi,
    I've attached a db for your review if necessary. I have a form, once that form is open you choose a JON, then click preview. Once that report comes up it lists all the parts for that particular JON. I have totaled the unitprice without problem. My problem is I'd like to add the shipping next to the parts total. But my problem is, the unitprice is per part, where the shipping is per order. Parts and Orders are kept in different tables. I actually have two order tables tbl4172A and tblPurchaseRequest. I've used a union query to bring them together.

    I can't figure out how to get the shipping on this report correctly. If I have to do just one shipping clump in the report footer I can live with that instead of by each RecSource footer.

    Any help would be appreciated.
    Thanks,
    Deb

  2. #2
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Formulas with Grouping (2000)

    It's not clear to me what shipping cost you want and where they have to come from, but can't you use a DSum function in the wanted table or query with the appropriate condition ?
    Francois

  3. #3
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Formulas with Grouping (2000)

    On the report I list each part separate grouped by RecSource. Each part may have an order, which may have shipping. I would like that shipping to be listed in the RecSource Footer next to the sum of the unitprice. If that's not possible, at least I haven't been able to get it to work, then maybe just a a total of shipping for all orders for that machine that is listed in the strMaterialJON footer.
    Thanks,
    Deb

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Formulas with Grouping (2000)

    I think you may have a structure problem here.
    I don't see how you can set a relation between an order and a recommended source as your report is based on parts (I think)
    Francois

  5. #5
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Formulas with Grouping (2000)

    That relationship isn't the problem. But I think you may be right in being able to get the data the way I want due to my table design. The Orders table is related to the Parts table which is related to the RecSource table. My problem is getting the shipping which is per order to sum correctly. I'm going to give the DSUM a shot and see if that helps.
    Thanks,
    Deb

  6. #6
    4 Star Lounger
    Join Date
    Oct 2002
    Location
    Sayre, Pennsylvania, USA
    Posts
    504
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Report Formulas with Grouping (2000)

    After a few trys and finally using the proper syntax the DSum function did the trick. I had to create another query that gave me the proper shipping, but it worked. I always forget about that function. Thank you for your help!

    Deb

Posting Permissions

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