Value in Crosstab Query

Can I show all the data or merge them into one single cell, 
instead of doing any aggregate functions such as sum and average,
in a Crosstab Query?

To illustrate my question:
StudentID          TestID          Score (Date)
AAA                     1                50 (date1)
AAA                     1                60 (date2)
AAA                     1                55 (date3)
AAA                     2                23 (date2)
AAA                     2                24 (date4)
AAA                     3                98 (date4)

I want to show the data in a Query or Report in this way:
StudentID           Test1            Test2              Test3
AAA                50 (date1)      23 (date2)      98 (date4)
                       60 (date2)      24 (date4)
                       55 (date3)

I worked out a Crosstab Query, but it allows to show only the First or the
Last record.

-- 
Message posted via http://www.accessmonster.com

0
hongluen
3/22/2007 3:31:09 AM
access.queries 6343 articles. 1 followers. Follow

0 Replies
1079 Views

Similar Articles

[PageSpeed] 34

Reply:

Similar Artilces:

convert a numeric value into its equivalent
Were can I find a code to insert in my excel worksheet (Module) so I can convert a numeric value into its equivalent in Spanish words. For example, change $100.50 into "Cien dolares con cincuenta centavos". Any help will be most appreciated! sAYk Here http://longre.free.fr/downloads/temp/Ntexteng.zip You can find a download which does almost that, for many languages, including spanish. It does not include the "dolares". If you do a google advanced group search (http://groups.google.com/advanced_group_search), for "excel" groups, with the keywords text numbe...

Return Maximum value
Hi Looking to find a formula that will calculate a maximum bid figure from an array. Example data. Products Bidder Amount Product 1 John �50 Product 2 David �50 Product 1 William �55 Product 1 Jill �45 Product 3 Tom �60 Product 3 Gwen �30 So when I put s table together of all Product I get the highest bidders for each e.g. Products Bidder Amount Product 1 William �55 Product 2 David �50 Product 3 Tom �60 Any ideas? Thanks 1)List your products from E1:E3 2)Put this formula in Cell D1: =MAX(IF($A...

Assigning Calculated value to main report from subreport
I have a report with a subreport in it. I am trying to sum up the values of a txt-box [Extended Price] in the subreport and put the value in a txt-box, "TotalAmountIS" on the main report. This is what I did: 1.) I created a txtbox in the footer of the subreport and called it TotalAmount 2.) I assigned "=sum([Extended Price])" to the control source of TotalAmount 3.) I created a txtbox on the main report 4.) I then assigned "=Nz([subreportname]![TotalAmount],0)" to the control source of TotalAmountIS This was the same way I did it on a form, with ...

Append Query problem
I have an Append Quey that works fine when you right click on the query ("ReaderIndCancelProforma_Append") and choose open INSERT INTO Reader_DistrHistory ( DistrId, [Reader Id], [Date on mailing list], TypeId, ReasonId, Reason, [Date off mailing list] ) SELECT Reader_DistrCurrent.DistrId, Reader_DistrCurrent.[Reader Id], Reader_DistrCurrent.[Date on mailing list], 6 AS Type, 15 AS Reas, [Forms]![Reader_CancelProforma]![Remarks] AS Remark, Now() AS off FROM Reader_DistrCurrent WHERE (((Reader_DistrCurrent.[Reader Id])=[Forms]![Reader_DB]![Reader Id])); The problem I hav...

Error in query referring to Combo Box
I am very new to using VBA to build forms and am having a problem. I trying to embed a query within a public sub routine. The goal of this query is to use an alphanumeric code entered into a combo box on the form to retrieve an associated ID within a table. The bound column of the combo box is text. I have used the following code: Public Sub GetSturID(intSturID As Integer) Dim rsSturID As ADODB.Recordset Set rsSturID = New ADODB.Recordset rsSturID.Open "SELECT tblSturg.SturgID FROM tblSturg WHERE (tblSturg.PIT = '" & cmbPIT.Value & "')" _ &...

Query to count between list of number (Predicting Start/End that may occur in data range)
Hi, I have a below list of numbers. 566667 566668 566669 566665 566666 566671 566672 566680 I want a query that would return a count between start and end of range. Like Start End Quantity 566665 566669 5 566671 566672 2 566680 566680 1 Thank you. On 2 apr, 07:17, Angela <ims...@gmail.com> wrote: > Hi, > > I have a below list of numbers. > > 566667 > 566668 > 566669 > 566665 > 566666 > 566671 > 566672 > 566680 > > I want a query that would return a coun...

Worksheet protect with query-based table
I have a table with 3 columns based on a SQL db query, with a worksheet column added to the table that is a formula I want to protect. There are 3 other non-table cells I want to protect. If I protect the worksheet, I can't refresh the table, even if I unlocked the table cells that are based on the query. ...

