Results 1 to 12 of 12
  1. #1
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    Unanswered: Group by DEAL????

    A Deal consists of X number of Purchase Orders and Y number of Sales Orders.
    Each PO and SO have a Deal ID as a foreign key.

    I am trying to make an Order Tracking Report where all the Purchase Orders and Sales Orders are grouped and listed under the common Deal ID.

    If I do this with only Sales orders or Only Purchase orders there are no problems.

    But when I try both I cannot get out of the fact that I get all possible combos because the PO:s and SO:s are matched by the common Deal ID in my query.

    Therefore I end up getting all possible combinations of the SO.s and PO:s. In other words X*Y combinations.

    Like:

    Deal ID 1:
    -------------
    PO ID:...P PRICE. SO ID:...S PRICE…etc

    PO 1..... $123...... SO 1..... $123
    PO 2..... $123...... SO 1..... $123
    PO 1..... $123...... SO 2..... $123
    PO 2..... $123...... SO 2..... $123
    …………………..
    Deal ID 2:
    ...

    But what I want is a simple list of all orders, grouped by Deal ID.
    Like:

    Deal ID 1:
    -------------
    PO ID:...P PRICE. SO ID:...S PRICE…etc

    PO 1..... $123...... SO 1..... $123
    PO 2..... $123...... SO 2..... $123
    …………………
    Deal ID 2:

    ...

    Sounds simple enough, my boss thinks so… but I just cant get it right.


    Jo´
    Last edited by Johnny Dove; 05-22-02 at 13:54.

  2. #2
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81

    As a file. Only 30 k...Plz help...

    I really need help on this one. A lot of people viewing my post but no one answering. Hm, maybe unclear question. Therefore I have made a little .mdb file.

    If you take a look at the report in this file youll know what I mean.

    Jo´

    File approx. 30 k
    Attached Files Attached Files
    Last edited by Johnny Dove; 05-23-02 at 01:35.

  3. #3
    Join Date
    Feb 2002
    Posts
    403
    Create two queries. One queries the Order ID the other the Sales Order, both queries using DealID as a field.

    Create a final query using the DealID as a join in the query window, pulling the asterisk down to the grid should achieve the stated outcomes.

  4. #4
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    I dont understand how you mean I shall make the first 2 queries.

    Do you mean I shall make one query with all the fields just from Purchase Orders and One with all the fields from just Sales Orders. Then insert these 2 qeuries in a 3rd query with the 2 previous joined by Deal ID?

    Would that really change anything? With the 2 tables inserted plainly in a query each? If I only had 1 SO and PO it would work of course. It does so in a single query with the 2 tables also. But the system is built to handle multiple POs and SOs per DEAL. Say we buy a large stock and sell this to many different customers or vice versa.

    All queries I have tried so far want to show all possible combinations of the POs and the SOs. As in my exemple above.

    Jo´




    Originally posted by dynamictiger
    Create two queries. One queries the Order ID the other the Sales Order, both queries using DealID as a field.

    Create a final query using the DealID as a join in the query window, pulling the asterisk down to the grid should achieve the stated outcomes.

  5. #5
    Join Date
    Feb 2002
    Posts
    403
    See attached
    Attached Files Attached Files

  6. #6
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Oh, bad exemple from my side. PO Number and SO Number can not be matched. They are only identical in my bad exemple.

    Many times we will have one BIG purchase order, where we get a good price for buying many, and then many smaller Sales Orders. The SO Number and the PO Number can not keep the DEAL together. Also they are autonumbered and will run away from each other quickly. The SOs and POs are grouped by the common DEAL ID only.

    Better exemple:

    Deal ID 1:
    -------------
    PO ID:...P QTY......... SO ID:...S QTY…etc

    PO 55....2000............SO 30..... 1500
    ............................... SO 35......250
    ............................... SO 36......250

    …………………
    Deal ID 2:

    They dont have to be lined up like this in the report. All POs and all SOs just need to be grouped together under a common deal header.

    They could be:

    DEAL ID 1
    ..........
    Purchase Orders:
    PO55 QTY 2000

    Sales Orders:
    SO 30 QTY 1500
    SO 35 QTY 250
    SO 36 QTY 250


    I have tried to hide duplicate values in the report. But that method seems slightly inconsistent and also it will hide values that are the same. Not lines that are the same. In my exemple above it would hide SO36s QTY, 250, for being the same as in SO35.

    Maybe if I could hide a hole line if - the PO ID was identical to a previous line.

    Thanks for your help,
    Jo´

  7. #7
    Join Date
    Feb 2002
    Posts
    403
    I did that in my sleep, so I am not surprised at an error. Will look at properly in Morning.

  8. #8
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    Thanks, really apreciate it. My boss is bugging me big time about these reports.

    Jo´

    PS Whats Oz like this time of the year. If the boss fires me thats where I am going from here. Already have a ticket. DS

  9. #9
    Join Date
    Feb 2002
    Posts
    403
    Format is not the best but your report is fixed, see report called rptMasterDeal. ignore my mucking about.

    The format is 2000, I hope this is what you were using.
    Attached Files Attached Files

  10. #10
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    So I can use a Subreport! I didnt know they existed. Silly of me. Thats great. Thats exactly how I have designed it in the form view. With subreports. I can probably just us my queries as they are from there.

    Thanks a lot! I am very happy now. This has been bugging me a lot.

    Jo´
    Last edited by Johnny Dove; 05-25-02 at 05:57.

  11. #11
    Join Date
    Apr 2002
    Location
    Thailand
    Posts
    81
    So I can use a Subreport! I didnt know they existed. Silly of me. Thats great. Thats exactly how I have designed it in the form view. With Subforms. I can probably just us my queries as they are from there.

    Thanks a lot! I am very happy now. This has been bugging me a lot.

    Jo´

  12. #12
    Join Date
    Feb 2002
    Posts
    403
    That's fine, took me a few goes before I thought of it.

Posting Permissions

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