Converting positive number to negative number

[hasanainf]

Hi all,

I have two querys

QueryPurchased
ProductID Location TotPcs
Prod1 Loc1 100
Prod2 Loc1 50
Prod3 Loc1 150
Prod3 Loc3 150

QuerySold
ProductID Location TotPcs
Prod1 Loc1 20
Prod2 Loc1 10
Prod3 Loc1 50
Prod3 Loc2 125
I want the result query to show as follows

QueryBalance
ProductID Location TotPcs
Prod1 Loc1 80
Prod2 Loc1 40
Prod3 Loc1 100
Prod3 Loc2 25

I can use a UNION ALL command to bring together
QueryPurchased query and QuerySold query but that will
just add the TotPcs instead of subtracting it.

I have a thought but don't know how to implement it.

If I can convert the TotPcs numbers of QuerySold query
from positive to negative and then do a UNION ALL then
I can achieve what I want to do or so I think.

Any suggestion or alternates will highly be
appreciated.

Thanking you all in advance

[osmethod]

From: "" <os******@eircom.net>
Newsgroups: comp.databases.ms-access
Subject: Re: Converting positive number to negative number
Date: Wed, 14 Sep 2005 07:41:34 -0700
ha*******@yahoo.com wrote:

Hi all,

I have two querys

QueryPurchased
ProductID Location TotPcs
Prod1 Loc1 100
Prod2 Loc1 50
Prod3 Loc1 150
Prod3 Loc3 150

QuerySold
ProductID Location TotPcs
Prod1 Loc1 20
Prod2 Loc1 10
Prod3 Loc1 50
Prod3 Loc2 125
I want the result query to show as follows

QueryBalance
ProductID Location TotPcs
Prod1 Loc1 80
Prod2 Loc1 40
Prod3 Loc1 100
Prod3 Loc2 25

I can use a UNION ALL command to bring together
QueryPurchased query and QuerySold query but that will
just add the TotPcs instead of subtracting it.

I have a thought but don't know how to implement it.

If I can convert the TotPcs numbers of QuerySold query
from positive to negative and then do a UNION ALL then
I can achieve what I want to do or so I think.

Any suggestion or alternates will highly be
appreciated.

Thanking you all in advance

Put a minus in front of the value you want to negate in the Union
Query...

e.g.

SELECT t1.ProductID, t1.Location, t1.TotPCs
FROM t1
UNION ALL SELECT t2.ProductID, t2.Location, -(t2.TotPCs)
FROM t2;
Then create a new select query from this union query. Sum the TotPcs
Field.

osmethod

[Salad]

ha*******@yahoo.com wrote:

Hi all,

I have two querys

QueryPurchased
ProductID Location TotPcs
Prod1 Loc1 100
Prod2 Loc1 50
Prod3 Loc1 150
Prod3 Loc3 150

QuerySold
ProductID Location TotPcs
Prod1 Loc1 20
Prod2 Loc1 10
Prod3 Loc1 50
Prod3 Loc2 125
I want the result query to show as follows

QueryBalance
ProductID Location TotPcs
Prod1 Loc1 80
Prod2 Loc1 40
Prod3 Loc1 100
Prod3 Loc2 25

I can use a UNION ALL command to bring together
QueryPurchased query and QuerySold query but that will
just add the TotPcs instead of subtracting it.

I have a thought but don't know how to implement it.

If I can convert the TotPcs numbers of QuerySold query
from positive to negative and then do a UNION ALL then
I can achieve what I want to do or so I think.

Any suggestion or alternates will highly be
appreciated.

Thanking you all in advance

At first I thought there was a 1 to 1 relationship...for every Purchase
there'd be a sold. Your example is a bit confusing since you have a
Purchase for Loc3 but you subtract it from Loc2 in Sold.

If there is a 1 to 1 purchase to sold, link the product/loc in the query
and you have the results to calc.

If there is all purchase but some missing solds...or visa versa, you can
create the query to select all records in purchase and only those that
match in sole....or all records in sold and only those matching in purchase.

You could create a union query to select all products/locs from
purchase/sold and make each product/loc distinct or else use a totals
query. Then use this query as the main table and add purchases and sold
tables/queries and select all records from the union of product/log and
only those that match for purchases and only those that match from sold.
To do so, double click on the relationship line and set accordingly.

[osmethod]

ha*******@yahoo.com wrote:

Hi all,

I have two querys

QueryPurchased
ProductID Location TotPcs
Prod1 Loc1 100
Prod2 Loc1 50
Prod3 Loc1 150
Prod3 Loc3 150

QuerySold
ProductID Location TotPcs
Prod1 Loc1 20
Prod2 Loc1 10
Prod3 Loc1 50
Prod3 Loc2 125
I want the result query to show as follows

QueryBalance
ProductID Location TotPcs
Prod1 Loc1 80
Prod2 Loc1 40
Prod3 Loc1 100
Prod3 Loc2 25

I can use a UNION ALL command to bring together
QueryPurchased query and QuerySold query but that will
just add the TotPcs instead of subtracting it.

I have a thought but don't know how to implement it.

If I can convert the TotPcs numbers of QuerySold query
from positive to negative and then do a UNION ALL then
I can achieve what I want to do or so I think.

Any suggestion or alternates will highly be
appreciated.

Thanking you all in advance

Put a minus in front of the value you want to negate in the Union
Query...

e.g.

SELECT t1.ProductID, t1.Location, t1.TotPCs
FROM t1
UNION ALL SELECT t2.ProductID, t2.Location, -(t2.TotPCs)
FROM t2;
Then create a new select query from this union query. Sum the TotPcs
Field.

osmethod

[osmethod]

ha*******@yahoo.com wrote:

Hi all,

I have two querys

QueryPurchased
ProductID Location TotPcs
Prod1 Loc1 100
Prod2 Loc1 50
Prod3 Loc1 150
Prod3 Loc3 150

QuerySold
ProductID Location TotPcs
Prod1 Loc1 20
Prod2 Loc1 10
Prod3 Loc1 50
Prod3 Loc2 125
I want the result query to show as follows

QueryBalance
ProductID Location TotPcs
Prod1 Loc1 80
Prod2 Loc1 40
Prod3 Loc1 100
Prod3 Loc2 25

I can use a UNION ALL command to bring together
QueryPurchased query and QuerySold query but that will
just add the TotPcs instead of subtracting it.

I have a thought but don't know how to implement it.

If I can convert the TotPcs numbers of QuerySold query
from positive to negative and then do a UNION ALL then
I can achieve what I want to do or so I think.

Any suggestion or alternates will highly be
appreciated.

Thanking you all in advance

Put a minus in front of the value you want to negate in the Union
Query...

e.g.

SELECT t1.ProductID, t1.Location, t1.TotPCs
FROM t1
UNION ALL SELECT t2.ProductID, t2.Location, -(t2.TotPCs)
FROM t2;
Then create a new select query from this union query. Sum the TotPcs
Field.

osmethod

[hasanainf]

Salad, sorry for confusion typo error on my part. I sincerely
apologise.
The last line on all three queries should read as follows.

ProductID Location
Prod3 Loc2

[Salad]

ha*******@yahoo.com wrote:

Salad, sorry for confusion typo error on my part. I sincerely
apologise.
The last line on all three queries should read as follows.

ProductID Location
Prod3 Loc2

DId you follow what I wrote?

[hasanainf]

Thanks for your suggestion osmet, it worked well and got the results as
I wanted