F
Flemming
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
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