Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38

    Unanswered: report using a query for outstanding orders

    Hi i am trying to do a report for outstanding orders. I have two table 1, called order which is where the order is placed. I have another table called order history. this table is used when orders have been delivered and can be used to check how many items have not been delivered.

    I have attached a screen shot of my two tables

    here is the query i am using but it is not doing what i want

    SELECT Order.OrderLineID, Order.[Date Required], Order.[Quantity Ordered], Order.[Unit Price Paid], Order.[Agreed Delivery Date], Order.PartID AS Order_PartID, Order.Destination, OrderHistory.OrderHistoryID, OrderHistory.[Delivery Accepted By], OrderHistory.[QTY Delivered], OrderHistory.[Qty Rejected], OrderHistory.[Qty Accepted], OrderHistory.[Reason for rejection], OrderHistory.[Date Delivered], OrderHistory.[O/S Deliveries], Order.PurchaseOrderID
    FROM ((Project INNER JOIN Purchase ON Project.ProjectID = Purchase.ProjectID) INNER JOIN [Order] ON Purchase.PurchaseOrderID = Order.PurchaseOrderID) INNER JOIN OrderHistory ON Order.OrderLineID = OrderHistory.OrderlineID
    WHERE (((Project.ProjectName)=[Forms]![Order]![Text25]));
    Attached Thumbnails Attached Thumbnails tables.jpg  
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  2. #2
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238

    Re: report using a query for outstanding orders

    Good Morning (Afternoon) Kabir!

    I see what you're trying to do here... but what do you mean it's not doing what you want?... Are you getting an error message?... Is it returning the wrong records? What's in [Forms]![Order]![Text25]?

  3. #3
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    Hello Trudi

    what im trying to do is have a report that basically shows all orders that have been placed but have not been delivered. At present because of my relation diagram if i have not filled in the Orderhistory(table) all orderes that have had no deliveries will not show up on the report.


    the textbox25 is a query linked to the text box so when i run the query i do not have to type in the order number again. In the diagram textbox25 is the Destination title on the form header

    many thanks Trudi
    Attached Thumbnails Attached Thumbnails orderform.jpg  
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

  4. #4
    Join Date
    Dec 2003
    Location
    Toronto, Ont. Canada
    Posts
    238
    Hi i am trying to do a report for outstanding orders. I have two table 1, called order which is where the order is placed. I have another table called order history. this table is used when orders have been delivered and can be used to check how many items have not been delivered.
    If the orders haven't been delivered, then there won't be any records in orderhistory table for that order... Why not just start off by querying Order to find the records with no matching record in OrderHistory?

    Select * From Order
    Where Order.OrderLineID Not In (Select OrderLineID From OrderHistory);

    That should give you the records you want to see on your report... right?

  5. #5
    Join Date
    Dec 2003
    Location
    Derby, England
    Posts
    38
    Hi trudi

    many thanks for your help however i tried the query and all i get is a blank data sheet. Once i execute the query

    here is the code i am using im not entirely sure why i am getting the error message

    SELECT Order.*
    FROM [Order]
    Where Order.OrderLineID Not In (Select OrderLineID From OrderHistory);



    once again many thanks
    Kabir Hussein

    http://www.angelfire.com/theforce/precious/index.html

Posting Permissions

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