Crosstab query columns not adding correctly

I have a crosstab query based off of a table "tblClaims".

TRANSFORM (Nz(Sum(tblClaims.Count),0)) AS [The Value]
SELECT tblClaims.LOC, tblClaims.RvwRsn, Sum(tblClaims.Count) AS Total
FROM tblClaims
GROUP BY tblClaims.LOC, tblClaims.RvwRsn
PIVOT tblClaims.Report In ("C170","RC 85");

My end result should have one row of data for, example: 
LOC               RvwRsn           C170            RC 85
72                 BTMJ                25               10

But instead I am getting:
LOC               RvwRsn           C170            RC 85
72                 BTMJ               25                0
72                 BTMJ               0                 10

Now I have done other more complex crosstab queries where I had the same 
problem but resolved it based on how the table was set up.  For this 
instance, my table "appears" to be set up correctly:
LOC        RvwRsn         Count          Report

Still I cannot get the data how I want it.  Any thoughts???

Thanks.
Jen
0
Utf
1/11/2008 7:20:04 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
1006 Views

Similar Articles

[PageSpeed] 34

Never mind...the RvwRsn field (from 2 different data sources) has extra 
spaces from the one source.  Used "Trim" and fixed it.

"Jen" wrote:

> I have a crosstab query based off of a table "tblClaims".
> 
> TRANSFORM (Nz(Sum(tblClaims.Count),0)) AS [The Value]
> SELECT tblClaims.LOC, tblClaims.RvwRsn, Sum(tblClaims.Count) AS Total
> FROM tblClaims
> GROUP BY tblClaims.LOC, tblClaims.RvwRsn
> PIVOT tblClaims.Report In ("C170","RC 85");
> 
> My end result should have one row of data for, example: 
> LOC               RvwRsn           C170            RC 85
> 72                 BTMJ                25               10
> 
> But instead I am getting:
> LOC               RvwRsn           C170            RC 85
> 72                 BTMJ               25                0
> 72                 BTMJ               0                 10
> 
> Now I have done other more complex crosstab queries where I had the same 
> problem but resolved it based on how the table was set up.  For this 
> instance, my table "appears" to be set up correctly:
> LOC        RvwRsn         Count          Report
> 
> Still I cannot get the data how I want it.  Any thoughts???
> 
> Thanks.
> Jen
0
Utf
1/11/2008 7:29:00 PM
Reply:

Similar Artilces:

Creating a combination chart using cluster column & stacked bar t.
I need to create a combination chart, using cluster column and stacked bar types. I need to "cluster" two columns side by side without space in between, with stacked bars within each of these columns. Then, have space along the x axis, and have 2 more columns clustered made up of stacked bars within. I am charting 2 years side by side, with different types of data within the columns, so need to stack/segment the column. How do I create and combination chart, and how do I select these 2 types to combine together? HELP, PLEASE?!? If you stagger your data, you can create sid...

2010 CAS and 2007 CAS same AD site
Hello all I have a 2007 sp2 CAS server and a 2010 CAS server located in the same AD site. On the 2007 CAS server i have changed the externalurl from https://mail.kbblab.com/owa to https://legacy.kbblab.com/owa, i left the internalurl at its default setting. On the 2010 CAS server the externalurl points to https://mail.kbblab.com/owa and i set the Exchange2003URL to https://legacy.kbblab.com/owa. The 2010 CAS server is using FBA authentication and basic with SSL. The 2007 CAS server is using NTLM and windowsintegrated for internaluathenticationmethods. I have created an ...

Outlook not printing correctly
I have Outlook 2003 and when I print an html message it prints out smaller than the original size and everything is aligned right. So only the right half of the paper is being printed on and the font has shrunk down. This only happens with HTML emails. Plain text emails print fine and other documents print fine. The problem is in Outlook somewhere ...

