How to Loop Through Data

[mattc66 via AccessMonster.com]

I need some help. I need to match order details to our inventory and
determine what order can be filled based on our current stock.

Example:
OrderDetail
OrderID ProductID OrdQty
1000 901001 5
1000 901002 1
1000 901005 2
1002 901001 4
1002 901002 3

Inventory
ProductID OnHandQty
901001 8
901002 4
901005 5

This is a very short example of the data I have to match. I wanted to do it
with a query. From what I have been advised is I need to use code and loop
through the orders and the product stock to determined which order can ship
and which ones cannot.

Can anyone give me some help with the code?

Thanks
Matt

 

[Marshall Barton]

I need some help. I need to match order details to our inventory and
determine what order can be filled based on our current stock.

Example:
OrderDetail
OrderID ProductID OrdQty
1000 901001 5
1000 901002 1
1000 901005 2
1002 901001 4
1002 901002 3

Inventory
ProductID OnHandQty
901001 8
901002 4
901005 5

This is a very short example of the data I have to match. I wanted to do it
with a query. From what I have been advised is I need to use code and loop
through the orders and the product stock to determined which order can ship
and which ones cannot.

Can anyone give me some help with the code?

Rather than leave you wondering about why you are getting
any replies, I'll just say that your problem statement is at
best incomplete.

In general, this kind of problem can be so complex that it
can not be solved within your lifetime. In some situations
there may be some special conditions that make it at least
partially solvable, but even then it could very well take
hours to compute whatever it is you think is within the
bounds of an acceptable solution.

 

[mattc66 via AccessMonster.com]

I guess thanks for the response may be in order. On the other hand, no
response would have been just as satisfying.

I need some help. I need to match order details to our inventory and
determine what order can be filled based on our current stock.

[quoted text clipped - 20 lines]

Can anyone give me some help with the code?

Rather than leave you wondering about why you are getting
any replies, I'll just say that your problem statement is at
best incomplete.

In general, this kind of problem can be so complex that it
can not be solved within your lifetime. In some situations
there may be some special conditions that make it at least
partially solvable, but even then it could very well take
hours to compute whatever it is you think is within the
bounds of an acceptable solution.

 

[Marshall Barton]

An answer is usually preferable, but at least you now know
that investing more thought into exactly what it is that you
need has some (small?) chance of getting something useful.

If you don't work on a clear set of specifications of your
problem, there is no chance of getting much help towards an
acceptable solution.

I'm not trying to beat you up over your question. It's just
that there is a lot more specific information is needed
before anyone (here or elsewhere) can provide productive
assistance.

I suggest that you sit down with pencil and paper along with
a substantial sample of your data. Work your problem by
hand, taking careful note of each decision along the way,
Then try to specify what procedural rules apply to each well
defined category of data.

It's probably relatively easy to come up with something if
the total orders are less than the inventory on hand. OTOH,
which orders should be filled when there is not enough in
inventory to fill all the orders?
--
Marsh
MVP [MS Access]

I guess thanks for the response may be in order. On the other hand, no
response would have been just as satisfying.

I need some help. I need to match order details to our inventory and
determine what order can be filled based on our current stock.

[quoted text clipped - 20 lines]

Can anyone give me some help with the code?

Rather than leave you wondering about why you are getting
any replies, I'll just say that your problem statement is at
best incomplete.

In general, this kind of problem can be so complex that it
can not be solved within your lifetime. In some situations
there may be some special conditions that make it at least
partially solvable, but even then it could very well take
hours to compute whatever it is you think is within the
bounds of an acceptable solution.

 

[AccessVandal via AccessMonster.com]

I would suggest another table for Issuing of Product? Although it is not
recommended that you keep calculated fields in your Inventory.OnHandQty as
required the rules of normalization, it is find with me.

tblIssueProduct
IssueID ProductID IssueQty
1001 901001 2
1002 901001 1
so on….

1. Sum the Total quantities for table OrderDetail, create a query.

Select ProductID, Sum(OrdQty) As OrderQty From OrderDetail Order By ProductID

2. Sum the Total quantities for table tblIssueProduct, create a query.

Select ProductID, Sum(IssueQty) As IssueQty From tblIssueProduct Order By
ProductID

3. Create a query from the Inventory table and in the query editor, insert
the two queries. Join the ID fields and in the Join Properties box, check 2
on the radio button. “Include ALL records from Inventory and only those
records from qryOrderDetail where the joined fields are equalâ€. This query is
a “Totals†query, meaning it is a “Group By†Query.

4. Same as for qryIssueProduct.

5. Inventory.ProductID --à qryOrderDetail.ProductID

6. Inventory.ProductID --à qryIssueProduct.ProductID

7. Fill/Insert into the Fields. Now, you should be able to create an
expression in a new field like..

CurrentBalance: Sum(OrderQty – IssueQty) this should give you the current
balance.

As you can see, you don’t need to use codes to loop through data.

It is very difficult to say that this will work for you as it will work for
me. For the Current Balance to work correctly, say ….. to determined the
physical in stock against the database stock.

If you start a productid with a balance zero, you are lucky. If not, you’ll
need to add the productid into the OrderDetail table with the correct balance
else the results will not be accurate. Meaning – you need to create an
adjustment for an order as a cover to create a starting point for a productid.


This is only a beginning where you will venture into more difficult design
environment where you need to master. Like what if the customer wants to use
an adjustment table for lost products or discontinued products?