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", 
"January 2007", "February 2007", "March 2007", "April 2007", "May 2007", 
"June 2007");

My problem is that Access will create all of the columns, however, they are 
not populated with the data from the pivot table.  A SumofHours Column was 
created, but the hours do not distribute.  Any ideas where I went wrong?

Thanks for your help!


0
Utf
6/21/2007 5:56:02 PM
access.queries 6343 articles. 1 followers. Follow

1 Replies
751 Views

Similar Articles

[PageSpeed] 28

In a crosstab query, you only get one set of aggregations, that is on the 
field you select to use as a Value, and in your case shows up on the 
TRANSFORM line of the query.  

1.  Delete the "Sum(qry_TSExportFY.Hours) AS TotalHours " portion of the 
SELECT clause

2.  Do you really have all of the values "FY05", "FY06", "July 2006", ...
in your [FiscalYear] field?  I would think you would have a column for 
FiscalYear (FY05, FY06, FY07, FY08) and another column for FiscalMonth.

HTH
Dale

-- 
Email address is not valid.
Please reply to newsgroup only.


"Leslie W." wrote:

> 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", 
> "January 2007", "February 2007", "March 2007", "April 2007", "May 2007", 
> "June 2007");
> 
> My problem is that Access will create all of the columns, however, they are 
> not populated with the data from the pivot table.  A SumofHours Column was 
> created, but the hours do not distribute.  Any ideas where I went wrong?
> 
> Thanks for your help!
> 
> 
0
Utf
6/21/2007 6:14:00 PM
Reply:

Similar Artilces:

Calling Dex Procedures from SQL
Is there any way to call dex procs from SQL or VB code? Jed There is an undocumented and unsupported method of calling Dex code from VBA. However, I know of no method of calling Dexterity from SQL. Please email me if you want an example, just remove the online. from the address below. David Musgrave [MSFT] Senior Development Consultant MBS Services - Asia Pacific Microsoft Business Solutions http://www.microsoft.com/BusinessSolutions mailto:dmusgrav@online.microsoft.com Any views contained within are my personal views and not necessarily Microsoft Business Solutions policy. This p...

DSum Question
I have a continuous form with an unbound text box that has a DSum function as it's control source. It is used to display the sum of a field in a query. It works fine except that the last line of my form (the one for a new record) displays #Error in this text box. My DSum function is; = DSum("SumOfFootprint", "qryBoxTotals", "BinID = " & [BinID]) I've tried assigning it to the text box control source through code; Me.txtFootPrintSum.ControlSource = DSum("SumOfFootprint", "Query1", "BinID = " & Me![BinID]) but th...

I cannot see me questions!!!
I posted two questions onto the system: Query of excluded items CrossTab Query but I cannot see them or find them with a search!!! The system sent me an e-mail saying that they have been responded to, but if I cannot see them it is not of much use. Do I have a setting wrong? Please feel free to contact me directly pmw0813@yahoo.com Thanks Send to you to your supplied email. Vanderghast, Access MVP "Paul" <Paul@discussions.microsoft.com> wrote in message news:DF3A5ED4-122E-4AFF-BE51-242AAF4DB5CA@microsoft.com... >I posted two questions onto the system: > >...

is SQL 2000 architecture different SQL Server 2005 / 2008 architecture?
Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I have been told is a good book. I am going backwards... reading about the sql server architecture in chapter 3 of the book. As I'm reading... I was wondering is it different SQL Server 2000 vs SQL Server 2005/2008? Also where can I find a book/or link that can give me more details about the internals on architecture? Thank you norm (normanchan@gmail.com) writes: > Hi I have a book "inside SQL Server 2000" by Kalen Delaney, which I > have been told is a good book. I am g...

Moving customizations from SQL 2k to SQL 2k5 CRM
This may be a non-issue but I wanted to check to see if anyone has done this and ran into issues. I have the XML customization files from our previous install of CRM 3.0 with SQL 2k database. I now want to import and publish these same customizations to CRM 3.0 on SQL 2k5. Has anyone done this or can point me to documentation that tells about the success or issues for doing this? Thanks. On 7 Feb., 06:35, Chris Treanor <ChrisTrea...@discussions.microsoft.com> wrote: > This may be a non-issue but I wanted to check to see if anyone has done this > and ran into issues. > &...

VBA Question #4
Hi: I have BASIC programming skills but have never had the opportunity to use VBA inside Excel. Using a UDF, I need to know how to take the contents of a cell and pass it to a variable inside my user defined function. I am trying to take the results in one cell and if it matches, then take the information from two other cells and multiply them together. I've poured over the VBA documentation and see how to push a value into a cell, but can't find any examples of retrieving a cell's content. Thanks in advance! -- Steve Spence Independent AMSOIL Dealer AMSOIL - The "...

