Crosstab Query results/ Sum

I created a crosstab query that outputs sales by each month (month as a 
column header).  I am creating a second query to retrieve sums of the months 
I am interested. 
Let's say the sales is as follows

Month1  Month2    Month3
$200      $100        $300

Here is the formula I am using:

Last3Months:  Month1 + Month2 + Month3

Instead of getting the sum which shoudl be $600, 
it looks like the formula is concatenating the values resuting in 200100300

i guess i can just sum it up in Excel but wanted to see if i can do it in 
Access.

0
Utf
5/20/2010 5:24:01 PM
access 16762 articles. 2 followers. Follow

8 Replies
859 Views

Similar Articles

[PageSpeed] 5

Based on what you posted, it looks like the query thinks the "values" are 
"text".

Consider coercing the values into "currency" using CCurr().  That way, the 
"+" should add the values.

By the way, what happens if you have a null?!  Check the Nz() function...

Regards

Jeff Boyce
Microsoft Access MVP

-- 
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

"KrispyData" <KrispyData@discussions.microsoft.com> wrote in message 
news:65F38074-B24E-4149-9398-DAC63D4FF99E@microsoft.com...
>I created a crosstab query that outputs sales by each month (month as a
> column header).  I am creating a second query to retrieve sums of the 
> months
> I am interested.
> Let's say the sales is as follows
>
> Month1  Month2    Month3
> $200      $100        $300
>
> Here is the formula I am using:
>
> Last3Months:  Month1 + Month2 + Month3
>
> Instead of getting the sum which shoudl be $600,
> it looks like the formula is concatenating the values resuting in 
> 200100300
>
> i guess i can just sum it up in Excel but wanted to see if i can do it in
> Access.
> 


0
Jeff
5/20/2010 5:52:59 PM
You can use the sum feature in a crosstab query.  And if memory serves, in 
order to concantenate in Access, the expressions have quotation marks around 
them.  Like "firstname" & " " & "lastname".  If you show the code you're 
using we can correct it.

"KrispyData" wrote:

> I created a crosstab query that outputs sales by each month (month as a 
> column header).  I am creating a second query to retrieve sums of the months 
> I am interested. 
> Let's say the sales is as follows
> 
> Month1  Month2    Month3
> $200      $100        $300
> 
> Here is the formula I am using:
> 
> Last3Months:  Month1 + Month2 + Month3
> 
> Instead of getting the sum which shoudl be $600, 
> it looks like the formula is concatenating the values resuting in 200100300
> 
> i guess i can just sum it up in Excel but wanted to see if i can do it in 
> Access.
> 
0
Utf
5/20/2010 5:58:01 PM
Try forcing the values to numbers with:

Last3Months:  Format(Val(Month1) + Val(Month2) + Val(Month3),"Currency")

or if the dollar sign is being returned as part of the string:

Last3Months:  Format(Val(Mid(Month1,2)) + Val(Mid(Month2,2))+ Val(Mid(Month3,
2)),"Currency")

Or you could probably cut out the middleman by basing a new query on the
original table(s), grouping it by whatever the row headers in the crosstab
are, restricting it to the last three months data and summing the sales
amounts.

Ken Sheridan
Stafford, England

KrispyData wrote:
>I created a crosstab query that outputs sales by each month (month as a 
>column header).  I am creating a second query to retrieve sums of the months 
>I am interested. 
>Let's say the sales is as follows
>
>Month1  Month2    Month3
>$200      $100        $300
>
>Here is the formula I am using:
>
>Last3Months:  Month1 + Month2 + Month3
>
>Instead of getting the sum which shoudl be $600, 
>it looks like the formula is concatenating the values resuting in 200100300
>
>i guess i can just sum it up in Excel but wanted to see if i can do it in 
>Access.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access/201005/1

0
KenSheridan
5/20/2010 6:07:17 PM
Thanks rebeccax.  However, the SUM feature in the crosstab query will not 
work because it sums all the sales.  
for some reports, i may want only the 3 months of sales, or even a random 
set of months.   i want to be able to add those months sales together in 
access and save my self the step of doing it in Excel.  

