Sum of counted values in a queryHello,
My question,....
I have a query which count the number of records in a table (as a
result of the menuoption view-> totals ->count) as a result of the
group by function for unique records.
The result is:
column1 column2
A 10
B 15
C 8
Now I want to insert a column3 which gives the sum of that counted
values. I'd tried something like: expr1:Sum(Count[column2])
The result should be for each record in column3: 33 (result of the
sum 10+15+8)
It doesn't work.
Somebody knows a solution ?
Thanks
regards,
Johan
You can always writ...
How do I do this UPDATE in a single query?UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [26] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='26')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09]. [50] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='50')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[51] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='51')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[62] = '1'
WHERE ((([HCPC struct 12-17-09].[Mod])='62')) ;
UPDATE [HCPC struct 12-17-09] SET [HCPC struct 12-17-09].[66]...
UPDATE RM20101 on posting SOP invoiceHi,
I am wanting to have the RM20101 Description field be updated with the
SOP10100.refrence field when the SOP invoice\return is posted. Currently the
description field gets populated with the SOPNumber. A combination of batch
and transaction posting is used, and when whatever is done - I only want it
done for the current set of SOP docs that are being posted.
Please advise the best way (and steps or script if possible) this can be
done - without Dexterity:
1) VBA (maybe on the SOP Posting Journal)
2) Trigger on the RM20101
3) SQL Job
4) Anything else that I have not listed
Thank you
...
Update type conversion error with nullsI need to simply divide one currency value by another currency value. Sounds
simple????? Here are two sql statements I've tried. Most records are null,
it is only the ones that I want to update and close that will have a value in
Net_PL.
UPDATE Diagonal SET Diagonal.Final_ROR = IIf([Diagonal]![Net_PL] Is
Null,0,[Diagonal]![Net_PL]/[Diagonal]![Tr_Trade_Risk]);
UPDATE Diagonal SET Diagonal.Final_ROR =
Nz([Diagonal]![Net_PL])/Nz([Diagonal]![Tr_Trade_Risk]);
I get the error message "...didn't update x number of fields due to a type
conversion error......."...
Can't update private meeting on room resourceI have a room resource that everyone has author access to. One of the users
keeps marking the meetings as Private. When he needs to change something on
the appointment, it shows the following messages:
One or more recipients in this item were not usable. You need to recreate
this address. You don't have appropriate permission to perform this
operation.
I have to open the mailbox of the resource and update the meeting. Then the
user has to remove the resource from the update request.
Is this normal? Do I have the permissions set incorrectly?
...
HelpCRM 3.0 refuses to install on my SBS 2003 R2 premium server.
I was encountering the known issues with CRM 3 on SBS R2 using the CDs that
came in the Action Pack subscription so I downloaded the updated CRM CDs from
http://www.microsoft.com/downloads/details.aspx?FamilyID=7d418781-69ad-422d-92fa-87fdb2538e2c&DisplayLang=en
This copy gave me a different set of problems.
The default setup mode pops up a couple errors. The first one is the lack
of full text search so I installed that and ran the setup again. The next
error was:
"The edition 'Workgroup Edition' of the specified SQL...
How to update a link field with preserve format using VBA?HI All,
How to update a link field with preserve format using VBA?
Anthony
Without knowing more about what you are trying to do it is difficult to
advise. You could for example switch to print preview and back or you could
use the example code at http://www.gmayor.com/installing_macro.htm both of
which will update fields in your document.
If, as your e-mail address suggests, this is a link to an Excel range then I
suspect your concern may be more to do with maintaining the formatting than
updating the field. That will depend on the type of link you have inserted.
If you ...
Cannot send updates for existing reoccurring meetingsHello All,
Some users I support are having an issue when attempting to change, then
send out an update on existing reoccurring meetings in their managers
calendar. Currently we are running in a AD domain with Exchange 5.5 and
Outlook 2000 as the client. What's happening is this: When the original
reoccurring meeting was created it was done by a user who rights were not
correct for that calendar. Now that these rights have been modified to be
correct the reoccurring meeting does not allow an update to be sent such as a
date change or time change. When this is attempted the "Cl...
Money not updating onlineIt seems that Money goes to www.spcomstock.com to get
quote updates (look in my information sheet) ... however
that site has change to www.comstock-
interactivedata.com. Now online updating in Money doesn't
work ... at least mine doesn't. Try to go to the
www.spcomstock.com and you'll time out with an error. I
don't see any way to change how money accesses the web
site.
Thoughts .... am I off base. Have read other notes on
the subject and suggestions around firewalls etc. I
haven't changed mine, but just in case turned it off and
still money won't update....
SUM of Numbers with colons (:) such as Hours & MinutesDoes anyone know how to get the SUM of a column with numbers formated as
Time (hh:mm) useing the SUM formula to get the total numbers of hours in
that column. For example:
Day-1 10:45
Day-2 11:05
Day-3 09:10
Total 31:00
Help is very appreciated.
Wally
Hi Wally
for your formula use
=SUM(B1:B3)*24
and format it as a number (format / cells - number)
Cheers
JulieD
"CLARKSON PHARMACY" <CLARKSON.PHARMACY@VERIZON.NET> wrote in message
news:XaCZd.3302$ed6.530@trndny06...
> Does anyone know how to get the SUM of a column with numbers formated as...
Keyboard shortcut for editing links with updating
Can some one post the keyboard shortcut:
When editing a link in a worksheet I don't want it update with new
value when I hit Enter because it takes long to update(going over
anetwork) and have many links to edit.
------------------------------------------------
~~ Message posted from http://www.ExcelTip.com/
~~View and post usenet messages directly from http://www.ExcelForum.com/
Hi there,
There isn't a shortcut that I know of per se - what you can do is go to
Tools-->Options on the Calculation tab change it from Automatic to Manual.
This will apply to all links and calculatio...
How to sum all payments in database and show on formHow to sum all payments in database and show on form?
I posted a question yesterday about my form, and have a new question about
the same form.
I have a form frmVendor with a subform frmPayment. They are linked to
tables tblVendor and tblPayments (which are related by VendorID).
The form frmVendor shows one vendor at a time, with subform showing only
payments made to that vendor.
I want to put a calculated field in the main form (frmVendor) header showing
the total of all payments in the tblPayments (not just the total for the
vendor currently being viewed.)
Reason being, Company ha...
Alert for modification/update of casesI have recently installed CRM 3 professional and i've come across a feature
that our sharepoint server has that CRM doesn't and i would like to know if
it is possible to achieve this.
Anytime anyone updates or changes something on sharepoint list/discussion
board the user can opt to have an email sent to them informing them that
something has changed on that particular board or list.
Can the same thing be done in CRM for cases?
--
Regards,
Matt
Yes, you could do this with a post-callout.
--
Matt Parks
MVP - Microsoft CRM
"Matt" <Matt@discussions.microsoft.com...
Summing with SpacesHi,
If I have a column of numers like this:
3
4
4
3
2
1
3
2
Each group of numbers represents a different day seperated by a space.
In the adjacent column I want to do a running total of each group so I
have a running subtotal for each day, so it would look like this:
3 3
4 7
4 11
3 3
2 5
1 6
3 3
2 5
The formula looks complicated. Thanks for any help.
Make sure the first line is empty
I assume that with "a space" you mean an empty row.
If your data is in Column A (starting in a2), put this formula in B2:
=IF(A2="",0,B1+A2)
And copy down as far as you ne...
Updating treeview control in formI have an Access 2007 form that has a treeview ActiveX control that lists the
dates and names of scheduled events. I have a popup form to edit and or
delete an event. On the popup form there is a command button for saving any
edits and a command button to delete a selected event. In both cases I need
code to refresh the event list displayed in the treeview control of the event
form after closing the popup form. I keep playing with refresh and requery
methods but can't seem to figure it out. Any help will be greatly
appreciated.
Ken
...
non vba way to print non continuous ranges
Hi,
Is there a non vba way to print non continous ranges on the one shee
using excel?
Specifically, lets say I want to print data from columns A, B, C, G an
K down to row 35 in each column. Is there a way I can do that withou
manually making them continuous by cutting and pasting each range ont
a new sheet?
Regards,
David Obei
--
DavidObei
-----------------------------------------------------------------------
DavidObeid's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=223
View this thread: http://www.excelforum.com/showthread.php?threadid=27196
You can h...
Summing certain values only in a reportI have a report where I want to Sum the qty of sales of a certain product but
I also want to display the Sum of total sales (dollar value).
ProductX_Qty Total Sales ($)
120 $55000
In this example the total dollar value of the 120 pcs may not total $55000
due to parts sales that I don't want to count. I have a column in the query
where it is described as either a "Parts" order or a "Unit" order.
--
TIA
Try an expression like this in the Control Source of a text box in the
Report Footer section:
=Sum(IIf([ProductID]="X", [...
How do you sum on top of a group byHow do you do a sum on top of a group by or how do you combine an if
statement with a group by? My code looks like this:
SELECT t_Starts_AppFlow.[Organization Level 1 : R],
t_Starts_AppFlow.[Location Level 3 : R], t_Starts_AppFlow.[EEO/AAP],
Count(t_Starts_AppFlow.[Step Name : CSW]) AS Hire,
Sum(t_Starts_AppFlow!Female) AS [Hire Female], Sum(t_Starts_AppFlow!POC) AS
[Hire POC], Sum(t_Starts_AppFlow!Manager) AS [Hire Manager],
Sum(t_Starts_AppFlow![Non-Manager]) AS [Hire Non-Manager],
Sum(t_Starts_AppFlow!Engineer) AS [Hire Engineer],
Sum(t_Starts_AppFlow![Non-Engineer]) AS ...
Security price updates.I have noticed that my price updates for stocks etc. have been slow and
intermittent over the last week or so. Is this further evidence that
Microsoft has lost interest in supporting Money. I am running the current
version of Money Plus Deluxe.
...
How do I advance the range by one row Excell VBA automatically #2I am using
Sheets(Array("Breakfast", "Lunch", "Supper", "Bedtime")).Select
Range("B94:D98").Select
but when I run this more than once it overwrites what was put in the first
time.
...
Updating mailNickname (ms-Exch-Mail-Nickname) with the contents of samAccountName (SAM-Account-Name)I currently have an issue where a good chuck of my e-mail accounts all
have Alias fields in Exchange that match the AD login account. However
my counterpart administrators in another country have not followed this
procedure and therefore their alias fields are many different things
with no standardization. I've been asked to find a way to get all
exchange accounts alias fields to match their AD login account.
If anyone knows anyway (i.e. - script, tool) way to do this I would
greatly appreciate your help.
Thanks,
Mark
"Stingray" <ks_stingray@hotmail.com> wrote:
>I...
Function to Use when compare sums another columnI have and age Field and a Column that contain counts.
If the aging Column contains a number between >=0 and <=30 I want it to sum
up the counts that correspond in field B2.
I tried the following and keep getting invalid returns.
=SUM(IF(MATCH("<=30",Sheet2!F:F,0),INDEX(Sheet2!B:B,MATCH("<=30",Sheet2!F:F)),0))
One way...(I think)...
=sumif(a:a,">="&0,b:b) - sumif(a:a,">"&30,b:b)
Heather wrote:
>
> I have and age Field and a Column that contain counts.
> If the aging Column contains a number bet...
Summing SubtotalsI have a worksheet with numerous subtotals and a grandtotal. The subtotals
are separated by several individual entries for each subtotal. I have tried
selecting each subtotal and the grand total cell, but when I hit enter it
shows only the last subtotal. What am I doing wrong?
Thanks for your help!
--
jim
Jim,
To sum a column of a sheet with subtotals included, you can use
=SUM(B:B)/2
OR
=SUM(B:B)/3
Since you have the grandtotal showing...
But you really shouldn't need to do that. Describe better what it is that you are looking to do,
and we can come up with a better way - Piv...
Can;t updateI purchased Office 2004 for Teachers, and I want to update from 11.2.5 to
11.3.5. The download starts, but then loses the connection. I posted about
this before and was told to contact my ISP. They say that since everything
else works, the problem has to be the download site. I've tried at least 20
times-no luck. Please help. Thank you very much.
In article <C33BBE9D.551%efbarton@roadrunner.com>,
efb <efbarton@roadrunner.com> wrote:
> I purchased Office 2004 for Teachers, and I want to update from 11.2.5 to
> 11.3.5. The download starts, but then loses the connectio...
Naming ranges?Hi,
I have no problem naming ranges of cells in XL. However, I have the
following scenario that I can't find a solution to:
Imagine you have 5 identical worksheets. You want to highlight a data range
which is also indentical in each worksheet.
Can you create 1 range name that applies to a range of cells on each
worksheet, rather than having to create 5 different named ranges (1 for each
worksheet)?
It is odd because if you have 1 worksheet, name a range, and then make 4
copies, they will all have the same range under the same name - so it must be
possible to do it the other way a...