CRM 3.0 Install Error with SQL Reporting Services and SQL 2005
Hi, Doing a CRM 3.0 Server installation. During the last page of the install wizard (or what I assume is the last page) when I goes through and does verification I get a red X next to SQL Reporting Services. The message I receive when I look at the details is: ***** The specified path is not a metabase path. Parameter name: path ***** Now SRS is installed and working. I put in the URL: http://<servername>/reportserver and every other variation on that I can find. Thoughts? Alan try http://servername/reports also assuming you did not use ssl... =======================...

OWA 2003 questions
Hi Before i was using Exchange 2000, since I've made the upgrade to Exchange 2003 when I go to the OWA 2003 web site I have this message after my credentials has been entered. This page contains both secure and non-secure items. Do you want to display the non secure items. After that message the padlock at the bottom of the screen disapears. This is not seems good to me When I was on Exchange 2000 I didn't have this message Thanks Are you using OWA with our without SSL? If you are trying to use it with SSL you will need to have a certificate authority setup in your doma...

SQL 'for xml' and C# example??
I'm missing some piece of the puzzle. I'm using 'for xml' type queries from SQL2K - and now trying to change the front-end from ADO to C#, I can't quite figure it out.. It seems that I could use an XmlReader.. and somehow use that instead of a DataReader.. but that didn't work.. I tried using a DataReader and get the byte array - but that wasn't right. SQL returns formatted XML, I need to load that XML into an XmlDocument somehow without ANY molestation of that dom. Any ideas? Examples? Missing links???? thanks THANKS!! I've been looking for the answer to ...

