Multiple MS Access table sources for pivot table

Hi
I would like my pivot table to draw data from several tables in an Access
database. Although I have gone through the query wizard and added the fields
from all the database tables, I still find that when Excel reads the data
into the pivot table it stops after the first table and I only get part of
the source data I am looking for.

What am I missing in order to consolidate the data from several tables?

with thanks.

I don't think it is possible, you can use multiple excel sheets/tables
(although a lot of the functionality gets lost) but not multiple access
tables.

--
Regards,

Peo Sjoblom


"fbj" <fbj@discussions.microsoft.com> wrote in message
news:D7C893BE-76F6-4B3A-A813-E0986AD09FD0@microsoft.com...
> Hi
> I would like my pivot table to draw data from several tables in an Access
> database. Although I have gone through the query wizard and added the
> fields
> from all the database tables, I still find that when Excel reads the data
> into the pivot table it stops after the first table and I only get part of
> the source data I am looking for.
>
> What am I missing in order to consolidate the data from several tables?
>
> with thanks.

Oh well,

I guess I'll just combine the tables into one.

Thanks

"Peo Sjoblom" wrote:

> I don't think it is possible, you can use multiple excel sheets/tables
> (although a lot of the functionality gets lost) but not multiple access
> tables.
>
> --
> Regards,
>
> Peo Sjoblom
>
>
> "fbj" <fbj@discussions.microsoft.com> wrote in message
> news:D7C893BE-76F6-4B3A-A813-E0986AD09FD0@microsoft.com...
> > Hi
> > I would like my pivot table to draw data from several tables in an Access
> > database. Although I have gone through the query wizard and added the
> > fields
> > from all the database tables, I still find that when Excel reads the data
> > into the pivot table it stops after the first table and I only get part of
> > the source data I am looking for.
> >
> > What am I missing in order to consolidate the data from several tables?
> >
> > with thanks.
>
>

I have created a query in Access using multiple tables and then used the
Data-Get External Data option to connect the Excel spreadsheet to the Access
database query.

The multiple-table query will work if there are relationships in the
data/tables. I'm assuming the tables you are trying to access are in the
same Access database...

"fbj" wrote:

> Oh well,
>
> I guess I'll just combine the tables into one.
>
> Thanks
>
> "Peo Sjoblom" wrote:
>
> > I don't think it is possible, you can use multiple excel sheets/tables
> > (although a lot of the functionality gets lost) but not multiple access
> > tables.
> >
> > --
> > Regards,
> >
> > Peo Sjoblom
> >
> >
> > "fbj" <fbj@discussions.microsoft.com> wrote in message
> > news:D7C893BE-76F6-4B3A-A813-E0986AD09FD0@microsoft.com...
> > > Hi
> > > I would like my pivot table to draw data from several tables in an Access
> > > database. Although I have gone through the query wizard and added the
> > > fields
> > > from all the database tables, I still find that when Excel reads the data
> > > into the pivot table it stops after the first table and I only get part of
> > > the source data I am looking for.
> > >
> > > What am I missing in order to consolidate the data from several tables?
> > >
> > > with thanks.
> >
> >

If you are trying to append several tables together, try this:

In the Get Data phase of the Pivot Table process:
1)Select any one table from the MS Access database.
2)At the end of the process opt to Edit the Query
3)Click the SQL button
4)Rework the SQL code to return the fields you want from each table by using
the UNION ALL statement.

In my testing, I had copied and renamed the same table in a financial
application that I built some time ago: LU_ExpAcct==> LU_ExpAcct1, LU_ExpAcct2
(I used 2 tables, but this works for any number of similar tables)

This is the structure of the SQL code for combining multiple tables:

SELECT Account, Name, RefNum
FROM (
SELECT LU_ExpAcct1.Exp_AcctNum as Account, LU_ExpAcct1.Exp_ShortName as
Name, LU_ExpAcct1.ID as RefNum
FROM `C:\Excel Stuff\Sandbox`.LU_ExpAcct1 LU_ExpAcct1
UNION ALL
SELECT LU_ExpAcct2.Exp_AcctNum as Account, LU_ExpAcct2.Exp_ShortName as
Name, LU_ExpAcct2.ID as RefNum
FROM `C:\Excel Stuff\Sandbox`.LU_ExpAcct2 LU_ExpAcct2
) as Combo

Return the data, to Excel and your pivot should contain the selected fields
from the combined tables.

Does that help?
--
Regards,
Ron

In Access, you can choose pivot table view for any form, table, OR QUERY.
Choosing pivot table view for a query gives you a huge amount of power for
analysis right in Access itself, without having to go to Excel. I don't find
pivot table view of either a form or a table very useful at all.

Microsoft could definitely have made these puppies easier to get into. The
documentation I've found on them is virtually useless. As I noted elsewhere,
I've got an article scheduled for the September issue of the Access Advisor
on how to put them to good use.

Kevin

"fbj" wrote:

> Hi
> I would like my pivot table to draw data from several tables in an Access
> database. Although I have gone through the query wizard and added the fields
> from all the database tables, I still find that when Excel reads the data
> into the pivot table it stops after the first table and I only get part of
> the source data I am looking for.
>
> What am I missing in order to consolidate the data from several tables?
>
> with thanks.