"rebeccax" wrote:

> You can use the sum feature in a crosstab query.  And if memory serves, in 
> order to concantenate in Access, the expressions have quotation marks around 
> them.  Like "firstname" & " " & "lastname".  If you show the code you're 
> using we can correct it.
> 
> "KrispyData" wrote:
> 
> > I created a crosstab query that outputs sales by each month (month as a 
> > column header).  I am creating a second query to retrieve sums of the months 
> > I am interested. 
> > Let's say the sales is as follows
> > 
> > Month1  Month2    Month3
> > $200      $100        $300
> > 
> > Here is the formula I am using:
> > 
> > Last3Months:  Month1 + Month2 + Month3
> > 
> > Instead of getting the sum which shoudl be $600, 
> > it looks like the formula is concatenating the values resuting in 200100300
> > 
> > i guess i can just sum it up in Excel but wanted to see if i can do it in 
> > Access.
> > 
0
Utf
5/20/2010 6:15:01 PM
Do you want your sums in the query or the report?

"KrispyData" wrote:

> Thanks rebeccax.  However, the SUM feature in the crosstab query will not 
> work because it sums all the sales.  
> for some reports, i may want only the 3 months of sales, or even a random 
> set of months.   i want to be able to add those months sales together in 
> access and save my self the step of doing it in Excel.  
> 
> "rebeccax" wrote:
> 
> > You can use the sum feature in a crosstab query.  And if memory serves, in 
> > order to concantenate in Access, the expressions have quotation marks around 
> > them.  Like "firstname" & " " & "lastname".  If you show the code you're 
> > using we can correct it.
> > 
> > "KrispyData" wrote:
> > 
> > > I created a crosstab query that outputs sales by each month (month as a 
> > > column header).  I am creating a second query to retrieve sums of the months 
> > > I am interested. 
> > > Let's say the sales is as follows
> > > 
> > > Month1  Month2    Month3
> > > $200      $100        $300
> > > 
> > > Here is the formula I am using:
> > > 
> > > Last3Months:  Month1 + Month2 + Month3
> > > 
> > > Instead of getting the sum which shoudl be $600, 
> > > it looks like the formula is concatenating the values resuting in 200100300
> > > 
> > > i guess i can just sum it up in Excel but wanted to see if i can do it in 
> > > Access.
> > > 
0
Utf
5/20/2010 6:19:01 PM
In the query if possible.

"rebeccax" wrote:

> Do you want your sums in the query or the report?
> 
> "KrispyData" wrote:
> 
> > Thanks rebeccax.  However, the SUM feature in the crosstab query will not 
> > work because it sums all the sales.  
> > for some reports, i may want only the 3 months of sales, or even a random 
> > set of months.   i want to be able to add those months sales together in 
> > access and save my self the step of doing it in Excel.  
> > 
> > "rebeccax" wrote:
> > 
> > > You can use the sum feature in a crosstab query.  And if memory serves, in 
> > > order to concantenate in Access, the expressions have quotation marks around 
> > > them.  Like "firstname" & " " & "lastname".  If you show the code you're 
> > > using we can correct it.
> > > 
> > > "KrispyData" wrote:
> > > 
> > > > I created a crosstab query that outputs sales by each month (month as a 
> > > > column header).  I am creating a second query to retrieve sums of the months 
> > > > I am interested. 
> > > > Let's say the sales is as follows
> > > > 
> > > > Month1  Month2    Month3
> > > > $200      $100        $300
> > > > 
> > > > Here is the formula I am using:
> > > > 
> > > > Last3Months:  Month1 + Month2 + Month3
> > > > 
> > > > Instead of getting the sum which shoudl be $600, 
> > > > it looks like the formula is concatenating the values resuting in 200100300
> > > > 
> > > > i guess i can just sum it up in Excel but wanted to see if i can do it in 
> > > > Access.
> > > > 
0
Utf
5/20/2010 6:31:01 PM
Sorry, the syntax for concantenation is [LAST] & ", " & [FIRST].

