Total Within A Query

Hi,

I have the following query the gives me the total Balance by pool. I'm 
Trying divide the Sum of each pool to the over all balance within a query.

SELECT POOL, Sum(Portfolio.Balance) AS SumOfBalance
FROM Portfolio
GROUP BY Portfolio.POOL

Example:

Pool	Balance	%
2006-01	10	67%
2006-02	5	33%

Please advice

Thanks


0
Utf
12/11/2007 7:55:02 PM
access 16762 articles. 3 followers. Follow

2 Replies
637 Views

Similar Articles

[PageSpeed] 6

One way to approach this:

SELECT Pool, Sum(Balance) as SumOfBalance,
            SumOfBalance/DSUM("Balance", "Portfolio") as Pct
FROM Portfolio
GROUP BY PortFolio

Another that would work is the following.  I believe this would be faster 
with a large dataset, but would have to test it out to see.

SELECT Pool, 
            Sum(Balance) as SumOfBalance,
            Sum(Balance)/SumOfPortfolio as Pct
FROM Portfolio,
         (SELECT SUM(Balance) as SumOfPortfolio
          FROM tblPortfolio) as PortSum
GROUP BY PortFolio


HTH
Dale

-- 
Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"The Report Guy" wrote:

> Hi,
> 
> I have the following query the gives me the total Balance by pool. I'm 
> Trying divide the Sum of each pool to the over all balance within a query.
> 
> SELECT POOL, Sum(Portfolio.Balance) AS SumOfBalance
> FROM Portfolio
> GROUP BY Portfolio.POOL
> 
> Example:
> 
> Pool	Balance	%
> 2006-01	10	67%
> 2006-02	5	33%
> 
> Please advice
> 
> Thanks
> 
> 
0
Utf
12/11/2007 8:10:00 PM
Thanks Dale.

It work with DSUM function.  I received the error message with the other 
statements.

"Dale Fye" wrote:

> One way to approach this:
> 
> SELECT Pool, Sum(Balance) as SumOfBalance,
>             SumOfBalance/DSUM("Balance", "Portfolio") as Pct
> FROM Portfolio
> GROUP BY PortFolio
> 
> Another that would work is the following.  I believe this would be faster 
> with a large dataset, but would have to test it out to see.
> 
> SELECT Pool, 
>             Sum(Balance) as SumOfBalance,
>             Sum(Balance)/SumOfPortfolio as Pct
> FROM Portfolio,
>          (SELECT SUM(Balance) as SumOfPortfolio
>           FROM tblPortfolio) as PortSum
> GROUP BY PortFolio
> 
> 
> HTH
> Dale
> 
> -- 
> Don''t forget to rate the post if it was helpful!
> 
> email address is invalid
> Please reply to newsgroup only.
> 
> 
> 
> "The Report Guy" wrote:
> 
> > Hi,
> > 
> > I have the following query the gives me the total Balance by pool. I'm 
> > Trying divide the Sum of each pool to the over all balance within a query.
> > 
> > SELECT POOL, Sum(Portfolio.Balance) AS SumOfBalance
> > FROM Portfolio
> > GROUP BY Portfolio.POOL
> > 
> > Example:
> > 
> > Pool	Balance	%
> > 2006-01	10	67%
> > 2006-02	5	33%
> > 
> > Please advice
> > 
> > Thanks
> > 
> > 
0
Utf
12/11/2007 8:42:02 PM
Reply:

Similar Artilces:

An XML question
I would like to produce the following output based on my XML file: My Album (2005) Elapsed Time (hh:mm:ss): 00:07:00 Song 1: title1 Length (hh:mm:ss): 00:02:30 Song 2: title2 Length (hh:mm:ss): 00:02:15 Song 3: title3 Length (hh:mm:ss): 00:02:15 ===== <album> <general> <title>My Album</title> <year>2005</year> </general> <content> <song> <songTitle>title1</songTitle> <songLengthInSeconds>150</songLengthInSeconds> </song> <song> <...

