Count groups

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi Folks,
I'm looking for some advice please. I have an orders table and a inventory
table - one to many. When the items in the inventory table are received and
used, I flag a field (closed) yes. What I would like to do is check the
inventory table per order number and when all items are closed I need to flag
the related orders table (related on orderno) closed also. Anyone have an
idea how to do this. I guess the pseudo code might look like this:

If all records with orderno 999 are flagged closed, flag the same orderno
record closed in the orders table. I would need to walk through the table
somehow to test them cause I don't want to manually check the orderno's.

Any help is appreciated.
Thank,
Bonnie
 
You would need to perform and Update query, that is within a loop of all the
desired order numbers.

--
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
 
Try SQL statement like:

UPDATE OrdersTable
SET OrdersTable.Closed = True
WHERE OrdersTable.OrderID IN
(SELECT Inventory.OrderID
FROM Inventory
GROUP BY Inventory.OrderID
Having Count(Inventory.OrderID) = Abs(Sum(Inventory.Closed)))

I would also run the converse of that as another query.
UPDATE OrdersTable
SET OrdersTable.Closed = False
WHERE OrdersTable.OrderID IN
(SELECT Inventory.OrderID
FROM Inventory
GROUP BY Inventory.OrderID
Having Count(Inventory.OrderID) <> Abs(Sum(Inventory.Closed)))

That should set all the values for you. On the other hand since
OrdersTable.Closed can always be calculated you might want to look at not
storing the data at all.

For an individual order you could use something like

UPDATE OrdersTable
Set OrdersTable.Closed = DCount("*","Inventory","OrderID =" & OrderID) =
Abs(DSum("Closed","Inventory","OrderID=" & OrderID))
WHERE OrdersTable.OrderID = 999
 
Back
Top