linked data and pivot reports

[Ricoy-Chicago]

I have an MS query that import certain records based on parameters entered by
the user. No problem here.
I created the report using the wizard, I selected the range based on the
parameters that I entered. The problem is that I do not know the possible
number of records, therefore the report may not include all of them (the
number of records varies depending on the parameters, it could 5 or it could
be 500 records). I removed the check mark for the [blank] box in the drop
down box in the reoprt. The problem is when the number of records is greater
thatn the initial numbers or rows that I selected when the report was created.

Question: Is there a way for Excel to select all the records given by the MS
query everytime the report is updated?

Thanx

 

[Debra Dalgleish]

When you import the data, a name is automatically created, e.g. Query
from MS Access Database. Use that name as the source for the pivot
table, and it will automatically adjust.

To change the source:
Right-click a cell in the pivot table, and choose PivotTable Wizard
Click the Back button
In the Range box, enter the sheet name and range name, e.g.:
Sheet1!Query_from_MS_Access_Database
Click Finish

I have an MS query that import certain records based on parameters entered by
the user. No problem here.
I created the report using the wizard, I selected the range based on the
parameters that I entered. The problem is that I do not know the possible
number of records, therefore the report may not include all of them (the
number of records varies depending on the parameters, it could 5 or it could
be 500 records). I removed the check mark for the [blank] box in the drop
down box in the reoprt. The problem is when the number of records is greater
thatn the initial numbers or rows that I selected when the report was created.

Question: Is there a way for Excel to select all the records given by the MS
query everytime the report is updated?

Thanx

 

[Ricoy-Chicago]

Dear Debra:
Thanx! I knew the answer was going to be easy. You made my day!

When you import the data, a name is automatically created, e.g. Query
from MS Access Database. Use that name as the source for the pivot
table, and it will automatically adjust.

To change the source:
Right-click a cell in the pivot table, and choose PivotTable Wizard
Click the Back button
In the Range box, enter the sheet name and range name, e.g.:
Sheet1!Query_from_MS_Access_Database
Click Finish

I have an MS query that import certain records based on parameters entered by
the user. No problem here.
I created the report using the wizard, I selected the range based on the
parameters that I entered. The problem is that I do not know the possible
number of records, therefore the report may not include all of them (the
number of records varies depending on the parameters, it could 5 or it could
be 500 records). I removed the check mark for the [blank] box in the drop
down box in the reoprt. The problem is when the number of records is greater
thatn the initial numbers or rows that I selected when the report was created.

Question: Is there a way for Excel to select all the records given by the MS
query everytime the report is updated?

Thanx

 

[Debra Dalgleish]

You're welcome! Thanks for letting me know that it helped.

Dear Debra:
Thanx! I knew the answer was going to be easy. You made my day!

:

When you import the data, a name is automatically created, e.g. Query
from MS Access Database. Use that name as the source for the pivot
table, and it will automatically adjust.

To change the source:
Right-click a cell in the pivot table, and choose PivotTable Wizard
Click the Back button
In the Range box, enter the sheet name and range name, e.g.:
Sheet1!Query_from_MS_Access_Database
Click Finish

I have an MS query that import certain records based on parameters entered by
the user. No problem here.
I created the report using the wizard, I selected the range based on the
parameters that I entered. The problem is that I do not know the possible
number of records, therefore the report may not include all of them (the
number of records varies depending on the parameters, it could 5 or it could
be 500 records). I removed the check mark for the [blank] box in the drop
down box in the reoprt. The problem is when the number of records is greater
thatn the initial numbers or rows that I selected when the report was created.

Question: Is there a way for Excel to select all the records given by the MS
query everytime the report is updated?

Thanx