Is it possible to add a Search Box within a spreadsheet?
Is it possible to add a search box within a spreadsheet? For example if you go the Menu you can click on the Find button and then enter a name; the function then searches the database and finds matching entries. So I'm wondering if you can add a similar "Search Box" within the spreadsheet which will allow the user to simply type in an entry and have the macro find the matching entries. Hope this is clear...Thanks Try pressing F5. OR Ctrl + F, Or are you looking for a strictly programmatic approch? In which case use the help file in vbe and search .Find Method. ...

Totals on Sales Inquiry native windows
We would like to have the totals on the Sales Inquiries: Transaction by Customer Transaction by Document Sales Documents Sales Items Today we can retrieve this information in separated inquiries and reports, be we would like to heve a easier way to see this totals... The same suggest can be applied to Smartlist. If possible, you can create a tool to sum a field after you make a filter I created a screen where you can see all sales, separated by day of week, and week of year, and totals YTD,MTD and WTD, but I am not able to have the same information grouped by item number, for example.....

Excel spreadsheet in Reports only works within network
I have a dynamic Excel spreadsheet that dynamically updates the data when the spreadsheet is opened. However, it only works inside the network because of the SQL connection string. Is there a way to make this work when a user runs the spreadsheet from outside the network? The CRM dynamic Excel fetches the data from the CRM Database so needs to have connection to it. If someone from the outside of the network wants to be able to run this report, they need to have access to your CRM from their network, i.e. VPN Check with your IT, they maybe other network options to securely connect...

Find within a list that is not unique
I'm trying to figure out the quickest way to search a column for a lis of values and the answer may not always be exact. For example, within a column, I want to find if there are any of a lis companies (ABC, IBM, Sears, XYZ, etc.). Those companies may be liste there, but not exact. For example, there may be ABC Inc. or AB Corporation. This is a routine search. When I use vlookup with a named range, I can only do exact matches. Fo some reason, anyway that I try to use * does not find matches. I�v tried the following: =VLOOKUP(B2,Looklist,2,0) where �Looklist� refers to a named range an ...

total cost on report
I have areport with a cost field that I would like to total that collum at the end of the report. How do I do this ? do I need to create another field (total) please help thanks, jettro Jettron wrote: >I have areport with a cost field that I would like to total that collum at >the end of the report. How do I do this ? do I need to create another field >(total) The usual way is to use a text box with an expression like: =Sum([cost field]) -- Marsh MVP [MS Access] I'm in design view of the report, where do I insert this expression? I clicked on page footer to insert thi...

