use Access SQL in Excel query

[Guest]

Hello,

I use an external database (access 2000) with quite a number of tables, in
which the marks of my students are placed.
I want to put these values in specific Excel sheets based on the classes the
students are in. I work with a Pivot Table, drawing the data from the Access
DB. The date in the Pivot Table are the reference to my classes. (euch
student has a number so I use the "Vertical Lookup" funtion to get the parks
in their specific columns in the class sheets.
So far no problem.

At the momenbt I use an 'intermediate' Access file (daughter.mdb),
containing the links to the specific tables in the 'mother.mdb'. I created a
'Group By" query in the 'daughter' which provides the data in Excel.

Is there a way to 'transform' this Access Query into the Excell query type?
Or is that of no use at all and is my method 'clever' and fast enough?

I tried to past the Access Query (SQL) into the Excel query, but it doesn't
work
The Access qry rubns like:

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'd appreiate your help and or advice.

Y.T.

Harry Betlem
Aka
Herrie

 

[Dorothy]

Hello,

I use an external database (access 2000) with quite a number of tables, in
which the marks of my students are placed.
I want to put these values in specific Excel sheets based on the classes the
students are in. I work with a Pivot Table, drawing the data from the Access
DB. The date in the Pivot Table are the reference to my classes. (euch
student has a number so I use the "Vertical Lookup" funtion to get the parks
in their specific columns in the class sheets.
So far no problem.

At the momenbt I use an 'intermediate' Access file (daughter.mdb),
containing the links to the specific tables in the 'mother.mdb'. I created a
'Group By" query in the 'daughter' which provides the data in Excel.

Is there a way to 'transform' this Access Query into the Excell query type?
Or is that of no use at all and is my method 'clever' and fast enough?

I tried to past the Access Query (SQL) into the Excel query, but it doesn't
work
The Access qry rubns like:

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'd appreiate your help and or advice.

Y.T.

Harry Betlem
Aka
Herrie

Hello there.

Have you tried using Access as the external data source for your pivot
tables in Excel? When you're going through the pivot table wizard, it
asks you where your data resides, you can choose external data source
there and then choose the table or query in Access that feeds the
pivot table.

Good luck!

Dorothy