I'm a first time poster and have only a basic knowledge of Access.
I have two tables: tblShipments and tblOrders.
tblOrders has a primary key of Order#
tblShipments has a primary key of Shipment#
Each ONE Order can have MANY Shipments (we sometimes ship partial orders).
In tblOrders, I have the following fields:
Order#
Item
QtyOrdered
QtyShipped
In tblShipments, I have the following fields:
Shipment#
Order#
QtyShipped
ShipDate
I am trying to run a query that will take the QtyShipped value from tblShipments and put it in the QtyShipped field in tblOrders. This works with an Append Query. However it only works when there is only ONE shipment per order. How do I group like fields (Order#) together and sum their QtyShipped fields?
For example:
tblOrders
Order# - A
Item - PartZ
QtyOrdered - 10
QtyShipped - 0
tblShipments
Shipment# - 100
Order# - A
QtyShipped - 4
DateShipped - 07/15/05
Shipment# - 101
Order# - A
Qty Shipped - 5
DateShipped - 07/16/05
In tblOrders, I need the QtyShipped field to be a sum of QtyShipped from tblShipments grouped by the Order#. So it should read like this:
tblOrders
Order# - A
Part# - PartZ
QtyOrdered - 10
QtyShipped - 9 (4+5)
Is this possible? What kind of query could do this for me? Like I said, I can use an Append Query to take QtyShipped from tblShipments and put it into QtyShipped in tblOrders, but if there are multiple shipments for one order, then that would become a problem.
Any help would be greatly appreciated.
I have two tables: tblShipments and tblOrders.
tblOrders has a primary key of Order#
tblShipments has a primary key of Shipment#
Each ONE Order can have MANY Shipments (we sometimes ship partial orders).
In tblOrders, I have the following fields:
Order#
Item
QtyOrdered
QtyShipped
In tblShipments, I have the following fields:
Shipment#
Order#
QtyShipped
ShipDate
I am trying to run a query that will take the QtyShipped value from tblShipments and put it in the QtyShipped field in tblOrders. This works with an Append Query. However it only works when there is only ONE shipment per order. How do I group like fields (Order#) together and sum their QtyShipped fields?
For example:
tblOrders
Order# - A
Item - PartZ
QtyOrdered - 10
QtyShipped - 0
tblShipments
Shipment# - 100
Order# - A
QtyShipped - 4
DateShipped - 07/15/05
Shipment# - 101
Order# - A
Qty Shipped - 5
DateShipped - 07/16/05
In tblOrders, I need the QtyShipped field to be a sum of QtyShipped from tblShipments grouped by the Order#. So it should read like this:
tblOrders
Order# - A
Part# - PartZ
QtyOrdered - 10
QtyShipped - 9 (4+5)
Is this possible? What kind of query could do this for me? Like I said, I can use an Append Query to take QtyShipped from tblShipments and put it into QtyShipped in tblOrders, but if there are multiple shipments for one order, then that would become a problem.
Any help would be greatly appreciated.