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:
[quote]
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

Just to clarify you have Mother.mdb as your back end database (tables only).
You have Daughter.mdb as your front end (queries, forms, reports and such).
You have a query in Daughter to filter/aggregate as needed. You have hooked
the Pivot Table directly to the query. Assuming that to be the case you have
an excellent structure.

Your query indicates to me that you are grouping your data which may or may
not be necessary as a pivot table can handle hundreds of thousands of rows.

Should you put the query directly into the pivot table? That depends on
whether you will be deploying this to external users.

If all users can not access your front end then you will want to avoid
linking the pivot to daughter. In that case you need to link back to mother.
If you do not want to put any queries in mother (keep it as a true back end)
then you need to put the query in the pivot table.

If on the other hand this is not going out to the world and any users will
be able to access daughter then don't put the query in the table. The query
will be much easier to maintain in access.

Final point... If you are sending this out to the world and you can
reasonably forsee the need to modify the query at any point then you will
want to have it linked to a centrally stored query in mother. If the query is
not centrally stored and you need to make a change then you need to change
all of the end user systems...
--
HTH...

Jim Thomlinson


"Herrie" wrote:

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:
[quote]
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

Jim,

Thanks for your early reply.

For you info, and for other readers' as well.
'Mother.mdb' is the Backend. It is provided by the program, and puts a
partial copy of the whole database on my memory stick, hence updates the
'mdb' on my HD.
'Daughter.mdb' is my private way of dealing with my needs. It might be
possible that colleagues might get interested and want 'their' copy. They
also complain about the original program's 'flaws'.
Placinf the query directly in 'mother.mdb' would be impossible, since it is
a partial, 'temporary' replica of the original database in school.
Only the few 'automation fans' know their way around, so it would cause many
a disaster to have others 'fool around' in 'mother.mdb'.
Another problem is that most 'backend' users have no clue about Access, so
it would be preferable to have the query in Excel's pivot table.
The minor changes, which, as far as I can see, will only be the use of the
correct abbreviaton/name of the teacher. This would filter out his classes.

For the time being it will do as it is, since I am the only 'idiot' who has
some knowledge....
I was a trainer in applications for 10 years, due to recession, my job as
such ended and I'm back at school teaching Englis and business economisc
(kids aged 14 to 17).

Thanks for your thourough reply. I would, of course, be interested how to
build a query with the same result in Excel's Pivot Table.

YT

Herrie



"Jim Thomlinson" wrote:

Just to clarify you have Mother.mdb as your back end database (tables only).
You have Daughter.mdb as your front end (queries, forms, reports and such).
You have a query in Daughter to filter/aggregate as needed. You have hooked
the Pivot Table directly to the query. Assuming that to be the case you have
an excellent structure.

Your query indicates to me that you are grouping your data which may or may
not be necessary as a pivot table can handle hundreds of thousands of rows.

Should you put the query directly into the pivot table? That depends on
whether you will be deploying this to external users.

If all users can not access your front end then you will want to avoid
linking the pivot to daughter. In that case you need to link back to mother.
If you do not want to put any queries in mother (keep it as a true back end)
then you need to put the query in the pivot table.

If on the other hand this is not going out to the world and any users will
be able to access daughter then don't put the query in the table. The query
will be much easier to maintain in access.

Final point... If you are sending this out to the world and you can
reasonably forsee the need to modify the query at any point then you will
want to have it linked to a centrally stored query in mother. If the query is
not centrally stored and you need to make a change then you need to change
all of the end user systems...
--
HTH...

Jim Thomlinson


"Herrie" wrote:
CUT