Access Simpleton: making complex tables

[Michael]

Bit of an odd request here, but something we tried this year for our
school data analysis worked well, but took a long time because it was
done manually. I'm now trying to work out if we can do something
electronically to make it easier.

I want to start with a column set of data about pupils achievements in
the previous year again their achievement in the current year using
sublevels.

I want to turn that into a table of data which has last year's score as
the left-hand column, this year's score as the top row, and individual
pupils' names dropped in the correct box

I have put together a diagram to show what I'm hoping to start and end with:
http://tesfaq.atspace.com/images/dataquery.htm
and wonder if anyone can tell me:

a) Is it possible?
b) Is Access the right tool to do it in?
b) If so, how?


Fingers crossed!

Thanks!

 

[Rick Brandt]

Bit of an odd request here, but something we tried this year for our
school data analysis worked well, but took a long time because it was
done manually. I'm now trying to work out if we can do something
electronically to make it easier.

I want to start with a column set of data about pupils achievements in
the previous year again their achievement in the current year using
sublevels.

I want to turn that into a table of data which has last year's score
as the left-hand column, this year's score as the top row, and individual
pupils' names dropped in the correct box

I have put together a diagram to show what I'm hoping to start and
end with: http://tesfaq.atspace.com/images/dataquery.htm
and wonder if anyone can tell me:

a) Is it possible?
Yes.

b) Is Access the right tool to do it in?

No. What you have there is a spreadsheet, not a database table.

 

[BruceM]

You could use Access to keep track of the information, and you would have a
variety of ways to present the data, but the information would not be stored
in a single table. Rather, each entity (such as Students or Level) would be
in a separate table. The tables would be related to each other as needed.
For instance, there would be only one record in the Student table for each
student, but there could be any number of individual Level records for each
student.
Access can be very versatile and powerful, but there is a steep learning
curve. In the context of a volunteer newsgroup it is unlikely that somebody
is going to talk you through the entire process of database creation, but
there is a lot of information on the web, and some very good books. The
following links are among the many that may help get you started with the
concepts involved, and provide links to additional resources.
http://allenbrowne.com/tips.html
http://www.mvps.org/access/
http://www.mvps.org/access/resources/index.html

 

[John Spencer]

Yes it is possible.

You would use a crosstab query to get your results.
Assume that your field names are PupilID, Lvl2006, Lvl2007

The Crosstab Query would look something like the following.

TRANSFORM Count(PupilID) as CellCount
SELECT Lvl2006, Count(StudentID) as RowTotal
FROM YourTable
GROUP BY Lvl2006
PIVOT ON Lvl2007

A better table design might be one that had PupilID, YearLevel, and Level as
fields.


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..