query - sum up and assign records to category

[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

 

[[MVP] S.Clark]

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
(e-mail address removed)
www.fmsinc.com/consulting

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