Crosstab query question 01-07-10

I have a question about using crosstab query of course maybe there is another 
way and any help would be great appreciated.  The problem is that the 
crosstab query is that it uses some type of function(sum, first, min) etc.  I 
need it to just show all the data.  An example would be:

Instrument             Date                      Result
Machine1              12/01/2009                20.1
Machine1               12/01/2009               22.5
Machine1              12/02/2009                23
Machine2               12/01/2009               25
Machine2                12/02/2009              24

I would like the query  to show the following:

Machine1         Machine2
20.1                  25
22.5                  24
23

The fact that I have multiple results on the same date is causing me 
problems. 

Any suggestions?

Thanks
0
Utf
1/7/2010 2:37:02 PM
access.queries 6343 articles. 1 followers. Follow

2 Replies
550 Views

Similar Articles

[PageSpeed] 37

Use a Ranking in a Group query to Temp table and then the crosstab query.

SELECT Q.Instrument, Q.Result, Q.YourDate, (SELECT COUNT(*) FROM [YourTable] 
Q1 
WHERE Q1.[Instrument] = Q.[Instrument] 
AND Q1.[YourDate] < Q.[YourDate])+1 AS Rank  INTO tblTemp
FROM YourTable AS Q
ORDER BY Q.Instrument, Q.YourDate;

TRANSFORM First(tblTemp.Result) AS FirstOfResult
SELECT tblTemp.Rank AS Entry
FROM tblTemp
GROUP BY tblTemp.Rank
PIVOT tblTemp.Instrument;

-- 
Build a little, test a little.


"CR" wrote:

> I have a question about using crosstab query of course maybe there is another 
> way and any help would be great appreciated.  The problem is that the 
> crosstab query is that it uses some type of function(sum, first, min) etc.  I 
> need it to just show all the data.  An example would be:
> 
> Instrument             Date                      Result
> Machine1              12/01/2009                20.1
> Machine1               12/01/2009               22.5
> Machine1              12/02/2009                23
> Machine2               12/01/2009               25
> Machine2                12/02/2009              24
> 
> I would like the query  to show the following:
> 
> Machine1         Machine2
> 20.1                  25
> 22.5                  24
> 23
> 
> The fact that I have multiple results on the same date is causing me 
> problems. 
> 
> Any suggestions?
> 
> Thanks
0
Utf
1/7/2010 4:04:04 PM
You need to have some value in your initial data that identifies why 20.1 
comes before 22.5. 

Do you have some type of primary key or other value that can be used? If 
not, your problem is quite difficult to resolve without code.

-- 
Duane Hookom
Microsoft Access MVP


"CR" wrote:

> I have a question about using crosstab query of course maybe there is another 
> way and any help would be great appreciated.  The problem is that the 
> crosstab query is that it uses some type of function(sum, first, min) etc.  I 
> need it to just show all the data.  An example would be:
> 
> Instrument             Date                      Result
> Machine1              12/01/2009                20.1
> Machine1               12/01/2009               22.5
> Machine1              12/02/2009                23
> Machine2               12/01/2009               25
> Machine2                12/02/2009              24
> 
> I would like the query  to show the following:
> 
> Machine1         Machine2
> 20.1                  25
> 22.5                  24
> 23
> 
> The fact that I have multiple results on the same date is causing me 
> problems. 
> 
> Any suggestions?
> 
> Thanks
0
Utf
1/7/2010 4:31:03 PM
Reply:

Similar Artilces:

2K7 question
Is there a way in E2K7 to make a particular message always send a message out in plain text format? Is that just an Outlook setting? Thanks! ...

Question #3
Hi I have some data that I graph. Column A has a date [has data in 12 rows] Column B has a data entry (number) [has data in 1st 6 rows] Column C has a data entry (number) [has data in 1 st 6 rows] Column D has a formula that calculates a % on column B & C [formulas in 12 rows] The graph range I have covers the whole 12 rows but it seems to read the formulas in col D and plots every row Is there something I can add to my formula that stops the graph from plotting it unless there has been an answer returned in it? Formula I have is : =IF(B5+C5<1,"",(B5-C5)/B5) Thanks ...