Unmatched query wizard??
I have succesfully made a Unmatched query using the wiz. I changed the query to a delete query, but when I run it, it asks me which table do i want to delete the records from. I keep screaming at it 'what do you mean which table isn't it obvious', but that doesn't work. Can anyone tell me how to overcome this please. Regards Geeves1293 Use a subquery to select the unmatched records This example deletes records in tblInvoice that have no matching records in tblInvoiceDetail: DELETE FROM tblInvoice WHERE NOT EXISTS (SELECT InvoiceID FROM tblInvoiceDetail ...

Setting ID# to 1 & Total on bottom of query result
Hi, I have 2 separate questions. First how do I set the ID# to 1 for the first record. I copied my whole database including queries and forms which were 2009 records and changed the titles to 2010. I did a "compact and repair" to the 2010 database. But when I start to enter records for 2010 the ID# is 2550. How can I reset the ID# back to start at 1? On many of my queries I have the results be for example 28 Caucation, 43 African American, 65 Hispanic, 4 Asian, 2 Native American. I need these numbers separate like they are but I would like a total line at the bottom...

query issues
I am having trouble figuring something out and hoping someone out there might be able to help. I have built an Access database for a hospital education department to track education activities that employees have taken and/or need to take. I have built into the structure a means of requiring a specific job code to take a specific activity, or employees from a specific department to take an activity. I have tables set up - Employee, JobCodes, Depts, JC_Requirements, and Dept_Requirements. Each employee has a Dept_Num and a JC_Num. These fields are linked to the Dept and JC tables. Each ta...

Finding Cells that Total a Value
Hello Friends, I need some assistance in solving a problem. I have a spreadsheet with over five hundred lines of transactions. The sum of these transactions are creating a balance on the account. Is there any formula/macro that will help me find the transactions creating the balance? The sum of the account should be zero. To clarify, if we owe client money, there would be a transaction setting up that postive balance then a payment on the account taking it back to zero. There could be multiple transactions and then one net payment. Or we could be due to receive. So at the end of the day, th...

Delete duplicates QUERY
I'm having two tables, each with one field which contains some data: ___________ Table1 Field1 Per Pert Perte Perter ___________ Table2 Field2 Pert ___________ Now, I want to make a DELETE Query in MS ACCESS 2003. I go by the "Find Unmatched Query Wizard" (1st point on Table2, 2nd point on Table1, 3rd click on Field2<=>Field1, 4rd point on Field2 to see. Then I have "Table2 Without Matching Table1" (I then change Is Null to <>"False" and show Table1.Field1 in stead of Table2.Field2)) I'll then end up with this SQL...

Query AD
Hy, there is some tools that i can use to query the active directory? I need to know if users are members of some groups or not. Thanks Kentucky On 21 Nov 2006 05:56:58 -0800, "Kentucky" <aforino@libero.it> wrote: >Hy, there is some tools that i can use to query the active directory? >I need to know if users are members of some groups or not. >Thanks >Kentucky ldp.exe (Windows Support Tools Download) adfind (you can google that) are two popular tools On 21 Nov 2006 05:56:58 -0800, "Kentucky" <aforino@libero.it> wrote: >Hy, there is some to...

Total Items
Hi - Is there a way to get a total number of items that are about to be tendered, on the POS screen ? Thanks. You can customize the status.htm file. Not sure exactly what the variable is that you want, but I know it cn be done. "NJS" <NJS@discussions.microsoft.com> wrote in message news:538F3CC7-F23D-43C7-8A5A-4319B8688E5B@microsoft.com... > Hi - > Is there a way to get a total number of items that are about to be > tendered, > on the POS screen ? > Thanks. Jason, Send me your email I have already customized this and will forward you. "Jason&qu...

Open Outlook 2003 from within Browser
Hi, I want to launch Outlook 2003 from within Opera or Firefox when I click on an email link from within a web site. How can I set this up so that when I click on a link, Outlook 2003 opens up as a blank new email message with the email address of the email link automatically placed in the to field? Thanks for any help. -- BadHead :) --- OS: Windows XP Professional SP2 (32-bit) PSU: Tagan 2 Force TG530-U22 530W MoBo: Abit AN8 Fatal1ty CPU: AMD64 3200 (Venice Core) RAM: 2048Mb 400Ghz Patriot XBLK (4X512MB) GPU: NVIDIA 6800 (Driver Version 78.01) Sound: Creative Audigy X-Fi Fatal1ty FPS Di...

Subform within a subform
I have a subform (Person) which is nested within another subform (PersonalAssistant). Everything works fine except the foreign key in the PersonalAssistant subform. I have used the Link Child and Master Fields property to establish this link. In this case the primary key for the Person table is PersonID. Its an autonumber field which begins from 4000. When the foreign key is added by access, it takes the last digit or last two digits. So say the PersonID is 4001. What I see in the PersonalAssistant subform and table on the PersonID foreign key field is 1. On Mon, 11 Feb 2008 17:39:00 ...

