Condense data in report

[hongluen via AccessMonster.com]

Hi,

I have created a report in this format:
Name Test_1 Test_2 Test_3
AAA result2_date1
AAA result1_date2
AAA result3_date3

How can I condense this into:
Name Test_1 Test_2 Test_3
AAA result1_date2 result2_date1 result3_date3

Thank you very much.

 

[tina]

try using a Crosstab query. there is a Crosstab option in the Query Wizard
that will walk you through it.

hth

 

[hongluen via AccessMonster.com]

Hi Tina,
Because different students took different tests on different dates,
if I use crosstab query, there will generate many columns of Test_1 of many
different dates.
I would like to make "Test_1" in one single column, such that the
report/query looks like this:

Name Test_1 Test_2
AAA result1_date1 result2_date2
result2_date3
BBB result1_date2 result2_date4
CCC (not tested) result2_date5

Thank you for your help again.

try using a Crosstab query. there is a Crosstab option in the Query Wizard
that will walk you through it.

hth

Hi,

[quoted text clipped - 3 lines]

AAA result1_date2
AAA result3_date3

How can I condense this into:
Name Test_1 Test_2 Test_3
AAA result1_date2 result2_date1 result3_date3

Thank you very much.

 

[tina]

try creating a query for each test field - one query for Test_1, another
query for Test_2, etc, including the foreign key field that identifies the
student, in each query. then use those queries in another query, including
the students table and using a LEFT JOIN to link the students table to each
test query, on the student identifier field. then pull the student name
fields from the student table and the test field from each test query into
the design grid.

hth

Hi Tina,
Because different students took different tests on different dates,
if I use crosstab query, there will generate many columns of Test_1 of many
different dates.
I would like to make "Test_1" in one single column, such that the
report/query looks like this:

Name Test_1 Test_2
AAA result1_date1 result2_date2
result2_date3
BBB result1_date2 result2_date4
CCC (not tested) result2_date5

Thank you for your help again.

try using a Crosstab query. there is a Crosstab option in the Query Wizard
that will walk you through it.

hth

Hi,

[quoted text clipped - 3 lines]

AAA result1_date2
AAA result3_date3

How can I condense this into:
Name Test_1 Test_2 Test_3
AAA result1_date2 result2_date1 result3_date3

Thank you very much.

 

[hongluen via AccessMonster.com]

Yes, thank you for your advice.
I can do the LEFT JOIN and generated a new list of records in the join query.
But, when a student took more than one (say, 3 times) of Test1 on different
dates,
the query generates 3 records, and then, this in turn leads to duplicate
records of Test2 and so on for Test3, Test4, etc.

After I query for Test1:
Name Test1 Test2
AAA 50_date1
60_date2
55_date3

Next, after I JOIN also the Test2, it becomes:
Name Test1 Test2
AAA 50_date1 70_date4
60_date2 70_date4
55_date3 70_date4

Then, for Test3, I will get:
Name Test1 Test2 Test3
AAA 50_date1 70_date4 30_date5
60_date2 70_date4 30_date5
55_date3 70_date4 30_date5
50_date1 70_date4 35_date6
60_date2 70_date4 35_date6
55_date3 70_date4 35_date6

How can I get rid of the duplicates?
Thank you again.

try creating a query for each test field - one query for Test_1, another
query for Test_2, etc, including the foreign key field that identifies the
student, in each query. then use those queries in another query, including
the students table and using a LEFT JOIN to link the students table to each
test query, on the student identifier field. then pull the student name
fields from the student table and the test field from each test query into
the design grid.

hth

Hi Tina,
Because different students took different tests on different dates,

[quoted text clipped - 28 lines]

 

[tina]

well, you're right, you'll get repeating values that way. at the report
level, you can set the date controls to HideDuplicates = Yes, but i'm afraid
that won't completely solve your problem. if studentA's last test date
record is the same as the next student's first test date record, the next
student's test date will be hidden - definitely not what you want.

i'm idea-ed out, hon, sorry. suggest you repost your question, perhaps
cross-posting (*don't* multi-post) to the queries newsgroup as well. there
are a number of expert developers here who can probably help you work out a
solution. good luck with it.

hth

Yes, thank you for your advice.
I can do the LEFT JOIN and generated a new list of records in the join query.
But, when a student took more than one (say, 3 times) of Test1 on different
dates,
the query generates 3 records, and then, this in turn leads to duplicate
records of Test2 and so on for Test3, Test4, etc.

After I query for Test1:
Name Test1 Test2
AAA 50_date1
60_date2
55_date3

Next, after I JOIN also the Test2, it becomes:
Name Test1 Test2
AAA 50_date1 70_date4
60_date2 70_date4
55_date3 70_date4

Then, for Test3, I will get:
Name Test1 Test2 Test3
AAA 50_date1 70_date4 30_date5
60_date2 70_date4 30_date5
55_date3 70_date4 30_date5
50_date1 70_date4 35_date6
60_date2 70_date4 35_date6
55_date3 70_date4 35_date6

How can I get rid of the duplicates?
Thank you again.

try creating a query for each test field - one query for Test_1, another
query for Test_2, etc, including the foreign key field that identifies the
student, in each query. then use those queries in another query, including
the students table and using a LEFT JOIN to link the students table to each
test query, on the student identifier field. then pull the student name
fields from the student table and the test field from each test query into
the design grid.

hth

Hi Tina,
Because different students took different tests on different dates,

[quoted text clipped - 28 lines]

Thank you very much.

 

[hongluen via AccessMonster.com]

Thanks, Tina.
I have also tried to HideDuplicates at the Report.
And, it resolves some, but not all.
If the repeated date is immediately below, the HideDuplicate control works.
However, if it is not right below, this HideDuplicate does not work.
Thank you, anyway.
Hong

well, you're right, you'll get repeating values that way. at the report
level, you can set the date controls to HideDuplicates = Yes, but i'm afraid
that won't completely solve your problem. if studentA's last test date
record is the same as the next student's first test date record, the next
student's test date will be hidden - definitely not what you want.

i'm idea-ed out, hon, sorry. suggest you repost your question, perhaps
cross-posting (*don't* multi-post) to the queries newsgroup as well. there
are a number of expert developers here who can probably help you work out a
solution. good luck with it.

hth

Yes, thank you for your advice.
I can do the LEFT JOIN and generated a new list of records in the join query.

[quoted text clipped - 42 lines]

 

[hongluen via AccessMonster.com]

Do you think I can use Concatenate function?

Thanks, Tina.
I have also tried to HideDuplicates at the Report.
And, it resolves some, but not all.
If the repeated date is immediately below, the HideDuplicate control works.
However, if it is not right below, this HideDuplicate does not work.
Thank you, anyway.
Hong

well, you're right, you'll get repeating values that way. at the report
level, you can set the date controls to HideDuplicates = Yes, but i'm afraid

[quoted text clipped - 14 lines]