Calculating Percentage in Query.

I have a query with the fields

Quantity (Count of ID's), Product, company.

My issue is to calculate the percentage for each company  in a query

When I put Quantity/Sum(Quantity)) I am getting subquery warning. How can I 
accomlish this in a query.

That is , I would like to get
Quantity, Percenage: Qty/Sum(Qty), Company.

Any help really appreciated.

Thank you


0
Utf
7/11/2007 7:34:01 PM
access.queries 6343 articles. 1 followers. Follow

3 Replies
9738 Views

Similar Articles

[PageSpeed] 9

Create a totals query to get your SUMs for the equation.
Join the query with your table in that output query to get percent.
-- 
KARL DEWEY
Build a little - Test a little


"Lin" wrote:

> I have a query with the fields
> 
> Quantity (Count of ID's), Product, company.
> 
> My issue is to calculate the percentage for each company  in a query
> 
> When I put Quantity/Sum(Quantity)) I am getting subquery warning. How can I 
> accomlish this in a query.
> 
> That is , I would like to get
> Quantity, Percenage: Qty/Sum(Qty), Company.
> 
> Any help really appreciated.
> 
> Thank you
> 
> 
0
Utf
7/11/2007 8:12:04 PM
May be I am mis understanding  or I do not know.

I guess When I create totals query I will get the totals. However ho do I 
join the two tables?


"KARL DEWEY" wrote:

> Create a totals query to get your SUMs for the equation.
> Join the query with your table in that output query to get percent.
> -- 
> KARL DEWEY
> Build a little - Test a little
> 
> 
> "Lin" wrote:
> 
> > I have a query with the fields
> > 
> > Quantity (Count of ID's), Product, company.
> > 
> > My issue is to calculate the percentage for each company  in a query
> > 
> > When I put Quantity/Sum(Quantity)) I am getting subquery warning. How can I 
> > accomlish this in a query.
> > 
> > That is , I would like to get
> > Quantity, Percenage: Qty/Sum(Qty), Company.
> > 
> > Any help really appreciated.
> > 
> > Thank you
> > 
> > 
0
Utf
7/11/2007 8:22:01 PM
Hello Lin.

"Lin" wrote:
>I have a query with the fields
>
> Quantity (Count of ID's), Product, company.
>
> My issue is to calculate the percentage for each company in a query
>
> When I put Quantity/Sum(Quantity)) I am getting subquery warning.
> How can I accomlish this in a query.
>
> That is , I would like to get
> Quantity, Percenage: Qty/Sum(Qty), Company.
>
> Any help really appreciated.
>
> Thank you

How about this:
Select company, Count(ID) as Quantity,
  Count(ID)/(Select Count(ID) From YouTableName) as Percentage
From YourTableName Group By company

-- 
Regards,
Wolfgang 


0
Wolfgang
7/11/2007 9:34:15 PM
Reply:

Similar Artilces:

"The Starting balance entered ... does not match the starting balance calculated
Searched faqs and recent posting without success. I am trying to balance a new checking account with Microsoft Money 2004, windows XP. My statement balances are identical with that of the corresponding bank. I have a 3 month report. Yet when I try to balance the account, I always get the message: The starting balance entered from hyour statement does not match the starting balance calculated from your Money records". If I choose the option for Microsoft Money to enter a starting balance, all my balances no longer compare with that of the bank. Spend over an hour with support ...

DLookup on a query??
Hello, Just wondering if it's possible to use the DLookup function on a query instead of a table? Alan Don't worry about it. I got it to work. Thanks anyway. Alan "Alan" wrote: > Hello, > > Just wondering if it's possible to use the DLookup function on a query > instead of a table? > > Alan ...

can you not add calculated fields in SmartList?
Hello: Can you not add calculated fields togather in SmartList? childofthe1980s I don't know for sure, but I seem to have a vague recollection of having a problem with that, too. Can you get around it by doing something like so: A = some field * .5 B = another field * 1.5 C = A + B (but this doesn't work) Instead of C being calculated as A + B, could you make C = (some field * .5) + (another field * 1.5)? -- Bud Cool Microsoft Certified Business Management Solutions Specialist, GP 9.0 Financials Currently supporting: GP 9.0, SP2 "childofthe1980s" wrote: > H...

HELP with queries!!!
Hi all, I am a new user to Access and need some help with setting up a query. I am building the framework to an inventory database. Currently I have one table that has the following information: serial # Status (checked in or checked out) equipment type (pc, network printer, standalone printer, scanner, and monitor) EE# (equipment number) model manufacturer work order# department (where the equipment is going) equipment location (the room number) relocation date comments I understand how to run the query, but I am trying to include drop down menu's in the query ins...

Web Query Not Working
Up until yesterday, my web query in my Excel worksheet has been working perfectly. I have been using the saved query named "Microsoft Investor Stock Quotes.iqy". It looks like the server accessed by the URL has been down. When I ping the domain, I get the message that the ping timed out. The link used by the query is http://investor.msn.com/external/excel/quotes.asp?SYMBOL=AA,fdx... Is anyone else experiencing the same problem? Does anyone know how to contact MSN to jog them to get things up and running? Or, have they stopped supporting the query? Gary Mine went d...

