Results 1 to 11 of 11

Thread: Orders Database

  1. #1
    Join Date
    Feb 2004
    Posts
    10

    Unanswered: Orders Database

    I have succesfully created an order database for my customers...in this database I can create reports that which months which products has to be manufactured depending the orders given by customers etc...

    little bit more information...i have a customer table and each customer may give many orders. in the orders table there is ship date etc...


    MY problem is that I have created querys and reports that calculates amount of products ordered that have to be completed weakly For Example for 7 May I have 4 X product and for 15 June I have more X products ordered.....BUT well after we fill the specific order and ship them the X order due 7 May is still in the total product and in all querys I need to find a way to not to include in my queries and reports....

    here is the relationship table....

    I would really appreciate if you can be specific because i am really new to access...
    Attached Thumbnails Attached Thumbnails db.gif  

  2. #2
    Join Date
    Feb 2004
    Posts
    10
    here is example of a query that will have problems
    Attached Thumbnails Attached Thumbnails db2.gif  

  3. #3
    Join Date
    Aug 2002
    Location
    Northampton, England
    Posts
    266
    You need a date criteria in the ShipDate field to filter out the orders that have been shipped.

  4. #4
    Join Date
    Feb 2004
    Posts
    10
    yes i was going to do that do you guys have example...lets say...if the ship date is before the =(now) don't show it..

    can you tell me how to write it please...

    thanks

  5. #5
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    where shipdate >= date() or shipdate is null

    Regards

  6. #6
    Join Date
    Feb 2004
    Posts
    10

    Unhappy

    i put it like shipdate >= date() this to the criteria field in the query but it doesnt work

  7. #7
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    select * from orders
    where shipdate >= date() or shipdate is null

  8. #8
    Join Date
    Feb 2004
    Posts
    10
    i am really sorry but i am really new to access....

    here is my SQL view of the query where would i add the criteria
    PHP Code:
    SELECT Orders.OrderIDOrders.CustomerIDOrders.ShipDateSum([Order Details].Quantity) AS [Total Units], Sum([Sum of Production Time].Days) AS SumOfDays, [Order Details].ProductID, [Order Details].Quantity
    FROM Orders INNER JOIN 
    ([Order DetailsINNER JOIN [Sum of Production TimeON [Order Details].ProductID=[Sum of Production Time].ProductIDON Orders.OrderID=[Order Details].OrderID
    GROUP BY Orders
    .OrderIDOrders.CustomerIDOrders.ShipDate, [Order Details].ProductID, [Order Details].Quantity
    ORDER BY Orders
    .ShipDateSum([Order Details].Quantity); 

  9. #9
    Join Date
    Dec 2003
    Location
    Dallas, TX
    Posts
    1,004
    Emree,
    If you don't want to show any dates before the current date, in your query in the ShipDate field place this in the Criteria section:
    Date()
    Simply put, that will reflect only ship dates that are for the current day and no dates before. See if that gets you what you want.

    A better way is to put a checkbox in your form to check if it has shipped. (1)Place a field in your Table for Shipped Y/N. (2)Then pull that into the query your form is based on. (3)In that query in the Shipped field place this in the criteria section: 0 That being a Zero, which tells the query to show only items that Have Not shipped. (4)Next in your form pull that same field Shipped into it and make it a CheckBox. This way you can always go back and pull the Shipped items vs Non-Shipped in a later query.(5) you're done. That is a way I have my database setup. Try it and see if you like the results. Someone may still have an even better way, just trying to help.
    Bud

  10. #10
    Join Date
    Jan 2004
    Location
    The Netherlands
    Posts
    421
    Dont post sql as unreadable as that, try this:
    Code:
    SELECT 
    	Orders.OrderID, 
    	Orders.CustomerID, 
    	Orders.ShipDate, 
    	Sum([Order Details].Quantity) AS [Total Units], 
    	Sum([Sum of Production Time].Days) AS SumOfDays, 
    	[Order Details].ProductID, 
    	[Order Details].Quantity 
    FROM 
    	Orders INNER JOIN (
    	[Order Details] INNER JOIN 
    	[Sum of Production Time] ON [Order Details].ProductID=[Sum of Production Time].ProductID) 
    		ON Orders.OrderID=[Order Details].OrderID 
    WHERE
    	shipdate >= date() 
    or 	shipdate is null
    GROUP BY 
    	Orders.OrderID, 
    	Orders.CustomerID, 
    	Orders.ShipDate, 
    	[Order Details].ProductID, 
    	[Order Details].Quantity 
    ORDER BY 
    	Orders.ShipDate, 
    	Sum([Order Details].Quantity);

  11. #11
    Join Date
    Feb 2004
    Posts
    10

    Talking

    sorry

    I really appreciate it thank you very much it works

Posting Permissions

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