You didn't mention if your second query is meant to be a crosstab as well.   
I would assume not.  If that's the case, add a column and create an 
expression summing the appropriate fields and give it an alias like "total".  
If you want sums at the report level, create an unbound text box and write an 
expression to sum the fields you want based on their names in the report.  
And if the months included in the report are controlled by the query feeding 
it, the sum option should appear if you walk through the wizard to create the 
report.

"KrispyData" wrote:

> I created a crosstab query that outputs sales by each month (month as a 
> column header).  I am creating a second query to retrieve sums of the months 
> I am interested. 
> Let's say the sales is as follows
> 
> Month1  Month2    Month3
> $200      $100        $300
> 
> Here is the formula I am using:
> 
> Last3Months:  Month1 + Month2 + Month3
> 
> Instead of getting the sum which shoudl be $600, 
> it looks like the formula is concatenating the values resuting in 200100300
> 
> i guess i can just sum it up in Excel but wanted to see if i can do it in 
> Access.
> 
0
Utf
5/20/2010 6:31:01 PM
Yep!  I use the Nz function to sum fields in my reports.

"Jeff Boyce" wrote:

> Based on what you posted, it looks like the query thinks the "values" are 
> "text".
> 
> Consider coercing the values into "currency" using CCurr().  That way, the 
> "+" should add the values.
> 
> By the way, what happens if you have a null?!  Check the Nz() function...
> 
> Regards
> 
> Jeff Boyce
> Microsoft Access MVP
> 
> -- 
> Disclaimer: This author may have received products and services mentioned
> in this post. Mention and/or description of a product or service herein
> does not constitute endorsement thereof.
> 
> Any code or pseudocode included in this post is offered "as is", with no
> guarantee as to suitability.
> 
> You can thank the FTC of the USA for making this disclaimer
> possible/necessary.
> 
> "KrispyData" <KrispyData@discussions.microsoft.com> wrote in message 
> news:65F38074-B24E-4149-9398-DAC63D4FF99E@microsoft.com...
> >I created a crosstab query that outputs sales by each month (month as a
> > column header).  I am creating a second query to retrieve sums of the 
> > months
> > I am interested.
> > Let's say the sales is as follows
> >
> > Month1  Month2    Month3
> > $200      $100        $300
> >
> > Here is the formula I am using:
> >
> > Last3Months:  Month1 + Month2 + Month3
> >
> > Instead of getting the sum which shoudl be $600,
> > it looks like the formula is concatenating the values resuting in 
> > 200100300
> >
> > i guess i can just sum it up in Excel but wanted to see if i can do it in
> > Access.
> > 
> 
> 
> .
> 
0
Utf
5/20/2010 6:37:01 PM
Reply:

Similar Artilces:

Odd Parameter request in Crosstab
Good Morning, I read the threads about creating parameters in crosstab queries and this helped me to get it to work but the parameter box pops up when I try to save the query. Though my changes are saved, I don’t think this is normal? It happens in all views. Can anyone tell me what might be wrong? Here is my SQL: PARAMETERS [Enter Month and Year] DateTime; TRANSFORM Sum(Trans_Mstr_ODC.ODC_Cost) AS SumOfODC_Cost SELECT Trans_Mstr_ODC.TransDate, Trans_Mstr_ODC.Category, Trans_Mstr_ODC.Product, Sum(Trans_Mstr_ODC.ODC_Cost) AS [Total Of ODC_Cost] FROM Trans_Mstr_ODC WHERE...

Looking for data in 1 worksheet & copying the results into another
Hi, Using Excel 2002. What I have I have a workbook (Inventory) with the following data in it.. Username first name, last name, Department, Division, Computer make, Compute Model, etc. in addition to various hardware and software inventories o the computer associated with a particular user. I have created worksheets for each Department in the 'Inventory worksheet (e,g Finance) What I want to do: e.g .Use a macro, formula etc. to look at column 'F' in the 'inventory worksheet and copy the entire row of any field that is = to 'Finance' into the Finance worksheet, HR i...

