Can't Update table from Crosstab Data? WHY not?

  • Thread starter Thread starter fred
  • Start date Start date
F

fred

OK. I want to update table A from datasource B. Datasource B will
have several different related records for every A record, corrosponding
to a different field in A. If I run a crosstab on B, I get a perfect
list, corrosponding perfectly to the fields in A.

If I try to update A based on this crosstab, I get an error that the
query is not updateable.

If I take the contents of the crosstab, copy it to a spreadsheet and
import it back in as a table, I get a table that looks EXACTLY like the
original crosstab, but I can update table A just fine using it.

What gives? Any way around this? The extra steps are not going to cut
it...

Thanx
 
But I am not updating any table connected to the crosstab query...If I make a
query of the crosstab query, that in and of itslef has no aggregation, would
that get around it?
 
fred said:
But I am not updating any table connected to the crosstab query...If I make a
query of the crosstab query, that in and of itslef has no aggregation, would
that get around it?
Click to expand...

fred,

I am sorry, but it would not. No amount of indirection stacking on top of
the Query with Aggregation will solve the problem (or at least, none that
I've tried). Access has been (don't choke on this one!) *designed* to stop
UPDATES this way. If you can find a way around it directly in SQL, I'd be
happy to hear it.

The usual way to get around it is to dump the results of the Aggregated
Query into a Temporary Table with a Make Table Query (SELECT INTO). Then
use the Temporary Table to do the UPDATE you need. Then delete the
Temporary Table. These actions can be controlled from VBA (or a Macro, as a
last resort).


Sincerely,

Chris O.
 
OK. I want to update table A from datasource B. Datasource B will
have several different related records for every A record, corrosponding
to a different field in A. If I run a crosstab on B, I get a perfect
list, corrosponding perfectly to the fields in A.

If I try to update A based on this crosstab, I get an error that the
query is not updateable.

If I take the contents of the crosstab, copy it to a spreadsheet and
import it back in as a table, I get a table that looks EXACTLY like the
original crosstab, but I can update table A just fine using it.

What gives? Any way around this? The extra steps are not going to cut
it...

Thanx
Click to expand...

Are you trying to *Update* existing records? If so you can't - any
aggregate query (such as a Crosstab) is ipso facto non-updateable.

However, if you're trying to *add new records* rather than updating
existing records, you can create an Append query based on a crosstab
query, and it will work.
 
Back
Top