Extract & Compress data to report

[hongluen via AccessMonster.com]

I am stuck at this. Please help.
I have a list of records of test results in datatable (Null meaning not taken
the test):

Student Name Date Test_1 Test_2 Test_3
John 05-May-05 50
John 06-Jun-06
John 07-Jul-06 75
John 08-Jan-07 65

Need to report in this format:
Student Name Test_1 Test_2 Test_3
John "Not tested" 05-May-05, 50 07-Jul-06, 75
08-Jan-07, 65

Don't know how to ignore all "Null" fields. All suggestions are welcome.
Thank you very much.

 

[Allen Browne]

You really need to use a relational table design here.
You can then solve the problem with a crosstab query.

Student table (one record for each student), with fields:
StudentID AutoNumber primary key
Surname Text
FirstName Text
...

Test table (one record for each test), with fields:
TestID primary key
...

StudentTest table (one record for each time a student sits a test):
StudentTestID AutoNumber primary key
StudentID Number Relates to Student.StudentID
TestID Number Relates to Test.TestID
TestDate Date/Time When this student took this test.
Result Number The student's result in the test.

The interface will be a main form bound to the Student table, with a subform
bound to the StudentTest table. You add another row in the subform each time
the student sits a test.

With that structure, you can create a crosstab query (Crosstab on Query
menu, in query design), where you have:
- StudentID as a Row Heading
- TestID as a Column Heading
- This expression as the Value (with First in the Total row):
Format([TestDate], "dd\-mmm\-yy\, ") & [Result]

 

[hongluen via AccessMonster.com]

Dear Allen,
My problem is that I might have created a bad database structure of my
datatables.
Table 1. Student Table
Student ID
Student Name
etc

Table 2. Test Results Table
Student ID
Date (test date)
Test_1 (result of Test_1)
Test_2 (result of Test_2)
Test_3 (result of Test_3)

Can I still use Crosstab Query as you suggested?

You really need to use a relational table design here.
You can then solve the problem with a crosstab query.

Student table (one record for each student), with fields:
StudentID AutoNumber primary key
Surname Text
FirstName Text
...

Test table (one record for each test), with fields:
TestID primary key
...

StudentTest table (one record for each time a student sits a test):
StudentTestID AutoNumber primary key
StudentID Number Relates to Student.StudentID
TestID Number Relates to Test.TestID
TestDate Date/Time When this student took this test.
Result Number The student's result in the test.

The interface will be a main form bound to the Student table, with a subform
bound to the StudentTest table. You add another row in the subform each time
the student sits a test.

With that structure, you can create a crosstab query (Crosstab on Query
menu, in query design), where you have:
- StudentID as a Row Heading
- TestID as a Column Heading
- This expression as the Value (with First in the Total row):
Format([TestDate], "dd\-mmm\-yy\, ") & [Result]

I am stuck at this. Please help.
I have a list of records of test results in datatable (Null meaning not

[quoted text clipped - 14 lines]

Don't know how to ignore all "Null" fields. All suggestions are welcome.
Thank you very much.

 

[Allen Browne]

If you can't fix the structure, you may be able to fudge it with a UNION
query such as this:

SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
1 AS TestID
Test.[Test_1] AS Result
FROM Test
WHERE Test.[Test_1] Is Not Null
UNION ALL
SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
2 AS TestID
Test.[Test_2] AS Result
FROM Test
WHERE Test.[Test_2] Is Not Null
UNION ALL
SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
3 AS TestID
Test.[Test_3] AS Result
FROM Test
WHERE Test.[Test_3] Is Not Null

Save that query, and use it as the source "table" to build your crosstab
query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Dear Allen,
My problem is that I might have created a bad database structure of my
datatables.
Table 1. Student Table
Student ID
Student Name
etc

Table 2. Test Results Table
Student ID
Date (test date)
Test_1 (result of Test_1)
Test_2 (result of Test_2)
Test_3 (result of Test_3)

Can I still use Crosstab Query as you suggested?

You really need to use a relational table design here.
You can then solve the problem with a crosstab query.

Student table (one record for each student), with fields:
StudentID AutoNumber primary key
Surname Text
FirstName Text
...

Test table (one record for each test), with fields:
TestID primary key
...

StudentTest table (one record for each time a student sits a test):
StudentTestID AutoNumber primary key
StudentID Number Relates to Student.StudentID
TestID Number Relates to Test.TestID
TestDate Date/Time When this student took this test.
Result Number The student's result in the test.

The interface will be a main form bound to the Student table, with a
subform
bound to the StudentTest table. You add another row in the subform each
time
the student sits a test.

With that structure, you can create a crosstab query (Crosstab on Query
menu, in query design), where you have:
- StudentID as a Row Heading
- TestID as a Column Heading
- This expression as the Value (with First in the Total row):
Format([TestDate], "dd\-mmm\-yy\, ") & [Result]

I am stuck at this. Please help.
I have a list of records of test results in datatable (Null meaning not

[quoted text clipped - 14 lines]

Don't know how to ignore all "Null" fields. All suggestions are welcome.
Thank you very much.

 

[hongluen via AccessMonster.com]

After UNION ALL to get a "new" source table,
shall I use Concatenate function instead of Crosstab Query?
Your advice is greatly appreciated.

If you can't fix the structure, you may be able to fudge it with a UNION
query such as this:

SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
1 AS TestID
Test.[Test_1] AS Result
FROM Test
WHERE Test.[Test_1] Is Not Null
UNION ALL
SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
2 AS TestID
Test.[Test_2] AS Result
FROM Test
WHERE Test.[Test_2] Is Not Null
UNION ALL
SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
3 AS TestID
Test.[Test_3] AS Result
FROM Test
WHERE Test.[Test_3] Is Not Null

Save that query, and use it as the source "table" to build your crosstab
query.

Dear Allen,
My problem is that I might have created a bad database structure of my

[quoted text clipped - 51 lines]

 

[hongluen via AccessMonster.com]

In the Crosstab query, I need to show all but not just the First record.
i.e. if a student took the same Test on 3 different dates, there should be 3
result_dates
instead of only the first or the last.
Please advise on how to show all in the Crosstab query. Thank you very much.

If you can't fix the structure, you may be able to fudge it with a UNION
query such as this:

SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
1 AS TestID
Test.[Test_1] AS Result
FROM Test
WHERE Test.[Test_1] Is Not Null
UNION ALL
SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
2 AS TestID
Test.[Test_2] AS Result
FROM Test
WHERE Test.[Test_2] Is Not Null
UNION ALL
SELECT Test.[Student ID] AS StudentID
Test.[Date] As TestDate
3 AS TestID
Test.[Test_3] AS Result
FROM Test
WHERE Test.[Test_3] Is Not Null

Save that query, and use it as the source "table" to build your crosstab
query.

Dear Allen,
My problem is that I might have created a bad database structure of my

[quoted text clipped - 51 lines]

 

[SYChua]

hi,

Try the isnull function. Example

if isnull(test_1)=true then "skip"