Performing excell-like function

[blarson0]

Hi! I need to add a sum-collumb to a table. This is something that would be easy in excell, but I don't know about access...

My table looks like this:

Job date Quantity needed quantity on hand quantity availuble
5555 7/15/02 6 30
5555 7/17/02 10 30
5555 7/21/02 15 30
5555 7/25/02 18 30
4321 7/15/02 10 15
4321 7/16/02 7 15
4321 7/19/02 8 15

I need to fill in the quantity availuble. This will be the difference of the quantity currently on hand and the quantity needed, and gets smaller for each job.
So for the example it would look like this:

Job date Quantity needed quantity on hand quantity availuble
5555 7/15/02 6 30 24
5555 7/17/02 10 30 14
5555 7/21/02 15 30 -1
5555 7/25/02 18 30 -19
4321 7/15/02 10 15 5
4321 7/16/02 7 15 -2
4321 7/19/02 8 15 -10


This way we can tell exactly when we will run out of parts, and I can set alerts to tell the workers when to order more.

What is the best way to get that extra collumb in my table?

Thanks!

-Brad

 

[TrojanRabbit]

I did a sum column once, but I couldn't just do it in the table. I actually had to use code to figure it by going through each record and adding to a variable (I was summing the column).

Use a recordset object to traverse the records in order, and use a variable to keep track of what's left from the last record. How you set the original value of the variable you would know.

<variable> = <variable> - rst!<quantity needed>
rst.edit
rst!<quantity available> = <variable>
rst.update

There may be a better solution, but this reflects what I did.

 

[Darrylles]

Hiya,

Either my maths is poor or yours is from the column data that you have supplied (30-10 = 20 not 14 etc).

I'm assuming that you've supplied all relevant fields (even though the data is incorrect).

Create a query, add the table to it, select the above fields into the query grid.

In the end column of the grid enter:

Qty: ([quantity on hand] - [quantity needed])

replace [quantity on hand] and [quantity needed] with the field names if they are different.

Run the query.

Regards,

Darrylle &quot;Never argue with an idiot, he'll bring you down to his level - then beat you with experience.&quot;

 

[blarson0]

No, the data is correct, I just didn't describe it very well. Here is the last 3 collumbs:

Quantity needed quantity on hand quantity availuble
6 30 24
10 30 14
15 30 -1
18 30 -19
10 15 5
7 15 -2
8 15 -10

The first list for the job is quantity on hand - qantity needed (30 - 6 = 24) each job after that is quantity availuble - quantity needed (24 - 10 = 14) it resets when we hit a new job, at 15 - 10 = 5, then 5 - 7 = -2

It probably isn't the best system, but it was the best I can think of. as much as I would like to do this with a query , I don't think it can be done because of the 2 different cases. I haven't worked with record sets any, so I will probably make a form that loops through every record automatically.

If you have any other thoughts, or could give me an example of how to use a record set, that would really help.

Thanks!

-Brad