Total of Totals
Hey there, I've set up a database consisting of four tables. I run a query which calculates a total for the item (unit price * quantity). Is it possible to calculate the final total of all this for a report? If that's possible, how can I get it to be at the bottom of all the records but only on the last page? Thanks Mathew On Aug 1, 12:48 am, KARL DEWEY <KARLDE...@discussions.microsoft.com> wrote: > Yes. > Open the form in design view, click on menu VIEW - Report Header/Footer a= nd > add a text box in the footer with Control Source =3D[unit price] * > [quantit...

Manipulating totals and columns in Pivot Tables
Hi, I'm constructing a pivot table that has investors as columns, investments as rows, and the general data is values. Trouble is there is a column which I want to only display half of each value. I know that I can make a formula that divides a column "column A/2", and then hide the unwanted columns. But I would also like the hidden columns to not be included in the grand totals. How do I make this happen? Thanks! Hi, So let me understand what you want, something like this in the Pivot Table: A B C Name Jp ...

date diff expression in query
I am trying to calculate the difference between two dates 'End Date' and 'Date of Visit' using a query. This is the expression I used: TimeFrame:[Date of Visit] - [End Date]. However, 'End Date' is present in both tables I have used to create this query 'tbl_NewInfo' and 'tbl_Foloowup'. I want the 'End Date' from 'tbl_NewInfo'. How do i specify this in the expression and thereby prevent the error msg I am getting? Thankyou You must fully qualify the field name like: [Table Name].[Field Name] I use a naming convention that resu...

Pole display Sub Total
Hi All, When a client of mine scans items the amount displaying is the sub total. Is there any way to display the total (ie including tax amount) Regards Amanda ...

How to change query empty field to "0"
Hello, At the beginning forgive me at the bad English. I am a beginner in Access. I'm working on a database that deals with records warehouse. To simplify the. I have one query, for example query A, a query where the calculation of 50 items. This query A has sum by the code of materials. The second query is composed of one few table and query A. But in this table is 60 items with the calculations of other tables. The column, which is associated with a query (sum: SumOfqueryA) has a problem. 50 fields have result, and 10 field is blank because I have 50 results in QueryA. How...

how do I get a total for all worksheets in my workbook?
I have ten worksheets, each with a total. How do I get a total of all totals? Considering you have 3 sheets, and the totals are in cell A1, then use: =SUM(Sheet1:Sheet3!A1) Mangesh "Aileen Hewat" <AileenHewat@discussions.microsoft.com> wrote in message news:18C80118-642F-4690-8B5E-7D68B63752E3@microsoft.com... > I have ten worksheets, each with a total. How do I get a total of all totals? This file might be a help: http://www.bygsoftware.com/examples/zipfiles/consol.zip It's in the "Accountants" section on page: http://www.bygsoftware.com/examples/exa...

Calculate Total Books
I am preparing packing slips for boxes to be sent out. There are different book orders going into the same box. Each book in the order will have its own set of id numbers that are usually, but not always, in a string(101-121 etc). I use a calculated field on the form to display the number of books in the string as I store the start book number and subract it from the end book number and add 1 to get the actual number of books in the string. I use the same calculated field on the report to show the number of books in each detial, but I have multiple strings in 1 box. I need to show a...

multiple mailboxes within an EDB file with the same name
Hoping to get some help with an issue related to exchange mailboxes. Without getting into the details of incidents that led to this situation, let me just describe the situation first - I am running E2K3 (as part of standard install of Small Biz Server 2003). I am trying to consolidate 2 mailboxes into 1. They are both the same name but one of them used to be an account in another domain. (a different security principal). When I browse Mailboxes via Exchange System Manager, I see both the mailboxes. Using the free version of Ontrack PowerControls, I can see the 2 mailboxes and their contents...

Address List Query to Multiple Mailstores
I am trying to populate an Address List so that it looks at more than one mailbox store, but NOT the entire server. Example Storage Group 1 Faculty 1 Faculty 2 Storage Group 1 Staff 1 Staff 2 Adress List All Faculty All Staff When creating the query, I only see a way to add one mailbox store. Any ideas? Use Custom Search from the Advanced tab if you're comfortable with ldap filters - you will be able to use multiple mailbox stores. Query will look something like this: (objectCategory=person)(objectClass=user)(|(homeMDB=blah)(homeMDB=blah2)) Replace blah with the distinguishedNam...

having a link within a link
Hi all- I'm new here and hope that someone will be able to help me with this. I am having an excel link problem. Here is the scenario in basic terms. I have one workbook, in the workbook i have 4 tabs labeled MASTER, SCENARIO 1, SCENARIO 2 and SCENARIO 3 Scenario's all have the same format but have many different numbers in them. The Master sheet has cells linked to scenario 1. A simple link on the Master sheet would show up as follows: ='scenario 1'!A1 which basically means that it is pulling from cell A1 on the scenario tab. Is it possible to have the "1&qu...