Access query refreshed in excel doesn't work right

  • Thread starter Thread starter Tim
  • Start date Start date
T

Tim

I brought an access crosstab query into excel. the
problem is, when in excel and I refresh the query, if a
column of data is no longer in the query results the query
will not refresh.

for example the original crosstab query which counts the
number of offices rated 1,2,3,4 or 5 returns the following

office rating 1 2 3 4 5

champ 22 18 1 2 5
orlando 12 11 4 23 1

now if the new data is updated and there are no "5" rated
offices the query in excel will not run. it is trying to
find a "5" rated office in Champ and Orlando. before the
update there were five 5 rated offices in Champ and one 5
rated office in orland.

it would seem to me that I should not have to edit the
query each time to get it to work.

also if there were no 5 rated offices the first time. the
refreshed query will not show new 5 rated offices.

got to be an easier way. feel free to email me if this
doesn't make sense
 
Add a clause to your Crosstab query that specifies the column values.

something like:

TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT OfficeRating In (1,2,3,4,5);
 
That solved 2 problems THANKS!!!
-----Original Message-----
Add a clause to your Crosstab query that specifies the column values.

something like:

TRANSFORM ...
SELECT ...
FROM ...
GROUP BY ...
PIVOT OfficeRating In (1,2,3,4,5);


.
Click to expand...
 
Back
Top