Creating Date Range in Pivot Microsoft Query
I have a pivot that I've connected to our SQL db using the MS Query. I'm having two problems with it: 1. In Access I create a date range to see dates between 3 and 15months ago with the SQL statement " WHERE (Trx.TrxDate) Between (Date()-485) AND (Date()-120)" - no problem in Access but not the MS Query program. When I do it through the Excel MS Query it says the Function 'date' is not valid. How do I describe the current date in the MS Query program? 2. In the same wizard I cannot combine two fields (in Access I would use Expr1: [Field1]&", "...

Error 'Invalid object ##Options' while running Multilevel query in
I created a company in GP9.0 with Analytical Accounting + Service Pack 1, created a transaction dimension and some codes, posted transactions in POP, GL and Inventory. When I ran the Multi level query wizard, i got the error '[Microsoft][ODBC SQL Server Driver][SQL Server]Invalid object name "##Options" when I gave all the values and the system started to run the report. What could be the problem? Thanks - Murali The options you are selected in MQW are stored in temp table and using that "##Options" table is created in run time.It might be because the temp ta...

Union queries and crosstab...
I created a union query that combined three tables. Three tables that store three types of deposits (ID, Coupon, Misc). The union query has a criteria of a date range. The fields include SalesAmt for all of the deposit types. If one of the tables do not have data for that date range (let's say Misc), the SalesAmt column for the Misc table does not show up in the query. Is there a way for me to have a filler column so it can be displayed in a report? Currently, if a table has no data, the report errors out saying the column does not exist. Thanks. Use a NZ function on that fie...

Sorting data in a query
Hello, I have a report based on a query that returns employees holiday dates in descending order. This works well but new boss only wants to see the last holiday that staff had. I have tried various methods of trying to return the LAST HOLIDAY DATE FOR EMPLOYEES but I can not achieve it. Could anyone help please? Current query example returns: Mr Smith 21 Oct 07 Mr A 1 OCT 07 mRS b 28 Sep 07 Mrs b 1 Sep 07 Mr z 29 Aug 07 Mr Smith 28 Aug 07 Mr A 20 Aug 07 Miss Y 1 Aug 07 The new query needs to return: Mr Smith 21 Oct 07 Mr A 1 OCT 07 mRS b 28 Sep 07 Mr z 29 Aug 07 Miss Y ...

Trouble with Query Statement
I keep getting the following error with the code below: runtime error '13'; Type mismatch. Set rst = CurrentDb.OpenRecordset("SELECT tblCaseInfo.CaseID, tblCaseInfo.DHSNo, tblCaseInfo.Region, tblCaseInfo.DHSAttny, tblCaseInfo.CaseName, " _ & "tblAction.CaseID, tblAction.Actn, tblStatus.CaseId, tblStatus.ClosedDate, tblStatus.StatusRptNotes " _ & "FROM (tblCaseInfo INNER JOIN tblAction ON tblCaseInfo.CaseId = tblAction.CaseID) " _ & "LEFT JOIN tblStatus ON tblCaseInfo.CaseId = tblStatus.CaseId " _ & "WHERE ((...

Query to Determine if Course is Taken
Hi, I have a table that lists courses an employee has taken. I also have a list of courses, when then expire and what their retraining course should be. Now I need to determine if the employee has taken not only the original course, but the retrained course when they're on separate lines. So.. if he's taken course 123 which expires in 12 months. Then he takes course 124. It shows in the list of courses that he's taken that 124 was also completed. However, my flag just determines that 123 is expired. I need that flag to not show if 124 has been taken. Hope that's a compl...

Date parameters on crosstab report?
I am reporting on orders analysed by advertising source. I have built my first crosstab query that works fine, and then a report based on that which also works OK. However, we need to select the date range (order date) for which it is reporting. In the past with ordinary select queries I have simply added Between [Enter start date] And [Enter end date] to the appropriate date field column in the design grid and it has worked fine. But on this one it makes no difference - it either continues to display the whole set of data, or nothing at all, depending on the syntax I use. Is there som...

Excel query #5
Hi, I'm using Microsoft Query to get data from my Access tables. After the 64th query, I get the message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed", which means I can't add another query. I need several more queries. Each query has 53 fields. Can I increase the number of queries the driver can open some way? If not can, pivot tables or OLAP cubes open more queries. I have played with them some. Can they open that many fields? Are pivot tables or OLAP cubes the best way to handle the data? I'm not looking for a 'how to' ans...

Query Criteria 04-15-10
Hi, Some of the fields I have in a table which is imported from a csv file are: Salesperson (has up to 20 names in it) Status (has Won,Lost,Submitted,Prospect,Did Not Proceed,Estimation) Status Date (has date) Value ($ value) What I am getting stuck on is a query to get the total value per Salesman for each status and also the count of each status, I would like it to show in datasheet form something like below. Salesman $won No. Won $lost No.Lost A $100000 5 $150000 3 B ...

Queries to Views?
I posted this question in a SQL Svr newsgroup and was basically slapped down for doing so. I was told this is an "Access" issue and I should try an "Access" group (man, those guys can be sensitive!). So, here we go... I've tried a few different approaches but, each time I try to convert an access query to a view in SQL Svr 2000, the result is a table not a view. Does anyone know how to take the dozens of queries I've created and move them into SQL server as views without copying and pasting the SQL statements into query analyzer one at a time? I've ...

Templates and Queries
I have an Excel Template that contains a query pulling from and Access Database. I have tried to modify this query, however, it doesn't seem to accept my changes. I can create a new query within a new template and then it works fine. Rather than doing this for everyone, I would like to just modify the query and save that within the template. I've tried saving the query and then resaving the template also. What am I doing wrong. This is Excel XP. Thanks, ...

Network connection for query
G'day there One and All, I'm currently trying to adapt a few of the Dept's workbooks to suit our district. There is a single master book, to produce reports, that is available to all on a read only basis and read write to the relevent manager. There are also a number of data entry books that are identical to each other and that provide the data from each division. The master book uses a query to obtain data from the division's books and thereby build the reports. The data books are read write for everyone with only a single sheet from each providing data for the que...

Query to find when a total is hit
We are trying to get some statistics on our Inventory people to see if they are issuing material to work orders on time. My work order table has a field that shows the last transaction date of the order, but if Inventory issue all the material then has to issue more because of scrap later they get dinged as being late. We have a Transaction History table that shows every issue however I don't know a good way to find out when the total number of needed parts was reached. For Example I am creating a shop order that needs 3 parts, 50 of each: ShopOrder PartNeeded D...

LDAP Query needed
I need an ldap query to find all users with an exchange mailbox where the users alias does not equal the nickname of the smtp email address(s). Thanks! "MrRAlan" <MrRAlan@discussions.microsoft.com> wrote: >I need an ldap query to find all users with an exchange mailbox where the >users alias does not equal the nickname of the smtp email address(s). Thanks! I don't believe LDAP supports what you want to do. You'll have to write a small program (vbs or perl) that creates a mail address and then queries the AD for a match. -- Rich Matheisen MCSE+I, Exchange ...

Queries?
Is it possible to make an "update query" to roll back year to date overtime hours? I have an tblhours that totals all overtime hours worked and refused in which I have ran queries to sum all of those. Once a year we have to rollback the year to date overtime hours which means we take the lowest employee, he goes to "0" and we subtract the lowest employees hours from the highest employees hours. I was trying to do this in an update query, but everything I try subtracts, for example, 10 hours from every line of overtime in the tblhours table. I know you are no...

Excel VBA SQL Query Filename
I recorded a macro to pull in an external dbf file using a SQL query in VBA (Excel 2003 on an XP machine). Everything works great until I point it at the original file (was pointing at a renamed copy) that I want to pull in. The files I need to pull in will have the format <output filename>~<model 1>~<model 2>~<sub model>.dbf. The SQL query doesn't like the strange characters (~'s) in the filename and if I try a wildcard within the filename (i.e. <output filename>*<sub model> or <output filename>%<sub model>) it doesn't l...

Querying dates & times
I am struggling with running a query where the field is both a date and a time. i.e. 01/25/2010 3:40 pm. When running the query, it doesn't see anything. After entering the query criteria, it only shows up as #01/25/2010# and no data selected... Any suggestions? Thx, D Can you tell us how you would like this to work? Do you want to ignore the time portion of the value or is the time significant to the criteria? -- Duane Hookom Microsoft Access MVP "Digi777" wrote: > I am struggling with running a query where the field is both a date and a > ...

Creating addressbooks that query on OU
This is a multi-part message in MIME format. ------=_NextPart_000_000C_01C4174B.630E15B0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable How would I create an addressbook that is based on the contents of an OU = in AD? ------=_NextPart_000_000C_01C4174B.630E15B0 Content-Type: text/html; charset="iso-8859-1" Content-Transfer-Encoding: quoted-printable <!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN"> <HTML><HEAD> <META http-equiv=3DContent-Type content=3D"text/html; = charset=3D...

Summing a crosstab
Is there a way to do a sumif on a crosstab? For example: Issue Year Duration 2,004 2,005 2,006 2,007 2,008 2,009 0 453 - - - - 505 1 - - 789 498 - 2 650 - - - 3 5,496 1,086 2,210 4 - 6,241 5 13,664 I want to summarize by Incurred Year, which would be Issue year + Duration So for Incurred Year 2009, I want to sum all cells where the Issue Year + Duration = 2009. sumif (row + col = year) Incurred Year Amount 2009 22,621 2008 1,584 2007 6,285 2006 650 2005 - ...

Football Results and Tables
i run a football league and would like to know if there is any formulas i could use for when i put a score in a cell it automatically sorts the table out to go with it. Many thanks Benn ------------------------------------------------ ~~ Message posted from http://www.ExcelTip.com/ ~~ View and post usenet messages directly from http://www.ExcelForum.com/ ...

Crosstab Query Wizard...Can percentages also be displayed?
When using the Crosstab Query Wizard to count values, is it possible for either the Query or Report (made from the query) to also display the percentages that sum to the total column? Here's an example of what I'm looking for: TOTAL CATEGORY1 CATEGORY2 4 (100%) 3(75%) 1(25%) ...

SQL Crosstab Question
I have written the following SQL Statement: TRANSFORM Sum(qryTSExportFY.Hours) AS SumOfHours SELECT qryTSExportFY.ProjNum, qryTSExportFY.ProjSub1, qryTSExportFY.ProjSub2, qryTSExportFY.LastName, Sum(qryTSExportFY.Hours) AS TotalHours FROM qryTSExportFY GROUP BY qryTSExportFY.ProjNum, qryTSExportFY.ProjSub1, qryTSExportFY.ProjSub2, qryTSExportFY.LastName PIVOT qryTSExportFY.FiscalYear In ("FY05", "FY06", "July 2006", "August 2006", "September 2006", "October 2006", "November 2006", "December 2006", "Janua...

Replacement Query
Hi all, I have three fields in my table: Date1, Month1, Year1 The data is as show in the Date1 field: 01/01/2007 01/11/2007 01/03/2006 05/02/2007 07/02/2007 07/09/2006 11/09/2007 Now, I want to replace Month1 with "01-Jan" for any Date1 = to "01" and "05-May" for any Date1 = to "05" and 07-Jul for "07". Also, I want to replace Year1 with the year of Date1: ex. "2007" = 2007 and "2006" = 2006. Could this be done? Thanks again You could create an update query that would do this. However, it is generally considered...