Registry question
Hello Group, I have an application that saves some configuration data in the registry system (under HKEY_CURRENT_USER\MyApp\ConfData) of the currently logged on user. This way, every user who runs this app will have his own configuration data. When a user runs for the first time this app, the 'MyApp' registry key will be created along with all its subkeys. Now suppose we have 3 users and all of them have already used the app. We will then have the 'MyApp' subtree created for these 3 users. What I want to know is that when the PC admin wants to uninstall the app, how...

2008 SBS questions... 07-04-10
I am trying to get a 2008 sbs running as efficiently as I can & was wondering about a couple of things. 1) I know it wants to be the DHCP server on a network, but what about for wireless clients? I have a wireless sonicwall TZ-170 for wireless laptops to connect to. They really don't need network access, just internet, so can I leave this running, as long as the addresses it hands out aren't in my same network? 2) Although the bought an SBS, the customer is not & probably won't be running exchange for a while. Can I remove exchange all together? I really see no rea...

Defrag question #3
I have a windows 2000 server that's a file/print server and Exchange 2000 also installed and running on the same box. I was wondering what is the best way if I want to run defrag and scandisk on the disk drive? I guess I'm asking because exchange is running on the box and I don't know if I run defrag and scandisk will I run into problems or cause the database problems. Thanks for your help. John In article <4D7A888F-B293-400C-A77C-DC379DDF4BDF@microsoft.com>, John@discussions.microsoft.com says... > I have a windows 2000 server that's a file/print server and Ex...

ADMT Tool Question
I'm trying to run this tool without success from a Windows 2003 native domain to extract accounts from an NT4 domain. Is this possible? Windows 2003 server Domain: Windows 2003 native mode Forest: Windows 2000 NT4 server sp6a http://www.petri.co.il/active_directory_migration_tool_usage_nt_windows_2003.htm "bobs" <myname@yourdomain.com> wrote in message news:e27m%23Km4FHA.2532@TK2MSFTNGP09.phx.gbl... > I'm trying to run this tool without success from a Windows 2003 native > domain to extract accounts from an NT4 domain. Is this possible? > > Window...

Question about Application behaviour,...
Hi, i have a simple MFC Application, that shows me the System Memory Usage. But after a while the Applications GDI Objects Count grows to something about >8.000 GDI Objects and its Virtual Memory (not much but step by step too) in less than 5 Minutes. I can see with ProcessExplorer that the application does have a high count of page faults. I cant figure out why! The System then starts to react slowly. The Timer invokes every seconds the member function with the code following this text, sets the Position on a progress bar control and raws a transparent text on the surface of the prog...

pulling data 04-27-10
I'm not sure I am explaining this right I want the data from C21 on sheet 1 in a cell on sheet 2 if the A & B column data matches SHEET 1 row 21 A B C D E 4/21/2010 34287 74 3 4 SHEET 2 row 20 A B C D E 4/21/2010 34287 74 try =sumproduct((s1!a2:a22=a2)*(s1!b2:b22=b2)*s1!c2:c22) -- Don Guillett Microsoft MVP Excel SalesAid Software dguillett@gmail.com "Norm" <Norm@discussions.microsoft.com> wrote in message news:A40C8E08-721F-47BB-A73C-458AC...

Question about Status Reason field
I am looking into what the Status Reason field is used for in relation to the Account object. There are two default values, Active and Inactive. I want to add values so that when an Account is inactive, we can state the reason that is inactive. I can add values to the Inactive state without a problem, but then how do I choose any of the values that I defined since when an account is Inactive, I cannot edit any of the fields? I can define a default value, but that again is of no use, because I can't change it. This seems to be a bug or oversight on the part of the CRM developers. You nee...