Import from web query problem
When importing from a web query the cell value <10E 6/L or <10^ 6/L does not import ie target cell is blank. Same happens whether the middle space is left in or out. Cells such as <100 import OK. Any suggestions as to what is happening here would be appreciated. Thanks. ...

Convert datatype using query
I have create a query which combines two tables; ASSETS and UNITINFO. The UNITINFO table is linked from another database and the ASSETS table has a field that looks up a field called UNIT # from the UNITINFO table. Both fields are called UNIT # but the datatype on the UNITINFO table is set to Text and the UNIT # field on the ASSETS table is set to Number since it is a lookup field. I cannot change the datatype on the UNITINFO table to Number as the field contains both numbers and text. So when I ran my query that I created to pull data from both tables it gave me an error bec...

assigning a unique query for each textbox on a form
I've got a table (tblMain) with the following (relevant) fields: tblMain .FirstName .LastName .Seat .RefNum I've got a form that has a graphical display of the seats to which the people listed in tblMain are assigned. I'd like to put a text box on each seat corresponding to first and last name of the person assigned to that seat (stored in tblMain.Seat). The same database is used for various seatings so I'd like only those associated with the RefNum at hand to be called. I can write a simple enough query which I can call from VBA and which relies on a...

Query Based DL [WILDPACKET]
I want to configure a QBDL so it sends message to all the Active Accounts in our Domain, is this possible? I guess when a message is sent to QBDL it also sends messages to the Disabled Accounts too, right? I aonly want the QBDL to send messages to Enabled Users. Please advise. Thank you On Mon, 5 Dec 2005 08:44:04 -0800, "WILDPACKET" <WILDPACKET@discussions.microsoft.com> wrote: >I want to configure a QBDL so it sends message to all the Active Accounts in >our Domain, is this possible? > >I guess when a message is sent to QBDL it also sends messages to the ...

Queries
I have the following query (for example): countofstates color 3 red 8 blue 7 orange 2 purple 9 yellow I would like to display this table on a report, but when I do it repeats 6 times. How do I only get this information to appear once? Thanks in advance. Try a Totals query... Color States GroupBy Count -- hth Al Campagna . Candia Computer Consulting . Candia, NH USA Microsoft Access MVP http://home.comcast.ne...

calculate percentage of numbers <= zero in a given range
I need to calculate the percentage of numbers, within a range of cells, that are equal to zero or less than zero. Simple example: numbers are 8, -1, 8, 8, 0, 0 The percentage of numbers that are equal to zero or less than zero would be 3/6 or 50% How can I do this in Excel? Try this: =3DCOUNTIF(range,"<=3D0") / COUNT(range) Change range to what you are using. Format the cell as percentage. Hope this helps. Pete On Jul 23, 4:46=A0pm, spowel4 <spow...@gmail.com> wrote: > I need to calculate the percentage of numbers, within a range of > cells, that are eq...