Cross Tab Query
I have a access database with SQL backend. The table labproofs has charges listed by report date, with the below query, I can run a cross tabquery with the end date selected from a form (monthly report), and it gives me the 12 month data from the end date selected. However, the problem I face is for example if the end date selected is feb 28th, I get the data for previous months as Jan 28, Dec 28, Nov 28 ......, I want data for the month end numbers like feb 28, Jan 31, Dec 31....., I am not sure what I am doing wrong here. Please help. Gmen PARAMETERS [Forms]![Monthly Reports]...

Reports
Sorry if this may seem an obvious question; I've recently started with CRM 3.0 and have been staring at it for too long! I have created a custom field on the case form called TimeSpent. On my journey to Utopia (to send e-mail automatically to client with Case Desc, Time Spent, Time Remaining) my immediate basic requirement is just to have TimeSpent as one of the columns. So, until I get to Utopia, I generate report - Export to Excel - click to drill down - and I have most of columns except for my custom TimeSpent! Can anyone help me Regards Did you publish your customizations? Hi F...

Adding a Keyboard Interface to a DialogBox???
I want to add a keyboard interface to a DialogBox. In particular I want to take actions based on the <INS> or <DEL> key. Does anyone know how to do this? "Peter Olcott" <NoSpam@SeeScreen.com> wrote in message news:jriKh.40880$Ko5.17706@newsfe08.phx... >I want to add a keyboard interface to a DialogBox. In particular I want to take >actions based on the <INS> or <DEL> key. Does anyone know how to do this? > Have to over-ride the PreTranslateMessage() handler because with DialogBoxes, all keyboard messages are routed to the controls on th...

Forward misspelled names to user account if doman is correct.
Is it possible to catch for instance a misspelled name like moike@domain.com that should be mike@domain.com and forward it to another account? It should be able to pick up anything (like *@domain.com) and forward it to joe@domain.com. Thanks in advance. Mike Are you really sure that you want to do this? If so, you can take a look at the methods listed here. http://hellomate.typepad.com/exchange/2003/08/exchange_catcha.html -- Ben Winzenz Exchange MVP MessageOne "pakitloss" <pakitloss@discussions.microsoft.com> wrote in message news:24DB863D-49F5-457A-BA47-B7D26F422...

