count query and negative numbers question

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


0
David
9/11/2007 8:57:44 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
2202 Views

Similar Articles

[PageSpeed] 59

>>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.

-- 
KARL DEWEY
Build a little - Test a little


"David" wrote:

> 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
> 
> 
> 
0
Utf
9/11/2007 10:40:01 PM
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" <david@nospamplease.co.uk> wrote in message 
news:cjDFi.42203$6u5.39886@newsfe1-gui.ntli.net...
> 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
>
> 


0
John
9/12/2007 11:31:07 AM
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





"John Spencer" <spencer@chpdm.edu> wrote in message 
news:O5$yRBT9HHA.3400@TK2MSFTNGP03.phx.gbl...
> 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" <david@nospamplease.co.uk> wrote in message 
> news:cjDFi.42203$6u5.39886@newsfe1-gui.ntli.net...
>> 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
>>
>>
>
> 


0
David
9/12/2007 4:45:54 PM
Reply:

Similar Artilces:

Concatenation question
I am trying to concatenate two fields and then add text in brackets to the end. Here is what I have: StaffFullName = ([Me.LastName] & ", " & [Me.FirstName] & " (Mgr)") I want the end result to be: Smith, Mary (Mgr) How do I achieve this? Thank you, Terri What you've done will work. You can also use the "+" operator if there is a possibility that there will be a missing first name: StaffFullName = ([Me.LastName] & "," & (" "+ [Me.FirstName]) & " (Mgr)") That will avoid an extra spa...

fiscal year setting question
hi. i know that the fiscal year settings get more or less set in stone, so i had a quick question before i set it for my org. in the template field, what is that used for? if i select 'quarterly', does that mean i can only run reports on a quarter basis? what if i wanted to do monthly reports? ultimately, my question is what is the 'template' field used for? thanks! Hi Jeff, Fiscal year setting must be aligned to your company's accountig policy. Fiscal year setting primarily defines sales quotas. When you set quarterly periods, you can set quarterly quotas for mem...

Queries and Charts
Does anyone know why the expressions in queries work fine for reports but not charts? ...

Pass parameter from FORM to QUERY
I have an append query that I trying to call from a cmd btn...but it prompts me for the parameter(ie QuoteID)... How do I tye it into my call: Dim stQueName As String stQueName = "Quote Query" DoCmd.OpenQuery stQueName, acNormal, acEdit On Apr 13, 12:56 pm, jlt...@hotmail.com wrote: > I have an append query that I trying to call from a cmd btn...but it > prompts me for the parameter(ie QuoteID)... > How do I tye it into my call: > > Dim stQueName As String > stQueName = "Quote Query" > DoCmd.OpenQuery stQueName, acNormal, acEd...

Changing query execution sequence
Hi all, I got a spreadsheet which would execute a bunch of queries. It's noted that the queries are executing in the sequence of when it was added to the spreadsheet. Does anyone out there know of a way to switch the order without deleting and recreating them? Thanks! Wing ...

Format for credit card numbers
I've tried a custom format for entering credit card numbers (four groups of four digits, separated by dashes: xxxx-xxxx-xxxx-xxxx. I have tried to use ####-####-####-#### and 0000-0000-0000-0000 but each of these causes the last digit to change to zero. So if I enter 5415779800902512 I get 5415-7798-0090-2510. Anybody already solved this problem? -- Schmacker ------------------------------------------------------------------------ Schmacker's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=28041 View this thread: http://www.excelforum.com/showthread.php?th...

limit the number of outgoing messages
Hi, who can I limit the number of concurrent outgoing messages? for example: if I have a queue of 1000 messages, how can I force exchange to send only 50 messages at a time? ...

a few basic question about resource files
Hi, I have an application that uses resource files to contain the string values for each language. I have created the required resource files in my project, but I don't speak the languages I wish to have resources for. What I would like to do, is somehow allow the end user to edit the resource file themselves so they can set the string values as needed. Is there a way to edit the resource files that ship with my application and get installed ? or Is there a way to have external resource files (not embedded into my application) that can be updated/replaced with o...

