If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
query - sum up and assign records to category
Help me!!!! i could not even give a descriptive subject.... :-(
I do not know if one or two queries is enough to solve my problem or if i need some modification of my database- Four tables: PurchaseOrders: OrderNo, OrderDate OrderDetails: Id, Product, OrderNO, ordQTY Invoices: InvoiceNo, Product, ShipmentDate, Arrival InvoiceDetails: Id, ContainerNo, conQTY, InvoiceNo, OrderNo --- I have made a form for typing each invoice with a subform for invoicedetails. When im adding a record in the subform it would be fantastic if i could make a drop down list with the orders still not finished. This means that i would like to make a query that return the orders still not completed. More details about the situation. Each Purchase order can consist of more than one product. eg. 2000L A and 1500L B. A delivery (invoice) does not directly reflect a purchase order and consist of only one product. eg 500L B split op in smaller parts (containers) eg 10*50L B. This means that a delivery can be part of eg. two purchase orders. So whether a container is part of one or the other order depends on the previous added containers. Im not good at explaining all this so ill give an example: Each container is appx 20L Order no 1: 60L product A and 20L product B Order no 2: 40L A Order no 3: 80L A and 40L B 1st delivery (invoice) 4 container of A (80L) this means that the first 3 containers belong to order no 1 and the fourth belong to order no. 2 2nd delivery: 100L product A (five containers) The first container belongs to order no 2 which hereby is completed. The rest of the containers belong to order no. 3 3rd delivery: 60L Product B. Both order no. 1 and no. 2 is herby complete. So how do i make a query that determs which Order each container belongs ???? I have considered adding some [completed] fields to the Order tables. But then ill need a query to update the tables when a record is added (or something.....??) all help appreciated. Thanks in advance Flemming |
#2
|
|||
|
|||
To create a "Drop Down List" (Access calls these Comboboxes, FYI) of 'not
finished' orders, you would first need to create a query that selects all of the not finished orders. Then set the rowsource of the combobox to that query. Should be very easy, depending on what your definition of a not finished query is. I think that you should have the OrderNo in the Invoices table, instead of Hopefully, you do not have the delima that a single order could be placed in Multiple container, but even if you do, it doesn't matter. You can make a query, with a Group By, that will show the containers associated to each order. Select PurchaseOrders.OrderNo, ContainerNo FROM PurchaseOrders INNER JOIN ...[let the QBE build this, by adding all the tables to the query] ... GROUP By PurchaseOrders.OrderNo, ContainerNo This will give you the unique containerNo's that have been associated to the Invoice Details that are associated to the Purchase Orders. -- Steve Clark, Access MVP FMS, Inc. Call us for all of your Access Development Needs! 1-888-220-6234 www.fmsinc.com/consulting "Flemming" wrote in message ... Help me!!!! i could not even give a descriptive subject.... :-( I do not know if one or two queries is enough to solve my problem or if i need some modification of my database- Four tables: PurchaseOrders: OrderNo, OrderDate OrderDetails: Id, Product, OrderNO, ordQTY Invoices: InvoiceNo, Product, ShipmentDate, Arrival InvoiceDetails: Id, ContainerNo, conQTY, InvoiceNo, OrderNo --- I have made a form for typing each invoice with a subform for invoicedetails. When im adding a record in the subform it would be fantastic if i could make a drop down list with the orders still not finished. This means that i would like to make a query that return the orders still not completed. More details about the situation. Each Purchase order can consist of more than one product. eg. 2000L A and 1500L B. A delivery (invoice) does not directly reflect a purchase order and consist of only one product. eg 500L B split op in smaller parts (containers) eg 10*50L B. This means that a delivery can be part of eg. two purchase orders. So whether a container is part of one or the other order depends on the previous added containers. Im not good at explaining all this so ill give an example: Each container is appx 20L Order no 1: 60L product A and 20L product B Order no 2: 40L A Order no 3: 80L A and 40L B 1st delivery (invoice) 4 container of A (80L) this means that the first 3 containers belong to order no 1 and the fourth belong to order no. 2 2nd delivery: 100L product A (five containers) The first container belongs to order no 2 which hereby is completed. The rest of the containers belong to order no. 3 3rd delivery: 60L Product B. Both order no. 1 and no. 2 is herby complete. So how do i make a query that determs which Order each container belongs ???? I have considered adding some [completed] fields to the Order tables. But then ill need a query to update the tables when a record is added (or something.....??) all help appreciated. Thanks in advance Flemming |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Assign Query to Form or Orderby | Thomas Kroljic | Using Forms | 5 | September 6th, 2005 01:59 PM |
Append Query to Delete duplicate records | Randy | Running & Setting Up Queries | 2 | January 12th, 2005 06:04 PM |
How to assign passthrough query into listbox rowsource | Sunny | New Users | 1 | January 10th, 2005 09:09 PM |
Attn Sprinks- Not duplicate insert records | babs | Using Forms | 1 | December 13th, 2004 06:25 PM |
Assign a Primary Key in Query | Rob Meiner | Running & Setting Up Queries | 2 | October 27th, 2004 06:58 PM |