469,913 Members | 2,443 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,913 developers. It's quick & easy.

Converting positive number to negative number

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

Nov 13 '05 #1
7 5065
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

Nov 13 '05 #2
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.
Nov 13 '05 #3

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

Nov 13 '05 #4

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

Nov 13 '05 #5
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

Nov 13 '05 #6
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?
Nov 13 '05 #7
Thanks for your suggestion osmet, it worked well and got the results as
I wanted

Nov 13 '05 #8

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

10 posts views Thread by JT | last post: by
15 posts views Thread by jaks.maths | last post: by
63 posts views Thread by deepak | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.