running totals in access 97

[ar]

I have two tables. one is stock
and the other one is shipping information

Example Table Stock

PARTNO QTYONHAND
A 50
B 25
C 5
D 10
E 20

EXAMPLE OF TABLE SHIP

CUSTOMER PTNO SHIPQTY DIFFERENCE
A A 45 5
B A 10 -5
C A 50 -55
A B 1 24


I Don't know how to get the difference column

For example for part A there is 50 in stock. If I ship 45 to customer A, The difference is 5 which is the new stock
If I ship 10 to customer B, There is only 5 in stock, so the differnce is -5.

Can anyone give me any suggestions on how do it. I am using access 97.

:(

[ryount]

Something like this?

A B C D
1 PARTNO ONHAND SOLD REMAINING
2 A 50 45 =B2-C2
3 B 25 10 =B3-C3

You only need to type the formula in cell D2, then copy and paste it down for the rest.

I would suggest you check out a tutorial like the one here:
http://www.usd.edu/trio/tut/excel/

Doh! I just realized you were in Access, not Excel. Sorry, never mind. My bad.

[Andilinks]

Do you know how to format a query in design view?

First drag the relevant fields from each table into separate columns in the QBE pane. (QBE = query by example) It is the lower section of the query Design view.

You need to insert an expression to build a calculated field called DIFFERENCE from the QTYONHAND field and the SHIPQTY field. It would be: DIFFERENCE: [QTYONHAND]-[SHIPQTY] Type or paste that expression into the top line of a third column in the QBE pane. Be sure to check "show." It should be checked by default, but if you get no result when running the query that may be the problem.

Let me know how it's going from there, if you still have questions post back here...

Andi

[ar]

Hello Andilink.

I think I will explain this again. For example

STOCK TABLE
PARTNO QTYONHAND
A 55



SHIP TABLE
CUSTOMER PARTNO SHIP DIFFERENCE
ABC A 50 5 *** I have 55-50
CDF A 10 -5 *** 5 on hand - 10 to ship = -5

**** for the same part number the qty on hand changes
****had 55 to begin with but ship 50 to customer ABC qtyonhand is now 5
****have now 5 on hand and want to ship 10 to customer CDF the difference is -5

[Andilinks]

It still sounds to me like what you want to do is run a query like the one I described to find the difference. I must be missing something here. Unless what you want is a macro that performs the calculation each time there is a new entry, which would be much more complicated--at least for me it would...

Andi

[ar]

I query will use the same qty over an over.
I guess you are right I am looking a calculation for each entry.

As each line is shipped the qty will keep decrementing

for example

if I start

pn qty
A 55


SHIP

CUS PN SHIP DIFFERENC
ABC A 50 5
DEF A 5 0
DCD A 10 -10


SEE THE DIFFERNCE COLUMN

[Andilinks]

I have more experience with Word macros than Access. They are similar, both being Visual Basic, but I'm not experienced enough with Access macros to suggest anything specific at this distance. The macros in Access changed in versions later than 97, so you will probably need someone familiar with 97.

The query would have to be run each time you want to see the new Difference field, which would work in a pinch if it is just you needing the calculation. But for a constantly changing Difference where you can browse it in real time, especially if there are many users, I think you need a macro.

If no one here comes up with an easy solution (it's still early), you might try this huge forum that specializes in Access problems:

http://www.access-programmers.co.uk/forums/


Good Luck.

Andi

[ar]

Thanks I will give this a try.