Results 1 to 7 of 7

Thread: query help

  1. #1
    Join Date
    Jun 2006
    Posts
    66

    Unanswered: query help

    I have the following query

    Code:
    SELECT tblcs1Orders.O_ID, tblcs1Orders.O_CustomerID, tblcs1Orders.O_Details, tblcs1Versions.V_Weight, tblcs1Orders.O_TotalPrice, tblcs1Orders.O_Date
    FROM tblcs1Versions RIGHT JOIN (tblcs1InvoiceRows RIGHT JOIN tblcs1Orders ON tblcs1InvoiceRows.IR_OrderNumberID = tblcs1Orders.O_ID) ON tblcs1Versions.V_CodeNumber = tblcs1InvoiceRows.IR_VersionCode;
    This returns a record for every invoicerow belonging to the order
    what i need is 1 row for every order but with the joined table to get the sum(weight) for each order

    is this possible to do in access?

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Could you describe the relationship between tblcs1Orders and tblcs1Versions?
    In fact, a diagram/screenshot of the relationships would be even better
    George
    Home | Blog

  3. #3
    Join Date
    Jun 2006
    Posts
    66
    there is no direct link between orders and versions
    in invoicerows there is an order id and versioncode which links to the version code in tblcs1versions

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    so you have a product table (which presumably knows the products weight), you have an order table and an order items table).

    you are already pulling the product details Id guess for invoice / despatch note so 'all' you need to do is to pull the product weight and multiply it by the quantity ordered


    eg

    select Orders.OrderiID, OrderDetail.ItemID, OrderDetail.Qty, OrderDetail.ProdID, Products.Weight, (OrderDetail.Qty * Products.Weight) as LineItemWeight, ......<blah> from Orders
    <...insert appropriate join clauses......>
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yeah, I reckon this can be done - think you might want inner joins rather than rights (if memory serves, access doesn't like right joins (you have to use reverse left?))...
    If you're using a sum, you'll need some grouping.
    I had a go at recreating your problem and here's my solution so far...
    Code:
    SELECT	 tblcs1Orders.O_ID
    	,tblcs1Orders.O_CustomerID
    	,Sum(tblcs1Versions.V_Weight) AS SumOfV_Weight
    FROM 	 tblcs1Versions
    INNER JOIN (tblcs1Orders INNER JOIN tblcs1InvoiceRows ON tblcs1Orders.O_ID = tblcs1InvoiceRows.IR_OrderNumberID) ON tblcs1Versions.V_CodeNumber = tblcs1InvoiceRows.IR_VersionCode
    GROUP BY tblcs1Orders.O_ID, tblcs1Orders.O_CustomerID;
    George
    Home | Blog

  6. #6
    Join Date
    Jun 2006
    Posts
    66
    thank you -- looks like just what i needed

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Really? Yay
    Let us know how you get on with it!
    George
    Home | Blog

Posting Permissions

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