Sum field with like *other* field

[jnr927]

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.

 

[RV]

I am trying to run a query that will take the QtyShipped value from tblShipments and put it in the QtyShipped field in tblOrders.

It really bad practive to store the same data in multiple tables.
Now what you need to do is this.

First, create a query that sums the QtyShipped per Order:

SELECT OrderID, SUM(QtyShipped) As Total
FROM tblShipments
GROUP BY OrderID;

Now save this query, f.i. as qryTotalShipped
Next, create a new one that joins the query above with your tblOrders to get the total QtyShipped per Order:

SELECT OrderID, Total
FROM tblOrders INNER JOIN qryTotalShipped ON tblOrders.OrderID = qryTotalShipped.OrderID;

Each ONE Order can have MANY Shipments (we sometimes ship partial orders).

Are you dealing with combined Shipments?
If so, you'd have a many to many relationship and therefore would require a junction table.

RV

 

[jnr927]

Thanks so much for the help. I will give it a try right now.

Are you dealing with combined Shipments?
If so, you'd have a many to many relationship and therefore would require a junction table.

Yes, ONE shipment can contain MANY orders as well. So yes, I guess I need to change my relationship to a MANY to MANY. I guess I don't know what you mean by a junction table. How would I go about doing this and would this change your previous instructions regarding the SQL statements?

Thanks again.
JNR

 

[jnr927]

Just wanted to add that I tried the queries, and the first one did exactly what it was supposed to. However when trying to run the second query, an error message came up saying:

The specified field 'OrderID' could refer to more than one table listed in the FROM clause of your SQL statement.

This is the SQL statement that I have:
SELECT OrderID, Total
FROM tblOrders INNER JOIN qryTotalShipped ON tblOrders.OrderID=qryTotalShipped.OrderID;

Any ideas?

Would it be better if instead of having a QtyShipped field in tblOrders, there be a QtyRemaining field? So tblShipments was the only table with QtyShipped? Could I run a query that would calculate what the QtyRemaining is? Just a thought....

 

[RV]

JNR,

there are lots of threads on the forum regarding junction tables.
I suggest you use the search facility, query on "many-to-many or" "junction table".

As your table structure would change, yes, the query would have to be adapted.
First try to implement the junction table yourself.
Then try to adapt the query.
The NorthWind sample database can help you on this one, I guess it comes with similar queries (= queries involving junction tables).

If you get stuck along the way, well, come back and ask for help

RV

 

[RV]

However when trying to run the second query, an error message came up saying:

The specified field 'OrderID' could refer to more than one table listed in the FROM clause of your SQL statement.

My mistake
Statement should read

SELECT tblOrders.OrderID, qryTotalShipped.Total
FROM tblOrders INNER JOIN qryTotalShipped ON tblOrders.OrderID=qryTotalShipped.OrderID;

Would it be better if instead of having a QtyShipped field in tblOrders, there be a QtyRemaining field?

No, you'd use the same approach as for your original question.
Use the same queries as provided (you'll have to adaprt the queries if you implement your junction tables).

Now add a calculated column to the second query to calculate the difference between your QtyOrderded and the summed QtyShipped.

RV

 

[jnr927]

Okay, everything works. However, when I run the query, how do I get the values to tblOrders?

I want to be able to open tblOrders and see that an order has been placed for 10 parts, but only 5 have been shipped, so there are 5 remaining on order. Appending it doesn't work because of the primary key issue.

Is this something that I am going to have to display in a form instead?

Thanks for EVERYTHING.

JNR

 

[jnr927]

I can display everything I need into forms - which is fine because that is how information is going to be viewed most of the time.

My only problems now, are that I have an Update Query called qryCloseOrders. It will be run daily in order to close all orders that have '0' remaining items to be shipped. I try to add tblOrders and the second query you gave me (called qryUpdateShipments) but it says that the query is not updateable. I am not trying to update the query, but simply use the information it provides in order to close the order.

Here is how my query looks now:

tblOrders
Order Closed (yes/no) Criteria: No ... Update To: Yes

qryUpdateShipments
QtyRemaining - Criteria: <=0

The error message when running the query:
"Operation Must Use an Updateable Query"

Is there another way to do this? I need some query to be run that will close orders when the order is completed without manually going in to each order and doing it. If there are 100 orders in a day, I would hate to have to go into every single one and click "Order Closed". The only reason I need this is because there is a report ran that shows all Open Orders which is based on the same yes/no field.

Any ideas?

 

[RV]

I'd use a subquery:

UPDATE tblOrders
SET tblOrders.OrderClosed = -1
WHERE tblOrders.OrderId IN
(
SELECT qryTotalShipped.OrderId
FROM qryTotalShipped
WHERE qryTotalShipped.Total = tblOrders.QtyOrdered
)

PS prevent using spaces in object names (as you do in your column Order Closed)

RV

 

[jnr927]

Thanks RV, you're the best.

JNR

 

[jnr927]

NEVERMIND!