80070490 Error: Windows update encountered an unknown error 03-04-10
3 security updates will not complete (now 4). Error Code 80070490 02-Mar-10 I am unable to do these three security updates: KB970238(release date 6/9/09), KB97451(release date 10/13/09), KB954155(release date 10/13/09), & KB975517(release date 10/13/09). The last succesful update was KB9937286 on 2/27/2010 I have a Compaq Presario PC: SR5214X. OS Name Microsoft® Windows Vista™ Home Basic Version 6.0.6002 Service Pack 2 Build 6002 OS Manufacturer Microsoft Corporation System Manufacturer Compaq-Presario System Model GV441AA-ABA SR5214X System Type X86-based PC ...

Query emails coming to a specific domain
Hello; We have about 5 domains on our exchange server that we receive email from. We are in the process of not renewing one of our domains but would like to check to see if email from that domain is still coming through. Is there any way to do that? I tried the Message Tracking Center with the *.domain.com but it didn't like that. It wanted a specific user. Thanks Check SMTP logs as well. -- Bharat Suneja MVP - Exchange www.zenprise.com NEW blog location: www.exchangepedia.com/blog ---------------------------------------------- <nospam@newsgroup.com> wrote in message news:...

publisher 97 #10
I have a file that was oriniated in Publisher 97. I inadevertently opened and saved it in Publisher 2002 on a different computer(using server). Now I can't access it through 97. Is there a way to do this? Can't save as, as the files of type don't say 97. Maybe they were the same as Publisher 98 files. Worth a try. -- Don Vancouver, USA "Terry" <Terry@discussions.microsoft.com> wrote in message news:D0B451AE-ACF2-449B-8F11-4F0C16297A12@microsoft.com... > I have a file that was oriniated in Publisher 97. I inadevertently opened > and saved it in P...

Running Office 2001 on Mac OS 10.2, help
This might be a dumb question, but I don't want to screw things up. I was running Mac OS 9.0.4 and I downloaded OS 9.1, 9.1.2, 9.2.2 and then the new 10.2. However, all my software is still in the 9.1 classic. I want to use it all in the new OS 10.2. Will Office 2001 Academic Edition work in OS 10.2? Do I need to uninstall it in OS 9.1 first, then install it is OS 10.2? Or, can I just drag and drop it on the 10.2 desktop or in the applications folder? I have a lot of other expensive softwares in OS 9.1 that I don't want to have to upgrade, but need to be able to use in OS 10.2. Wi...

Multi-language support question
We have an MFC application that uses ADO, ATL components to communicate with a SQL Server 2000 database. Our application is currently English only and we are making the conversion to support multiple languages. The text in the application is read in from a table so translating the text itself won't be a big deal programmatically. The text is loaded at log in and we can just autoswitch to the appropriate data based upon who the user is. My concerns are with different character sets, how the different languages impact the OS (how we set the character set in the OS automatically)...

DNS Question
Hello All, I am working with a customer that has 2 Small Biz servers in the same office. These are on 2 domains, but the same subnet. I am having trouble getting mail to route between the 2 domains. Each is Small Biz server 2003. Domain A has a public IP of 72.151.19.122 and an internal IP of 192.168.200.78. Domain B has a public IP of 72.151.19.123 and an internal IP of 192.168.200.250. We are using a Netgear firewall and inbound and outbound mail between each of these domains and the rest of the world works fine. They just can't seem to talk to each other. I would think this woul...

Question re:clustered column w/3D visual effect
I have Excel 2003 Why wont the 3D chart allow you to drag it more open? There is a large open area between the left side and the axis that looks jerky :) In a plain clustered column chart you can click inside to make the frame appear and drag it larger or smaller as you desire. In the 3D, clicking only allows you to change the angles of the 3D box. Am I missing something? thanks, Meenie This should be a hint to avoid the 3D effects. 3D charts are inflexible, but more important, the 3D effects mask the information in the chart. - Jon ------- Jon Peltier Peltier Technical Services, Inc....