Two different queries return same results
I’m calculating average returns for stocks over two time periods; 30-days and 90-days. These two queries always return the same results for different time periods and I have no idea why. PARAMETERS [Forms]![frmMstr]![cboEnd] DateTime; SELECT Avg(SharePrices.StockPrice) AS AvgStockPrice30_Days, SharePrices.StockSymbol FROM tblStocksGroup INNER JOIN SharePrices ON tblStocksGroup.Ticker = SharePrices.StockSymbol WHERE (((SharePrices.DateTime)>=DateAdd("d",-21*(365/250),[Forms]![frmMstr]![cboEnd]))) GROUP BY SharePrices.StockSymbol; PARAMETERS [Forms]![frmMstr]![c...

adding values from 2 columns
I am trying to compose a personal cash flow chart where I can enter outgoings and income for every day of the month and see what the balance is on any day. I need to get a value in Col F which adds the value in Col C (daily income), subtracts the value in Col E (daily outgoings) and shows the balance. Also I may have to specify several rows if I have multiple outgoings on a particular day. Any ideas gratefully received. Gaby Sounds like you want a checkbook register. Something like this? A B C D E Start Bal $1,023.32 Date Item Expense Deposit Balance $10.12 $1,013.20 $23.3...

Adding Toronto Exchange to watch list
I've just re-installed Money 03 and now I need to add the Toronto Stock Exchange (TSX) to my "Investment Watch List". It's not listed in the list of exchanges. Can someone help? Thanks Is there anyone out there that can answer this for me??? Anyone??? On Fri, 12 Sep 2003 16:12:05 -0400, Steve P <Steve25261@comcast.net> wrote: >I've just re-installed Money 03 and now I need to add the Toronto >Stock Exchange (TSX) to my "Investment Watch List". It's not listed >in the list of exchanges. > >Can someone help? > >Thanks In ...

rgxextract() in a query to extract any one of many possibilities
I am using access 2003. rgxExtract() from http://www.j.nurick.dial.pipex.com/Code/vbRegex/rgxExtract.htm#Code has helped me solve a problem partially but if anyone can tell me how to get rgxextract() to find in a text Field "this or that or the other" heres whats going on; I am pasting information into a Field, and rgxextract in a query looks through the data that can be in any order to find what I ask it to. However the data doesn't cooperate as easily. for example the pasted data will read "a cute 3bdr house for rent" and the following will extract the ...

Ads in Microsoft Money
The advertising in Microsoft Money is really getting on my nerves. I clicked on the Taxes tab in Money 07 the other day, and an ad for Jessica Alba's new movie appeared in a box on the Taxes page. I click on items that I think will be planning tools and instead, a browser pops up with content from the MSN Money website. I really don't understand why Microsoft would think they need to generate income through advertising, especially with the unrelated ads in personal finance software. This bugs me so much that I decided to blog about it, and I don't usually use my blog to vent fr...

Try this correction package
--cmmsnunwpw Content-Type: multipart/related; boundary="rewxxgfscrv"; type="multipart/alternative" --rewxxgfscrv Content-Type: multipart/alternative; boundary="potqqbkgxfmns" --potqqbkgxfmns Content-Type: text/plain Content-Transfer-Encoding: quoted-printable MS Customer this is the latest version of security update, the "September 2003, Cumulative Patch" update which fixes all known security vulnerabilities affecting MS Internet Explorer, MS Outlook and MS Outlook Express as well as three newly discovered vulnerabilities. Install now to protect you...

Is There A Way to Test if Global Catalog Functioning Correctly
Still dealing with some cleanup issues from a DC loss last week. The DC was a Global Catalog server. However, I *believe* we had another one in place. At least, there was a checked GC box under the NTDS properties. However, I was wondering if there is some sort of test to make sure a GC is available and functioning before I proceed with the metadata cleanup of the dead domain controller? Not that it matters much at this point, I suppose. Still, it would be nice to go into this feeling comfortable about having a GC available. Thanks Hello Mark, See the last part ...

Table and query question
I would like to create a table that will relate an account to its parent(s). 1 account can have multiple parents, and 1 parent can have multiple accounts. And, a parent can have a master parent. For ex: Account 1 can have Parent 1. Account 2 can have Parent 1 and Parent 2. Parent 1 can have Parent 2. And, the queries that I will perform will request these data: 1. give me all parents of Account 1. The query should return Parent 1 and Parent 2 (because Parent 2 is the parent of Parent 1) 2. give me all parents of Account 2. The query should return Parent 1 and Parent 2 3. give...

T-SQL query for finding shared user and team records
Hi all, I'm new to CRM and would appreciate any help. I need to find account records that record owners have shared to users or teams in CRM. I've found out that this requires direct query to filtered view tables. Microsoft responded as below by I need actual T-SQL query. Can anyone help with this? You would need to write a query against the PrincipalObjectAccess table and you would need to have joins to the other related tables. If you tried to have it all displayed using one query, it would be a pretty complex query. This is because you would need to join to the FilteredA...

Import and Export from/to Excel query
I need to import and export Excel files in and out of Outlook 2003. When I try and do it I am told I don't have the correct translator and do I want to install it - when I say yes it tells me to put in the CD - when I put in the CD it doesn't install but just freezes up and I have to cancel - the file it tells me I need is L4561403.CAB How and where do I install L4561403.CAB which I assume that I can find on the CD?? TIA for any help. Brian Tozer KiwiBrian <briantoz@ihug.co.nz> wrote: > I need to import and export Excel files in and out of Outlook 2003. > When ...

Adding data from another sheet.
I hope I can find some help on this one. I have a spreadsheet that consists of two sheets. The first is a form. The second is a list of questions. I have added a checkbox next to each question on sheet 2 to select the question and add it to the form on sheet 1. The problem: when you select a question on sheet 2 it will only add it to a specific cell on sheet 1. So if I select question 15 it will go in a position like it is the 15th question when actually I would like it to be the first question. The question: Is there a way to make the questions start adding to the top cell on sheet 1...

Products by column constants
A ver y simple question for your experts. I have a column of number that I want to multiply by a constant ( a price list tthat I need t increase by 5%). How to do? I can do one line at a time but how do I d the entire column -- Message posted from http://www.ExcelForum.com conn Enter 1.05 in a cell. Copy this. Select your data column and Paste Special>Multiply>OK>Esc. If you set up your formulas initially with a multiplier cell in the formula you can just change the value in the multiplier cell. e.g. Formula in C1 =(A1+B1)*D1 Change the value in D1 and the result in C1 will...

queries written inside MS-Access are getting deleted
Some of the queries I have written inside MS-Access are getting deleted automatically. And while I run the queries through code, I get this error 'Query should have one destination field' Explanation: I created a query in MS-access. Ran it from the code. Closed the database. Started it again, and now for that particular query, it is showing 'SELECT ;' only. Strange. I am in panic mode now What can be the possible reason? My first thought is that the database file is corrupt. Unfortunately it's a type of corruption that Compact and Repair probably won...

Adding USB To Dell Latitude D600
I have a used Dell Latitude D600 O/S WINXP PRO. I am trying to find a way to add additional two USB ports making it a total 4 usable USB ports for: 1 - Wireless Mouse 1 - 16GB Toshiba external drive (backup) 1 - 400GB Toshiba external drive (Data drive) 1 - USB wireless modem or external floppy drive. I tried using a 4 ports Hub and change to 4 ports external powered Hub. Both are not able to drive even 3 USB attachments. If I use Dell docking station (4 USB Hub), I can only use three USB attachments and that depend where I attach the three USB attachment. Is there any so...

Printing a worksheet in two (or more) columns
Hi, I've got an extremely long spreadsheet table comprising two columns of data. I'd like to print these data in a more compact form - in the same way that the entries are organised in a telephone directory. Say on average my page is 50 rows high - my first two columns on page 1 would appear on the left hand side and be rows 1 through 50, row 51 would continue at the top of the page, but on the right-hand side. Row 101 would appear at the top left hand side of page 2, etc. I'd like to keep the orientation of the columns in portrait (I did find a printer-associ...

how can i relate between two columns in two sheets?
Question no too clear - you can use this space to give a detailed question. But here goes: On Sheet1 I can type formulas such as =Sheet2!A1 =SUM(Sheet2!A:A10) If I type = and then click on the cell A1 of a worksheet called My Yearly Totals, I will get the formula ='My Yearly Totals'!A1 Note that a sheet name having spaces get surrounded by single quotes. Does this answer your question? best wishes -- Bernard Liengme Microsoft Excel MVP people.stfx.ca/bliengme email address: remove uppercase characters REMEMBER: Microsoft is closing the newsgroups; We will all me...

How can I automatically remove duplicate data within a column?
Is there a way to automatically remove duplicated data within a column in Excel? For example, I have a column of over 10000 rows. The cell contents are call numbers, many of them are duplicate and I'd like to remove them without sorting and manually removing them. Chip Pearson has a means for doing this at http://www.cpearson.com/excel/deleting.htm If it is just a single column, though, you can use Data>Filter>Advanced Filter. Once there, select Copy to a new Location and Unique Records only. That will give you a list of unique values in your column - i.e., no duplicates...

Dup check 2 columns
Using Excel 2007, I have a two column list with over 100,000 rows. Example: Jane Doe U Jane Doe John Doe John Doe U John Doe Jack Doe Jack Doe I want to run a dup check to make sure there is only one Jane, Jack or John Doe, but the copy I want to stay is the one with a U behind it if there is one (wouldn't matter which row is kept for Jack). From what I've done so far, the dup check simply keeps whichever record is in the top row. Seems like it should be an easy fix, but I'm lost. Please help. Hi, If the rows you want to keep ar...