sql summarizing help
Hi All, Given: table1 --------- id, cdate, weeksago, amount, typeid, timeclose 496, 2010-02-11, 0, 450, 3, 1 490, 2010-02-01, 1, 200, 4, 1 491, 2010-02-01, 1, 350, 2, 2 493, 2010-02-01, 1, 500, 5, 1 489, 2010-01-21, 3, 150, 2, 1 136, 2010-01-12, 4, 500, 3, 2 137, 2010-01-12, 4, 100, 3, 1 138, 2010-01-12, 4, 500, 2, 1 1. i'm trying to create a sql that will give me a summary resultset grouped by weeksago. 2. i want to sum the columns and get counts based on typeid. 3. avg of timeclose here's the sql i have: select weeksago, , (sum(timeClose)/(count(timeclo...

thread question #4
How can I determine in a block of code if the thread it's running in is the same as the main GUI thread? I was trying to use GetCurrentThread to get the thread the function is being called from. Not sure how compare that to GUI thread. On Wed, 21 Dec 2005 13:58:35 -0600, "Eric Margheim" <NOSPAM***eric@prism-grp.com***NOSPAM> wrote: >How can I determine in a block of code if the thread it's running in is the >same as the main GUI thread? > >I was trying to use GetCurrentThread to get the thread the function is being >called from. Not sure how...

Update multivalue field in table from form with SQL
Access 2007 on Vista I am using a form to establish the variables for a report before it is created. One field on the underlying table is a multivalue field, and the form includes a multi-select combo box control. After selections are made and the OK button on the multi-select combo drop down is clicked, focus moves to the next control. I have discovered though, that the underlying table does not update immediately, but does so correctly when I close the form. This sequence does not deliver the proper report data, so I need a way to immediately update the underlying table (m...

Stupid question, I know
I feel like an idiot for even having to ask but I haven't been able to find a definitive answer anywhere. When building a new Exchange Server, do you need both an O/S (Windows Server 2003) and Exchange Server (Standard or Enterprise) Edition on the server or does the Exchange Server 2003 serve as the O/S? Let the comments begin.... You need Windows Server 2003 or 2000. Now if you purchase SBS 2003 then Exchange comes as part of the SBS Suite of applications but all applications must be run from the SBS Server. -- John Oliver, Jr MCSE, MCT, CCNA Exchange MVP 2006 Microsoft Certified ...

Refer to SQL statements globally
I have two large SQL statements that I need to use across several forms, reports, etc. Rather than repeat them in each form, I'm trying to store and reference them globally. For example, I'd like to store strSQL1 and strSQL2 in a global module, so I can use either like this in a form: Random form: Me!lstResults.RowSource = strSQL1 Random report: Me.Report.RecordSource = strSQL2 Would I just store the SQL statements in a global module like: Public Function SQLSource() As String Dim strSQL1 As String Dim strSQL2 As String strSQL1 = "SELECT blah blah...

To SQL or not to SQL?
I have a very vertical product developed 100% in M/S Access. I developed it myself in 1993 in the initial release of Access and been with it ever since. It is currently very stable and running in Access 2003. My big question is how or even *if* we should move it to SQL. My customers are getting bigger all the time with more users. Typically our customers run one or two workstations generally never more than 5. Performance, however, is starting to slow with database sizes passing 500 meg. We also have a LOT of code behind forms so are bound tightly to the Access development environ...

Questions #7
Please can anyone help me with a couple of questions I have about am Excel XP spreadsheet I have devised to pass out to other people. 1. I have a graphic on the spreadsheet which I want to make 'delete proof' I have used cell protection and a pass work to protect the sheet, but the graphic is still removeable, is there a cure for this? 2. On the sheet is a graph, the graph itself needs to be protected, but the titles could do with being editable, or linkable to another cell (in a seperate data sheet) that the user could edit to change the titles. I'm not a stranger to VBA, ...

MDI, beginner questions
Im trying to find the best way for me to implement multiple mdi child windows with dialog layout. My application will need several dialog windows with different layout (sort of like photoshops toolbar windows). All windows will only occur once so there is no need for any new/open/save functionality. I only need MDI to keep all windows organized. Could someone please help me with a simple code example? I've read a bunch of usenet posts, but I still can't figure out a suitable approach for my type of app. Thanks! Fredrik wrote: > Im trying to find the best way for me to implement ...

Question about antivirus for Exchange 2003
I currently use Mcafee groupshield for exchange 5.5 and while it does block infected attachments, it still sends the email message to the user which generates alot of complaints. I am ugrading to Exchange 2003 and this time I want to get something that is a little more flexible and can block not only the attachment, but the message too. I will look into the latest Mcafee solution, but wanted to hear about the experiences of others on this NG. Thanks NH Hi Ned, I'm using McAfee GroupShield for Exchange 2003. For your information, the software still behave exactly as you mentioned in...

PostgreSQL or other SQL servers in RMS
Has anyone successfully implemented RMS with one of the free SQL servers? Theoretically, is there any reason why we have to use MS SQL Server 2000 for large databases? David "dh" <dh@discussions.microsoft.com> ha scritto nel messaggio news:1783A90D-AF3E-4D91-A445-0039D4E7E379@microsoft.com... > Has anyone successfully implemented RMS with one of the free SQL servers? > Theoretically, is there any reason why we have to use MS SQL Server 2000 > for > large databases? RMS has implemented only for MS SQL Server (2000/MSDE) and isn't possible to work with ano...

Requey Question?
On my Switchboard [frmMain] I have a Combobox [cmbTaxWarning] it shows clients through a query which Clients have not been taxed My problem is when I go to my Clients form and change him to No Tax then go back to frmMain my combo box dose not show the change unless I close the DB and open it............Any Help Please.....Bob "Bob V" <rjvance@ihug.co.nz> wrote in message news:e50a1Wc7HHA.4304@TK2MSFTNGP03.phx.gbl... > On my Switchboard [frmMain] I have a Combobox [cmbTaxWarning] it shows > clients through a query which Clients have not been taxed > My probl...

writeback to SQL 2005
hello! I use excel 2007 iI open a connection agianst SQL server 2005 and choose a table. In my workbook i can now see this data from this table. if i change a value in some cell i wish to writeback to table in SQL server. Is this possible? It's possible through the use of VBA and ADO, but not simply using the data connection you've already established. Ross "CJ" <CJ@discussions.microsoft.com> wrote in message news:D54EC521-0DD5-4B3D-971B-4FF3394D6FA8@microsoft.com... > hello! I use excel 2007 > iI open a connection agianst SQL server 2005 and choose a table...

very general mail question
Hi, I just tried to send an e-mail after copying and pasting the e-mail address from a Contact (from Outlook 2007, Vista Home Premium, SP1). The message could not be sent. There were no error messages and I had no idea why this message could not be sent. For some reason the address was enclosed with single apostrophes ..... 'name@something.com' I tried copying the same e-mail address again and this time those apostrophes did not appear and the message was in fact sent. As a test I tried sending my self a message by enclosing my own valid e-mail address with enclosed with si...

legality question
hello, I'm implanting a graph control in my program, if ill use ms graph/chart control will it have any legal issues connected to it? any problems in selling? distributing and etc.? It depends upon the company selling graph/chart control. You will need to contact them. Typically its all spelled out in the license agreement that you got when you purchased the control. -- Ajay Kalra [MVP - VC++] ajaykalra@yahoo.com "fima" <fkpkot@gmail.com> wrote in message news:Oy0iH2UPGHA.648@TK2MSFTNGP14.phx.gbl... > hello, > I'm implanting a graph control in my program,...

Two Questions
I am not sure if this should go in here or the VBA group but I have 2 questions I am using Powerpoint 2007 Is there a way in VBA to delete all the footer and date placeholders in the slide master og my prsentation. I do not use them and I have a lot of slide master slide in the presentation Also is there a way in VBA to format all pictures in my presentation so that they use the "soft edge rectangle" format and the "rounded Rectangle" picture shape. I have a full day presentation so it is a lot of pictures to change TIA Q2 Code here should help http:...

sql.request #2
Does anyone have a comprehensible guide on how to use it that a dolt like me can follow? Every time I try I get a #N/A. The only guide I can find is at http://www.bygsoftware.com/examples/zipfiles/UsingSqlRequest.zip but it's really hard to follow. From the MSDN Library: http://msdn.microsoft.com/archive/default.asp?url=/archive/en-us/office97/html/SEC41.asp Not sure how familiar you are with ODBC; but if you are getting #N/A as a result, it is usually because of problems either with the SQL syntax or the connection string. There are a few examples of connection strings in the re...