H
Herrie
L.S.,
Intro.
In our school we use an Access database (mother.mdb) to store scores (notes)
from our students in.
Since the program that works with this database does not do wat I want it to
do, I built an Excel file, in which I created sheets per class.
At the moment I retrieve the data from 'mother.mdb' through a selfmade
'daughter.mdb' in which I built a 'Group By' query based on linked tables
from the 'mother.mdb'
The records contain a unique number for each student, as well as info to
which class they belong.
Using the 'daughter' query works in a pivot table works as it should. I get
all students in a Colum end their 'marks' in rows.
The Vertical Lookup function in Excel puts the data neatly where they belong.
My questions:
A) Is my method (through an intermediate 'daughter') the best?
B) Does it make sense to use the 'SQL' statement from Access 'daughter' in
Excel's "get External Data" Pivot table?
C) if so, how should it be rewritten?
Copy from SQL in Access:
Intro.
In our school we use an Access database (mother.mdb) to store scores (notes)
from our students in.
Since the program that works with this database does not do wat I want it to
do, I built an Excel file, in which I created sheets per class.
At the moment I retrieve the data from 'mother.mdb' through a selfmade
'daughter.mdb' in which I built a 'Group By' query based on linked tables
from the 'mother.mdb'
The records contain a unique number for each student, as well as info to
which class they belong.
Using the 'daughter' query works in a pivot table works as it should. I get
all students in a Colum end their 'marks' in rows.
The Vertical Lookup function in Excel puts the data neatly where they belong.
My questions:
A) Is my method (through an intermediate 'daughter') the best?
B) Does it make sense to use the 'SQL' statement from Access 'daughter' in
Excel's "get External Data" Pivot table?
C) if so, how should it be rewritten?
Copy from SQL in Access:
SELECT DISTINCTROW L.MENTOR, L.STAMKLAS, L.LLNR, L.ANAAM, L.RNAAM,
piTOETSCIJFER.CIJFER, piCIJFERKOLOM.PCS, piCIJFERKOLOM.KORT,
piCIJFERKOLOM.LANG, piCIJFERKOLOM.WEGING
FROM L INNER JOIN ((piCIJFERKOLOM INNER JOIN piGVD_CIJFKOL ON
(piCIJFERKOLOM.ID_CIJFKOL = piGVD_CIJFKOL.ID_CIJFKOL) AND
(piCIJFERKOLOM.DB_CIJFKOL = piGVD_CIJFKOL.DB_CIJFKOL)) INNER JOIN
piTOETSCIJFER ON (piGVD_CIJFKOL.ID_GVDCIJFKOL = piTOETSCIJFER.ID_GVDCIJFKOL)
AND (piGVD_CIJFKOL.DB_GVDCIJFKOL = piTOETSCIJFER.DB_GVDCIJFKOL)) ON L.LLNR =
piTOETSCIJFER.LLNR
GROUP BY L.MENTOR, L.STAMKLAS, L.LLNR, L.ANAAM, L.RNAAM,
piTOETSCIJFER.CIJFER, piCIJFERKOLOM.PCS, piCIJFERKOLOM.KORT,
piCIJFERKOLOM.LANG, piCIJFERKOLOM.WEGING, piTOETSCIJFER.ID_GVDCIJFKOL,
piTOETSCIJFER.MUTATIEDAT, piTOETSCIJFER.DB_GVDCIJFKOL,
piGVD_CIJFKOL.ALLEEN_LEZEN, piCIJFERKOLOM.ID_CIJFKOL
HAVING (((piTOETSCIJFER.MUTATIEDAT)>#8/1/2007#) AND
((piGVD_CIJFKOL.ALLEEN_LEZEN)="N"))
ORDER BY L.STAMKLAS, L.LLNR, piCIJFERKOLOM.PCS, piCIJFERKOLOM.KORT;
[\quote]
I'm looking foorward to comments and help
Y.T.
Harry Betlem
AKA
Herrie