Question about CRM and Great Plains Integration
Can I create invoice when I invoice a contract in CRM and then show the record in the Great Plains ? Thanks in advance ...

No Sound 03-26-10
Sorry for not crossposting - I have more problems than I can handle!!! While trying to uninstall Comodo, I had an error and the computer froze. I shut it off and it wouldn't boot - not even in Safe Mode. So I used ERUNT to fix the Registry from a command prompt. Unfortunately, the latest ERUNT file was corrupted - so I had to go back about a week. The computer then booted, but I found many changes in some of my data and settings. Also, I cannot get the sound to work. I again used ERUNT from Windows with the same backup I used from the command prompt. All indications on the ...

Check box question 04-23-07
I have a form with a check box used to indicate if a receipt is voided or not. What I'm trying to do is go to a new record after the checkbox is marked. I have the following code in place and it works. Kinda. The problem is that if I then go back and UNCHECK the check box, it sends me to a new record again. Here's the code: Private Sub Check43_AfterUpdate() If Ckeck43 = Yes Then DoCmd.GoToRecord , , acNewRec Thanks for any help! End Sub The following works fine for me. If Me.Check3 = True Then DoCmd.GoToRecord , , acNewRec so try If Ckeck43 = True Then DoCmd.GoToRecor...

CHtmlView question #3
Is there an easy way to get the source of the HTML document? On MSDN online, there's a GetSource(CString& str) listed but that's apparently not implemented in VC++ 6.0. ...

Custom Controls 10-16-04
Hi, I would like to create a custom control .ascx and I'd like to konow how can I put it on a standard CRM form (for example Accounts) Thanks in advance Zsolt There is not a supported way to do this on the standard forms themselves. The closest supported method wuld be to add a new Tab on the left of the page via teh ISV.Confi and have that display your control. Matt Parks MVP - Microsoft CRM ---------------------------------------- ---------------------------------------- On Sat, 16 Oct 2004 01:19:06 -0700, Zsolt Z�mbik <zombikzs@titanium-consulting.net> wrote: Hi, I would ...

A simple mail merge question
How do you change the return address on envelops printed using mail merge? I don't understand the question. The return address will be whatever you tell it to be when you set up your merge document. -- Russ Valentine [MVP-Outlook] "Nina" <anonymous@discussions.microsoft.com> wrote in message news:b16b01c3ec17$335b99f0$a501280a@phx.gbl... > How do you change the return address on envelops printed > using mail merge? ...

Question about Enable-ExchangeCertificate Command
I am trying to eliminate (more specific to my previous thread) the security alert in Outlook 2007 when opening up the program attached to Exchange 2007 with a public SSL cert and connecting to Exchange through the private address. I am seeking information about the Services column in the get (or enable)-ExchangeCertificate Command. I think I am on the right track here in that I can somehow tell Outlook to use the internally generated certificate instead of trying to use the public certificate. Can someone tell me which of the services options I should enable so that Outlook uses it i...

Data Import/ Data Map Questions
Hello, I am developing a solution that will allow various new and updated entities to be loaded from several source systems into a Microsoft Dynamics 4.0 installation on an ongoing basis. There are some up- front components to the solution that detect new/changed source system data that are custom but I wanted to be able to use the built-in Data Import facilities within 4.0 to take the data from Excel files and load it into the CRM. I've looked at the sample code in the SDK and have been able to write code that will do this, including use of Picklist and Lookup (ie parent account) type ...

Newbie questions (and maybe I need a 1hr tutorial for some $)
Hello, I am a competent vb6 programmer and understand a bit of C structures. However the C++ system is mystifying me..I simply will have to learn it to now.... I have 2 questions. 1> Can anyone tell me how code "Program 4. Microphone Select" at http://www.codeproject.com/audio/ADMixer.asp can be changed to simply self close by clicking the IDOK button routine programmatically? This is the simple effort that triggered my current angst.! (I 'think' need to set a state in BOOL CSelectDlg::OnInitDialog" to fake the IDOK event or trigger to the OnDestroy routine). Fr...