MFC based client server application query?
Hi experts I have developed an client server application using MFC sockets support. The server is a dialog based application & the client is an SDI application. I am having problem with sending data from client to server. After starting the server application, i start the client and try to connect & send data to server using the following code. void CMainFrame::OnConnect() { CSimpleSDIsockclientApp *pApp = (CSimpleSDIsockclientApp*)AfxGetApp(); int res = pApp->ClientSocket.Create(40000); if(res) { MessageBox("Client Socket at port no. 40000 Successf...

Using a cell to determine a value in a database query.
Hi folks, I've run into a problem recently - maybe there's no good answer, then again perhaps you can help me come to one. I have a spreadsheet on which There are three entry fields: start date end date store number I've managed to map start date to [startdate] in a query and end date to [enddate] - that seems to work fine. The problem I'm running into now has to do with the store number field. In the database, store number is a 4 character field, as of now the numbers of the store are like 01,02,03,04..HQ,RO. When I try to map store number to [storenum] in the query, I ...

Where are the Forms and Queries in 2007
In Access 2003 if you wanted to open your existing Forms and Queries you clicked on the Forms button. In Access 2007 this option seems to be missing. My question is how do I access the Forms,Tables and queries. The only forms button I have found creates new forms, I want to access my existing forms. Many thanks for any advice. This is one of those times when a picture paints a thousand words, so here's a picture ... http://brenreyn.brinkster.net/navpane.jpg -- Brendan Reynolds "Ernmander" <Ernmander@discussions.microsoft.com> wrote in message news:4CDA479B-BFA6...

Calculating Averages but excluding zero's
Hi, i want to calculate the average of a column but exclude anyzeros in that column. For example: If column A1 contained 2, 2, 0, 2, 2, 0 then the average would be 2. (8/4) NOT (8/6) Thanks. -- fodman ------------------------------------------------------------------------ fodman's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=31941 View this thread: http://www.excelforum.com/showthread.php?threadid=555103 Try this: =AVERAGE(IF(A1:A6>0,A1:A6)) Enter this as an array formula. Use CTRL-SHIFT-ENTER instead of just Enter. HTH, Elkar "fodman"...

Query Builder (SQL View)
I am trying to join 2 tables. I want to join if 'Field B' is in 'Field A'. ie. Field A = 'ABCDE' Field B= 'BC' ....the join would be successful cause 'BC' is in 'ABCDE'. Is this possible? I tried... SELECT FROM table1 INNER JOIN table2 ON table1.name like (% table2. partial_name %); Keep getting errors. Any help greatly appreciated! Warren Depending on the "flavor" of SQL you are using SELECT * FROM table1 INNER JOIN table2 ON table1.name like '%' & table2.partial_name & '%' Or SELECT * FROM table1 IN...

Access Deleting a Query SQL
Hi all, I am working with a multiple parameter search query. The form I am using to conduct the search displays the results in a subform. I have gone through and really combed through the SQL to make sure there are no blatant spelling errors or incorrect references within the code. Anyways when I open the form that it is attached to, I keep getting the boxes described in this website http://allenbrowne.com/bug-13.html .. The form is open, and I even changed names of fields in the table in case SQL was choosing to be picky about what I named my fields. Is there anything else that would cau...

Calculate Daily Interest, Paid out Weekly
Not sure if the subject makes sense I have 2 dates Rate = 8% Start = 12/28/2009 End = 2/18/2010 This is 7 weeks 3 days Daily interest = .08/365 = 0.02192% How do I find what the total for say $35.95 and the end of the period 02/18/2010 - If daily interest is applied at the end of every week. 1. Do I add the 3 days to 35.95 before calculating week1? 2. Is there a built in function that does this I'm thinking the total should be around 253.20 + the 3 days Thanks Steve What is the principal and when is it paid. Are you talking about one investment of $35.95...

Help required in Update query
Hi, pls help me here table t1 has 3 fields, and data is as below name , grade , marks n1 a 10 n2 b 20 n3 c 30 n4 a 30 n5 c 10 n6 n7 40 now i want an update query where 2 names should be swapped. like n1 is updated as n2, and n2 updated as n1. i tried to put update query, but problem is if n1= n2 then we have 2 n2's in the table then again n2=n1 tried then we have 2 n1's , i want both names should be interchanged, any help STEP 1: BACKUP you...

If statement to export query if record count is not null
Greetings, thank you very much to all who are reading this. Basically I have an automated email that goes out every Monday. Very simply it uses sendobject to email a query. I would like to add some logic that basically looks at the record count and if it isn't null...sends teh email as is...if the record count is empty I would like to have it send an email that basically says "The record set this week is empty." Below is the module converted from the original macro. I was having trouble modifying the module so any help would be greatly appreciated. Thanks in ...

Ten Key Calculator cell format
Is there a way to format a cell to move the decimal back two spaces similar to a ten key adding machine. In example, if I key 1025 in a cell can a custom format make that value 10.25. Other examples: Enter Format 10 .10 456 4.56 2000 20.00 Thanks in advance for anyone who could help me with this. Manny ...

Ms
I've been struggling with this for some time now and hope that I'm missing something simple. Is it possible to switch the source of a table in MS-Query. The situation. I've created large excel data tapes (150+ fields) which are populated from a SQL Server view via MS-Query. My company has decided to move these views onto a different server. The view I will be using will be the exact same only coming from a different location. Is there an easy way to modify the source of data in MS-Query without rebuilding the entire thing? Please tell me yes. ;) Any help will greatly be ...

select query 12-17-07
Hi All, I have following 2 tables 1. Employee table with Emp_ID and Emp_Name 2. Vehicle table with Veh_ID, Emp_ID, Vehicle And both are related with one to many relationship. In these case, Can can I know the list of employees having Car and Bike. The list should not include if employee is having only Car or only Bike or (Car and Van) Or (Bike and Van) Regards Venkatesh Simplest method is probably to use two sub-queries in the WHERE clause. SELECT E.Emp_ID, E.Emp_Name FROM Employee as E WHERE E.Emp_ID IN (SELECT V.Emp_ID FROM Vehicle as V WHERE V.Vehicle= 'Car') AN...

Quicker Calculation Time
I have a sheet thath as multiple LOOKUP functions being used and couple custom functions being used. The calculations are extremely slow...is there a way to speed them up -- jeffcravene ----------------------------------------------------------------------- jeffcravener's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=9 View this thread: http://www.excelforum.com/showthread.php?threadid=31977 Since you give no details, it's impossible to give you any specific advice. Check out: http://www.decisionmodels.com/optspeed.htm In article <jeffcravener....

SQL query from numeric to dollars
Hi Gang - Part of my sql query: Convert(numeric10,2) RM20101.SLSAMNT)AS SLSAMNT and it returns with a format like this 1000.00 and this is ok, but I am being asked to show it as $1,000.00 [how do I put in the dollar sign and the the comma]!! select convert(varchar, convert(money, slsamnt), 1) from RM20101 Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com The Dynamics GP Blogster at http://dynamicsgpblogster.blogspot.com "Michael@nyresume.com" wrote: > Hi Gang - > > Part of my sql query: > &g...