Not First, Not Last in Crosstab Query

  • Thread starter Thread starter hongluen via AccessMonster.com
  • Start date Start date
H

hongluen via AccessMonster.com

Hi all,
Can we get all the records instead of "First" of or "Last" of records that
meets the query criteria?
Just want to show all records. Thanks.
 
If you want all the records to show in a Crosstab, put the primary key
in the RowHeading and choose First so it, presumeably, executes faster ;)

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Dear Crystal,
Sorry that I cannot follow. Please elaborate.
I don't quite understand where to add the primary key in Crosstab query.
I only know that we can add primary key in design view of data tables.
Thank you again.
Have a nice day.
Hong
 
Dear Crystal,
Please kindly advise what the other parameters should be.
e.g. in Total Row, should it be Expression or Where
in Crosstab, should it be Value?
Thank you.
Hong
 
Hi Hong,

please tell us the fieldnames you are using and we can guide you better

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
 
Place the field that is used as primary key in the GROUP BY clause, so all
records will be displayed, each group made of just one record. (You do not
create ANOTHER primary key field, you USE the one already created).


Vanderghast, Access MVP
 
.... and to add to what Michael said ... it will be a RowHeading once you
have set the query up to be a Crosstab

from the menu --> Query, Crosstab Query

you will need at least one Row Heading, Column Heading, and Value in the
Crosstab cell

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*



Michel said:
Place the field that is used as primary key in the GROUP BY clause, so all
records will be displayed, each group made of just one record. (You do not
create ANOTHER primary key field, you USE the one already created).


Vanderghast, Access MVP
Click to expand...
 
Hi Crystal,
I want to have something like this in the report:

Student Name Test 1 Test 2
AAA 40, 15-Nov-06
50, 20-Jan-07

BBB 39, 30-Jan-07 94, 23-Nov-05
38, 19-Feb-07

Therefore,
Row Heading is Student Name
Column Heading is TestID
Data is test results & dates (varying, some students have not taken any, some
have taken several times). That is also why I want to show all records in the
data area rather than just the First or the Last.

Hong
Hi Hong,

please tell us the fieldnames you are using and we can guide you better

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Dear Crystal,
Please kindly advise what the other parameters should be.
Click to expand...
[quoted text clipped - 20 lines]
Click to expand...
 
Hello Hong,

you can use 2 crosstabs...

make a query that gives you all students and all tests. Here is some
logic, hopefully you can get the general idea...

QueryName --> qryAllStudentTests
SELECT TestID, StudentID FROM Students, Tests

for the 2 crosstabs -- use qryAllStudentTests, Students, Tests, StudentTests

use LEFT JOIN so all records are reported from qryAllStudentTests

same row and column headings for each...
---

field --> StudentID
table -->qryAllStudentTests
total --> Group By
Crosstab --> RowHeading

field --> StudentName
table -->qryAllStudentTests
total --> Group By
Crosstab --> RowHeading

field --> TestID
table -->qryAllStudentTests
total --> Group By
Crosstab --> RowHeading

field --> TestName -- or TestNumber -- didn't quite understand what you want
total --> Group By
Crosstab --> RowHeading

-----

first crosstab:

field --> TestScore
total --> First
Crosstab --> Value

-----

second crosstab:

field --> TestDate
total --> First
Crosstab --> Value

~~~~~~~~~~~~~~

now, make a query that joins your 2 crosstabs as well as other info you
may need from other tables

~~~~~~~~~~~

then, make a report based on this final query and suppress duplicate
values in student name

~~~

I will say that what you are trying to do is not easy -- and you will
have to modify your report design as you add more tests


Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*


Hi Crystal,
I want to have something like this in the report:

Student Name Test 1 Test 2
AAA 40, 15-Nov-06
50, 20-Jan-07

BBB 39, 30-Jan-07 94, 23-Nov-05
38, 19-Feb-07

Therefore,
Row Heading is Student Name
Column Heading is TestID
Data is test results & dates (varying, some students have not taken any, some
have taken several times). That is also why I want to show all records in the
data area rather than just the First or the Last.

Hong
Hi Hong,

please tell us the fieldnames you are using and we can guide you better

Warm Regards,
Crystal
*
(: have an awesome day :)
*
MVP Access
Remote Programming and Training
strive4peace2006 at yahoo.com
*
Dear Crystal,
Please kindly advise what the other parameters should be.
Click to expand...
[quoted text clipped - 20 lines]
meets the query criteria?
Just want to show all records. Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top