Report or SQL Query from website
Let's say I have my own hosted website and domain with MySQL support (not sure if that matters here)... What I would like to do is pull real-time or near real-time data from my Store Ops or HQ Server database. What kind of solutions should I be looking for? If this is not possible, I was going to try a "roll-my-own" solution by scheduling SQL queries that output to text files and pushing the files to a web server via ftp using windows task scheduler. I would much prefer the "pull" solution to get real time data. I guess I would need to do some port forwarding o...

Query the top top ten by category?
I have a table that has a list of occurences by store locations. I use a query to count those occurences and then produce a top ten report. I am currently producing that report by market. In order to do this I have built independent queries for each market, which are the source for subreports, that are all on the same report. In effect I get one report with the top ten occurences by market. I now need to move to a regional report. The issue I have is that there are 70 regions. And I produce three unique reports. I only have 6 markets so I had no real issue producing 18 different...

find numbers in a range that add to a specific value
Jason previously answered the above question with the below answer however I am looking for a formula that works when adding more than 2 numbers together? Can anyone help? thanks, Brett Assuming your list of numbers are in A1:A50 and the total number is in F1, try this in B1 and copy down to B50: =ADDRESS(SUMPRODUCT(--(A1+$A$1:$A$50=$F$1),ROW(INDIRECT("1:50"))),1,4) If there are any feasible combinations, a cell reference will appear next to a number. That number, but the number in the cell reference, will equal the number in F1. HTH Jason Atlanta, GA If you have to check ...

Create a Query to pull a list of records from large DB
Table 1 has 1 field and 20 records (unique tracking nos.). Table 2 has 8 fields and 900+ records. Field 7 of Table 2 contains the unique tracking nos. -- 20 of which are found in and comprise Table 1. I want to do a query that will pull only the records from Table 2 (with all 8 fields) that match the 20 unique tracking numbers found in Table 1. Can anyone help? -- Mary Create a query that includes both tables. Join the field in Table 1 to Field 7 in Table 2. The query will return only the records you want. PC Datasheet Providing Customers A Resource For Help With Access, Excel And...

Need assistance with Detailsview and passing values to a textbox
Hi I have a Multiview with two views. Both views each have a detailsview control with several textbox's and AutoGenerateEditButton="True". During Update I am trying to insert a value to the product textbox based on the option chosen from a dropdown but am having problems. Could someone please help. The error is for Exception Details: System.NullReferenceException: Object reference not set to an instance of an object. Source Error: Line 545: DirectCast(DTLTrainUpdateVw.FindControl("txtTrainProduct"), TextBox).Text = product Line 546: I get t...

Displaying timestamps corresponding to min / max values
I have a table with temperature readings of different locations over a period of time. Thus multiple records per location. I have a query to show me the min and max temperatures for each location. I needed to add to that query the date when the min and the max temperatures were recorded. How could I do that? Many thanks in anticipation. This query returns two records (one for the minimum at the location and one for the maximum) SELECT YourTable.Location, Temperature IIF(YourTable.Temperature = YourCurrentQuery.MinTemperature, "Minimum","Maximum") as Which FROM You...

How to combine text and the value in another cell?
Excel 2007 Lets say in A2 I have a calculated value of 2 in A1 I want it to say The total of XX = 2 This is what I tried: =CONCATENATE(The Total of XX =,A2) Gave me a "value" error what other approach should I try? tia dave Try this: ="The total of xx = "&A2 -- HTH, RD ===================================================== Please keep all correspondence within the Group, so all may benefit! ===================================================== "Dave" <dave@accessdatapros> wrote in message news:20C0AC3C-2CF3-49BC-8837-428F60A42692@microsof...

Query Expression help
What is the correct expression to get average handle time per return, rather than per batch (returns are logged in batches that vary in quantity). Currently using this espression to get AHT per batch- Handle Time: [Sign In Time]-[Sign Out Time] I would like to divide this sum by [Processed], which is already an expression Processed: [Filed]+[Sent to Accounting]+[Pended] to ger AHT per return. I tried- Handle Time: sum([Sign In Time]-[Sign Out Time])/[Processed], but that doesn't work. Help! Perhaps the following, although I suspect that will not give you the results you expect. S...

Displaying null values in charts
I have made some charts with the data populating the underlying tables using Vlookups. However as they are populated over time i dont want to show any null values. I have tried the following formula =IF(D14=0,"",D14) to convert the 0 values in to a null value but it still shows it on the chart. Do you know how to get around this and if it is possible. Use NA() instead of "". -- Regards, Tushar Mehta www.tushar-mehta.com Excel, PowerPoint, and VBA add-ins, tutorials Custom MS Office productivity solutions In article <3F8AEA4E-968E-4E6F-B448-6A2A6F362C15@micros...

