Looping thru multiple records

[chalrav]

Reading through multiple records, with Loop capability
--------------------------------------------------------------------------------

Hi,

I have three tables as below:

Table: Demand
ITEM_ID, QUANTITY
Item A, 10

Table: Route
ITEM_ID, OPERATION_NO, RESOURCE, TIME
Item A, 10, RES1, 60
Item A, 20, RES2, 120
Item A, 30, RES3, 60

Table: Load

RESOURCE, LOAD
RES1, 600
RES2, 1200
RES3 , 600

The values in LOAD field of the LOAD table are calculated values.

What I want to achieve?
I want to calculate the LOAD field in the LOAD table

Steps:
1) Select the records for every ITEM_ID (e.g. Item A ) in the Route table.
2) Start with the lowest number of Operation_No (e.g. Operation_No 10), check the following:
a. Value in the RESOURCE field (e.g. Res1) and value in the TIME field (e.g. 60).
3) Multiply the value in the ROUTE.TIME field with the corresponding value in DEMAND.QUANTITY . (ROUTE.ITEM_ID = DEMAND.ITEM_ID)
4) Update the Table Load with the following values:
a. RES1 in field RESOURCE
b. The multiplied product we got in Step 3 above in the LOAD.LOAD field.
5) Go back to Step 2 above and select the next lowest record for Item A ( Operation_No = 20 in this case) and perform the following steps:
a. Step 2.a
b. Step 3
c. Step 4
6) Go back to Step 2. Continue this iteration (loop) until we complete all the records for this item. (In this case, there are three records for Item A: Operation_No: 10, 20 & 30).
7) Once all the records for Item A are processed and the LOAD table is updated, then go back to Step 1 and select the next item.

[maxamis4]

My First question is how well do you know VB?

To do this you will have to do it programmaticall y. So in order to proceed you will have to know visual basic to start of with.

You will need to understand ADO in order to create record sets to compare too.

I will help you step by step if needed but it will be a long task if you do not understand VB.

[chalrav]

HI Maxamis,

Thanks for your quick reply.

I am sorry, but, I have no knowledge of VB. I hope you can help.

Thanks,

Ravi

[NeoPa]

Before you continue with this you may want to read (Normalisation and Table structures) and reconsider your whole approach. It is not recommended (for very good reasons) to maintain calculated fields in your tables.
You may also want to consider asking questions of less scope. We try to discourage anyone from asking for their whole project (including design by the looks of it) to be done by our experts.
We're happy for you to come here and ask questions to help you progress and learn, but this is not a programming resource.
Think of it more as a crutch than a motor-vehicle, but a crutch that helps you to learn to walk.
Perhaps you should read the link then reconsider how your database should work. We can help you with that and future questions (of more limited scope) on how to implement various features of it.