Label a chart of counts with other percentage data
Here's the data: Group 2005 2006 2007 LTM Data A 19.4% 22.8% 21.2% 19.9% Profitability A 6 7 7 7 Count B 9.5% 31.6% 30.4% 30.7% Profitability B 2 3 3 3 Count C 22.4% 23.6% 16.6% 17.6% Profitability C 15 16 17 18 Count D 19.2% 20.5% 15.9% 13.7% Profitability D 8 8 9 10 Count I have successfully generated a stacked bar chart that shows the counts per group by year. Now I would like to include a label for each group to show profitability for each group in each year in the 4 stacks. How would I do that? Thanks, --...

Outlook 2003 rules question again
I have a rule so that after the e-mail comes in, it searches for specific words in the e-mail header. I set up this rule to search for sober.worm since I'm receiving a copious amount of spam e-mails which include the sober.worm attachment. I set the rule to delete the e-mails indefinately but it does not delete it. It just sends the e-mails to the junk mail folder. I would like to have it delete the e-mails forever. What can I do to achieve this? -- Nocturnal @ http://www.randomfix.com Also, I did get it to work a few times but the rule stops working. Another thing is if ...

Numbering in Book Files
I am creating a tech manual where each chapter is its own file. I have created an outline numbered list and saved the list in a template that I then use to create the new chapters. However, I am finding that when I go into a new chapter that the outline numbering does not pick up on the chapter (Heading 1) number. For example: Chapter 1: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. But when I start my next chapter, when I update the chapter number to start at 2, I get this: Chapter 2: Heading 1 style 1.1: Heading 2 style 1.1.1: Heading 3 style, etc. ...