filter a table based on a query
What I really want to do is update a table when another table has been edited. I have a table of attendence, and another for registration that the table attendence is created from based on an Append Query. I managed to create a delete query for when course are cancelled to delete records in the attendence table, but when an individual cancels a course, I'm having trouble figuring out how to delete because this query and table are not linked directly. So I was thinking alternatively of writing a macro and applying a query to filter my table and then working out that all 'present' fi...

distinct values in column
I like to format a column such that it allows only distinct values. fo eg in the column A i have 10 numbers 1to 10 (A1 to A10) . if i ente any value between 1 and 10 in A11 it should not allow. A11,A12.....et should allow only distinct values. pls help me to do this thanks in advanc -- parthaemai ----------------------------------------------------------------------- parthaemail's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=3131 View this thread: http://www.excelforum.com/showthread.php?threadid=51928 Data > Validation > Allow whole number > Betw...

Total is sum of bold values
Suppose I have a the following columns in a Excel Worksheet :- A B C D 4 (b) 6 4 (b) 5 6 6 (b) 6 (b) 6 3 (b) 6 (b) 4 4 6 4 (b) 3 1 (b) -------------------------------------------------- SUM -------------------------------------------------- 7 16 10 1 The totals add only the values that are bold.. (b denotes bold text) How can I make this (Total/Bold) functionality to work in Excel ? See http://www.mvps.org/dmcritchie/excel/formula.htm#BoldSum -- Regards, Peo Sjoblom "Saj" <shissa@leics.gov.uk> wrote in message news:09ea01...

Monthly Report by SQL Query
How do you query in RMS database so that you can output monthly breakdown of Sales/Cost/Profit to Excel? I don't want to use Quickbooks for reports. It would look like: Jan Feb Mar Apr... --------------+-----+-----+-----+--- Sales 500 530 530 350 Cost 200 203 398 129 Profit 300................... Thanks! The following instructions are vague, but you will be able to figure it out. create a new datasource pointing to your RMS database. start excel and create a new pivot table using external datasource. follow "wizard" customize query in M...

Animation query
I'm building a program that animates a ball moving around the screen. The ball is going to be a simple object, probably not much more than a small white circle. I haven't tried it yet, but am just trying to guage opinion from people who have done this sort of thing on what would be the best method to start investigating. I am planning on kicking off a thread to handle the repainting of the ball in a different position, and then letting that thread Sleep for a certain amount of time between frames, the time it would Sleep for would be guaged by how fast the ball is moving. I ju...

Using Option Group to select a range of Queries
Hi there, I'm trying to set a form up to select a range of columns in which I'd like to search for a specific string using presaved queries. I'm thinking of having 10 text boxes, each displaying a single line of the received data. I was then going to have to columns of radio buttons to signify the start and end of the search. The user would then be able to select 'Start button' 3 and 'end button 5', which, after clicking on the 'go' button, would run the 'search column 3', 'search column 4' and 'search column 5' queries. If the Star...

XML updating,querying question #2
I use a TreeView WebControl Menu which i installed from microsoft. But the TreeView XML that can be loaded with the Webcontrol is very specific.For example (The root as to be TREENODES!!):- <?xml version="1.0" encoding="utf-8"?> <TREENODES> <TreeNode Text="Contracts"> <TreeNode Text="Contract Pads" NavigateUrl="a.aspx" target="_blank"/> <TreeNode Text="Points Deduction Report" NavigateUrl="a.aspx" target="_blank"/> <TreeNode Text="" NavigateUrl=...

open a message box if a query is null during an autoexec macro
Hope someone can help with this. I have a query that runs using an AutoExec macro when the database opens. What I want is for a message box to open instead of the query results window, telling me if the query produced any records. I can do this easily enough through a command button on a form, but cant figure out how to do it through an autoexec macro at startup. Any suggestions? You need to have your Autoexec macro run a VB function (macro action RunCode)that executes the query and captures the count of records retrieved. Dim strSQL as string Dim k as integer Dim rs a...

Get the max value of a column given the current date (this is hard!!!)
Given: a1: current date (ex: November 2, 2004) b2: 11/1/04 c2: 11/2/04 C3: 1 C4: 2 a2: Reject Definition a3: stain a4: dent How can I have the max value of a column having the current date and have its reject definition? Let's say I want to place the max value a b8 and its reject definition at a8... please anyone help me on this! this work is really making me crazy! need a computer wiz to get this work out!!!! waaah!!! -- Shadow_Otix ----------------------------------------------------------------------- Shadow_Otixz's Profile: http://www.excelforum.com/member.php?action=geti...