Responding to Negative Values

[sush]

Hi,

I will explain what i want to do.. you may know of an easier way.

I have two spreadsheets (ms excel).

I want to import them into MS Access and compare the stock codes.

A stock take situation.

If they match then subtract the Qty's, if the value of stock is less than the value in the first spreadsheet, put a value in a field saying "OUT" and if the value is greater put "IN".

The Differences in the Qty's needs to shown as a positive figure if it is "OUT".

The Values "IN" & "OUT" will tell the system whether to reduce stock or increase stock.

So,

The system will then import the Qty difference into the system.

Can anybody help??

thanks

 

[Fornatian]

EXCEL ANSWER:
I'd import both spreadsheets into the same workbook. Then use a two VLOOKUP() functions some thing like this:
Assume that A1 has the stock item APPLES and quantity is the second item in each column

=VLOOKUP("A1",1stSheetRange,2,FALSE)-=VLOOKUP("A1",2ndSheetRange,2,FALSE).

This will give you the stocktake differences.
The you can write =IF() formulas to return your "in" and "out" values.

Hope this is what you meant!

 

[sush]

Thanks for the advice,

I have tried this solution, how do i match the stock codes together.. and if no matches are found then the item must to be presented in the other spreadsheet.

Also once i have calculated the difference how do i assign the difference as either in or out.

sorry i may be being a little vauge.


Thanks again