Removing text from cells leaving numbers (help with function)
I need a function that will remove all text from a cell and just leav numbers. Formatting cells to number does not work. For example if I have: (Sired] Tennessee 37013 (herein I just want 37013 left. Anybody know a function to resolve this -- Message posted from http://www.ExcelForum.com The following will strip the text from the active cell and place the number in the adjcent cell one column to the left. If there are subsequent numbers in the original string you will get erroneous results. Put the cursor on the cell to be processed and run the macro. ********************************...

multiple iterations of same query
Running Access 2003 on Vista. I have a query that chooses 15 records at random. This works fine. What I want to do is run the same query for approx 110 different users, each with their own randomly selected 15 records. Obviously, I want to avoid manually running the same query that many times. How can I get around this? Post your query SQL. -- Build a little, test a little. "Nathan" wrote: > Running Access 2003 on Vista. > > I have a query that chooses 15 records at random. This works fine. > > What I want to do is run the same query fo...

Formula to count the number of different values in a range
I'm looking for a formula that will give me the number of different values in a range. Example: Column A may have five cells that are "4", five cells that are "7", five cells that are "9". Of the fifteen cells that contain data, there are only 3 different values. I'd like to use a formula that will count the number of different values in column A, in this case the result is "3". Thanks, Paul Try... =SUMPRODUCT((A1:A15<>"")/COUNTIF(A1:A15,A1:A15&"")) OR =SUM(IF(A1:A15<>"",1/COUNTIF(A1:A...

Time Format Question
Hello, I currently have time entered like this: 173517 90207 I need to look like this: 5:35 p.m. 9:02 a.m. I've done a ton of research but nothing has worked so far. Thank you. With your values in column AA, try: =TIME(LEFT(A1,LEN(A1)-4),LEFT(RIGHT(A1,4),2),RIGHT(A1,2)) -- Gary''s Student - gsnu201003 "Toria" wrote: > Hello, > I currently have time entered like this: > 173517 > 90207 > > I need to look like this: > 5:35 p.m. > 9:02 a.m. > > I've done a ton of research but nothing has worked so fa...

Question for Bob Phillips re Splitting Names from Cells
Bob You gave the answers below for splitting names from cells: =LEFT(A1,FIND("^^",SUBSTITUTE(A1," ","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))-1) and =RIGHT(A1,LEN(A1)-FIND("^^",SUBSTITUTE(A1,"","^^",LEN(A1)-LEN(SUBSTITUTE(A1, ",""))))) Using these formulas on this example John A Doe results in John A an Doe, is it possible to split it to show John / A / Doe in 3 separat cells, I know I could use the formulas again on the John A result t split them but I'd like to do it in 1 go If possible could...

Outlook Question
What causes url links not work? I'm experiencing problems with it, and cannot get them to show the URL Links. Clicking the Links doesn't help. ...

How to show query parameters on an Excel page header or worksheet?
Is it possible to display query parameters on a page header or on a worksheet? I have a worksheet that uses a query to retrive data from an ODBC database. The Query prompts for the Start Date and End Date. I would like to be able to print the worksheet and display the Start Date and End Date the user typed in. Thank you for your assistance. ...

Tricky ComboBox / Filter query
Here's one On Sheet1, from A1:A2931 I have dates, every day from 01.01.02 to 31.12.2009 (A1="01/01/2002", A2="02/01/2002", etc). On a Userform I have 2 ComboBoxes: 1 for month, one for year. Is it possible to use these to filter Sheet1 and leave only those dates chosen in the dropdowns (eg, December 2004 only)? TIA Alan ...

Query question 12-11-07
I have a question that I hope you all can help me with. When I run a query, the results come back with multiple lines of data for an order because of multiple critereas in another field. Here is an example of the data returned to my query: Order Status A In Process A Sent B In Process C In Process I would like to see only the data for orders that have not been shipped, and totally exclude data for orders that have been sent. In the example above, I would like my query results to show Orders B and C, but no data for A since it has al...

How can I count unique values in a query in the report footer 12-16-07
I have a report that gives me the count of the status of individuals . This works fine as long is there is only one record in the query (in my query there is one record per month). When I query 12 months (individuals may appear in various months) it counts each record of an individual. For Example in a query considering 12 months for a widow Jones it may count her 12 times and for a survivor named Smith may count her 8 times: Widows 12 Survivors 8 I would like to add a count in the report footer that will tell me how many unique individuals I have in the report (Example widows: Tot...

Locating a number for a MS office
I have lost the boklet that accompanies my CD. However, I am unable to locate a number to contact someone. Any thoughts. I think if you ever learned how to contact them directly, they'd put a hit out on you. (Seriously, visit the MS website and drill down through the tech support area until you find the numbers you need. Good luck.) -- The problem with resting on your laurels is that eventually you are sitting on dead branches. JoAnn "ziggy" <ziggyzaggy@hotmail.com> wrote in message news:0a4c01c36747$0fb00a40$a001280a@phx.gbl... > I have lost the boklet that...

1 QUESTION about OUTLOOOK EXPRESS!
You know the little flags that are on the Wright side off the open INBOX I thinks is a paper clip, a down arrow and then somethi9ng I never saw before today a pair of sunglasses! What the hell! Maybe it was there but today I got a letter with the sunglasses next to it does anyone have a clue what this means I looked all over Microsoft's site all the help files in XP Some one please tell me what this means its bugging the shit out of ME! Thanks, Tom ...

Another question regarding matching names and adding spaces
I have a new question that goes along with this. Is there a way to expand on the cell selection? Besides the names in Column B, I also have data in Columns C, D, E, & F that I need to keep with the names in Column B. Thanks Again! "Xt" wrote: > On Feb 13, 10:28 am, ILunacy <ILun...@discussions.microsoft.com> > wrote: > > I hope someone can help me out with this. Say I have column A filled with > > like 500 names, and column B filled with 300 of the same names. Is there a > > way or a formula to match the names in column B to the corre...

Number colours
Hi, Is it possible to change to colour of a number to indicate whether it was positive or negative? IE - blue for positive numbers and red for negative. Thanks for any help. Matt. Matt Format>Cells>Number>Custom [Blue]#,##0.00;[Red]-#,##0.00 Gord Dibben Excel MVP On Thu, 11 Dec 2003 16:32:12 -0000, "The Stoat" <thefantstoat@lycos.co.uk> wrote: >Hi, > >Is it possible to change to colour of a number to indicate whether it was >positive or negative? IE - blue for positive numbers and red for negative. > >Thanks for any help. > >Matt. &...