473,686 Members | 2,090 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 5376
From: "" <os******@eirco m.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
8219
by: pj | last post by:
Why does M$ Query Analyzer display all numbers as positive, no matter whether they are truly positive or negative ? I am having to cast each column to varchar to find out if there are any negative numbers being hidden from me :( I tried checking Tools/Options/Connections/Use Regional Settings both on and off, stopping and restarting M$ Query Analyer in betwixt, but no improvement.
2
1612
by: bafidi | last post by:
i write a number to textbox1.text i want to learn if it is negative or positive
19
2425
by: Harshan | last post by:
The range of signed int is - 2^15 to + (2^15 )-1 (-32768 to 32767) Why the one less at positive Range ? (compared to the negative side..!) I came to know that it uses 2's compliment internally for storing the negative numbers . why it uses 2's compliment but not the ones compliment for storing the negative numbers ?
3
28188
by: Ken Durden | last post by:
Is it possible to force positive values to have the + sign prefixed on them? double f1 = 1024.2; double f2 = -1024.2; string.Format( "{0:F}", f1 ); // +1024.2 string.Format( "{0:F}", f2 ); // -1024.2 I was hoping for something either in the string.Format prefix, or
10
11374
by: JT | last post by:
Need some help in converting a byte to a signed int. This is what I have attempted to do: byte bytes = new byte { 0xFF, 0xFF, 0x9C}; StringBuilder hexString = new StringBuilder(); foreach (byte tmpByte in bytes) hexString.Append(tmpByte.ToString("X2")); int intValue = int.Parse(hexString.ToString(), NumberStyles.AllowHexSpecific);
15
35491
by: jaks.maths | last post by:
How to convert negative integer to hexadecimal or octal number? Ex: -568 What is the equivalent hexadecimal and octal number??
19
11536
by: Johs | last post by:
I need to make some special action if 'a' and 'b' are both positive or both negative. Is there some inbuilt function to check this? Johs
63
5656
by: deepak | last post by:
Hi, Can someone give the standard function which can create positive integer value in C? Thanks, Deepak
0
1369
lee123
by: lee123 | last post by:
i am making a program that is about algebra and i want to know how can i get it to put negative and positive numbers in a text box for example if i had a problem that is: -6(2x + 10) = -48, -12x + 60 = -48 in my project i have 6 textboxes for the numbers and two of them are for "X" if the problem has the "X" after the 10 anyway in this project i show the steps for figuring thes problems out right down to the answer.(with labels...
0
8586
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
9057
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
8772
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
8780
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7603
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6442
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
1
2953
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2208
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1941
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.