How to create a query view, one person per line

[Elaine16025]

Hi every one, how can I make this happen?

I have a table like this:

Student Math English DateTaken
Lisa 90 92 10/01/06
Lisa 80 95 11/10/06
David 78 86 09/10/06
Mary 95 90 10/10/06
Mary 91 88 11/02/06

I need to create a query view, one person per line:

Student Math1 English1 Date1 Math2 English2
Date2
Lisa 90 92 10/01/06 80
95 11/10/06
David 78 86 09/10/06
Mary 95 90 10/10/06 91
88 11/02/06

Thanks in advance!

elaine

 

[David F Cox]

You can do this with a crosstab query, concatenating the fields together as
a value.

Row titles are names
column titles are "DateTaken English Math"
vales are [dateTaken] & " " & [English] & " " & [math]

you may want to format date as "yyyy mm dd"



Values are

 

[Elaine16025]

Thank you David, but why should I concatenate the fields? I need to
keep them separated, so I can count like how many are greater than 90
etc...

elaine

 

[David F Cox]

It's the "Give them what they ask for, then they'll tell you what they want"
rule.-<)
A crosstab only returns one value field.
You probably would be better constructing a report, but you can push the
crosstab route even further by building a query from sub-queries to include
all of the stats you want, and concatenating those in the crosstab too. I
have a weakness for pushing the technology, but it is often not the best
advised route.

Good luck.

 

[Guest]

Elaine:

What is the advantage in returning one row per student? The information
content is not increased as it simply presents the same data in a different
layout. You can achieve something similar in a report grouped by Student.

A more fundamental problem, however, is that your table design is flawed.
"English" and "Math" are data; they are values of an entity type Subjects.
In a relational database data should be stored as values at column positions
in rows in tables and in no other way. What you are doing is known in the
jargon as 'encoding data as column headings'.

A correct structure would mean decomposing the table into four tables,
Students, Subjects, Tests (the last would include a TestDate column) and a
fourth table which models the relationship type between the first three,
TestResults say, which would have columns such as StudentID, SubjectID and
TestID referencing the primary keys of the other three tables along with a
Score column.

With this normalized structure the integrity of the data is protected by
eliminating redundancy and thus reducing the possibility of update anomalies,
and querying the database becomes much simpler, particularly when aggregating
data to return values such as average scores.

Ken Sheridan
Stafford, England

 

[Elaine16025]

Thank you Ken! You are right, I should not go this way. Thanks for your
time!

Elaine