query flat file data for most recent data

[Georgie]

Annually data is imported from a flat file with one record per student and
appended to the existing history.

I need to determine the value of the most recent value for each subject
areas (blanks are ignored). The 1's indicated met the standard in the subject
and 0's they did not.
Student A needs a report for Reading and Writing but not for Writing and
Science, Student B only needs a report for Science, Student C needs a report
in Writing, and Student D does not need a report.

Thank you for your help in advance.


Student Year Grade Reading Writing Math Science
A 2007-2008 7 0 1 0
A 2006-2007 6 1 1
A 2004-2005 5 0 1 1
A 2003-2004 4 1 0 0
A 2003-2004 3 1 0
B 2007-2008 7 1 1 1
B 2006-2007 6 0 0
B 2005-2006 5 0 0 0
C 2007-2008 7 1 0 1
C 2005-2006 5 1
C 2005-2006 4 0 0 0
D 2007-2008 8 1 1 1
D 2006-2007 7 1 1 0
D 2005-2006 6 1 0
D 2005-2006 5 1 0 0
D 2003-2004 4 0 0 0

 

[Allen Browne]

You can use a subquery to grab the most recent non-blank result for any
subject + student from the flat-file data, and match that to the student in
your main table.

This exampe assumes:
a) The flat file table is named tblFlat, and your main table is tblMain.

b) You have added an autonumber named ID to the flatfile (so it has a
primary key, which lets you distinguish between duplicates)

c) You create a query using tblMain (only), and type an expression like this
into the Field row in query design:
LastMath: (SELECT TOP 1 Math
FROM tblFlat
WHERE tblFlat.Student = tblMain.Student
AND tblFlat.Math Is Not Null
ORDER BY tblFlat.[Year], tblFlat.ID)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Hopefully the target here is to get this flat-file data into some correctly
normalized tables.

 

[Ken Sheridan]

Allen:

Correct me if I'm wrong, but unless I've misread things I don't think the
'flat file' comes into the query at all does it? It has 'one record per
student' so the TOP 1 per student would be the only row per student. It’s
just the 'history' table which is involved, as far as I can see, so that
needs an autonumber ID column adding first. The query would then go like
this:

SELECT DISTINCT Student,
(SELECT TOP 1 Reading
FROM StudentHistory AS SH2
WHERE SH2.Student = SH1.Student
AND Reading IS NOT NULL
ORDER BY [Year] DESC, ID)
AS LastReading,
(SELECT TOP 1 Writing
FROM StudentHistory AS SH2
WHERE SH2.Student = SH1.Student
AND Writing IS NOT NULL
ORDER BY [Year] DESC, ID)
AS LastWriting,
(SELECT TOP 1 Math
FROM StudentHistory AS SH2
WHERE SH2.Student = SH1.Student
AND Math IS NOT NULL
ORDER BY [Year] DESC, ID)
AS LastMath,
(SELECT TOP 1 Science
FROM StudentHistory AS SH2
WHERE SH2.Student = SH1.Student
AND Science IS NOT NULL
ORDER BY [Year] DESC, ID)
AS LastScience
FROM StudentHistory AS SH1;

Ken Sheridan
Stafford, England

You can use a subquery to grab the most recent non-blank result for any
subject + student from the flat-file data, and match that to the student in
your main table.

This exampe assumes:
a) The flat file table is named tblFlat, and your main table is tblMain.

b) You have added an autonumber named ID to the flatfile (so it has a
primary key, which lets you distinguish between duplicates)

c) You create a query using tblMain (only), and type an expression like this
into the Field row in query design:
LastMath: (SELECT TOP 1 Math
FROM tblFlat
WHERE tblFlat.Student = tblMain.Student
AND tblFlat.Math Is Not Null
ORDER BY tblFlat.[Year], tblFlat.ID)

If subqueries are new, here's an introduction:
http://allenbrowne.com/subquery-01.html

Hopefully the target here is to get this flat-file data into some correctly
normalized tables.

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

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

Annually data is imported from a flat file with one record per student and
appended to the existing history.

I need to determine the value of the most recent value for each subject
areas (blanks are ignored). The 1's indicated met the standard in the
subject
and 0's they did not.
Student A needs a report for Reading and Writing but not for Writing and
Science, Student B only needs a report for Science, Student C needs a
report
in Writing, and Student D does not need a report.

Thank you for your help in advance.


Student Year Grade Reading Writing Math Science
A 2007-2008 7 0 1 0
A 2006-2007 6 1 1
A 2004-2005 5 0 1 1
A 2003-2004 4 1 0 0
A 2003-2004 3 1 0
B 2007-2008 7 1 1 1
B 2006-2007 6 0 0
B 2005-2006 5 0 0 0
C 2007-2008 7 1 0 1
C 2005-2006 5 1
C 2005-2006 4 0 0 0
D 2007-2008 8 1 1 1
D 2006-2007 7 1 1 0
D 2005-2006 6 1 0
D 2005-2006 5 1 0 0
D 2003-2004 4 0 0 0

 

[Allen Browne]

Ken, if I have misunderstood what's needed, I'm sure your post will help, so
thanks.

Georgie, let us know if you are still stuck.