count query and negative numbers question

[David]

Hi All,

Pretty new to database queries so apologise if I sound dumb!

I have a database for stock control of energy efficient light bulbs for a
charity that gives the bulbs out free to folks in need.

The database has various fields including type of bulb, location, quantity
and whether the stock has been added to stock or removed.

I want to know how many of the different types of bulbs are in stock at any
time.

I have tried to run a query which makes the 'quantity' amount of bulbs
removed to be negative (puts a '-' in front of number). I then want to run a
second query to count the bulbs so the count subtracts any quantities that
are negative.

Can't get the queries to work and make the quantities negative and the
second query treats negative numbers as positives. What can I be doing
wrong.

Cheers, david

 

[KARL DEWEY]

I have tried to run a query which makes the 'quantity' amount of bulbs
removed to be negative (puts a '-' in front of number).
How are you doing this?

You can make it a negative number by multiplying by -1.

 

[John Spencer]

It would help if you posted the SQL of the query you are using. View: SQL
on the menu, copy and paste to the message.

Generically, the query might look something like the following SQL statement

SELECT BulbType,
Sum(Quantity * (StockAddRemove ="Remove")) - SUM(Quantity * (StockAddRemove
= "Add")) as InStock
FROM YourTable
Group By BulbType

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[David]

Thanks Guys,
multipy by -1 how simple is that, never occurred to me (doh!)

here are my queries

Update to negative works OK now

UPDATE Data SET Data.Units = [Units]*-1
WHERE (((Data.Status)="Out" Or (Data.Status)="Broken") AND
((Left([Units],1))<>"-"));

my count query below also works if I leave out 'data status' (eg broken,
out)
It seems there ae two many catagories of bulbs and users are recording in
the wrong catagories.
I'll get more data and post again

SELECT DISTINCTROW Data.Location, Data.Type, Data.Status, Data.Source,
Data.Product, Sum(Data.Units) AS [Sum Of Units], Count(*) AS [Count Of Data]
FROM Data
GROUP BY Data.Location, Data.Type, Data.Status, Data.Source, Data.Product;

thanks for your help

cheers, david