Updating Crosstab Query?

[Guest]

Hello,

I have built a good crosstab query that dispays sales by customer by
quarter. Lets call it "History/Forecast". Basically is comes up with 200
records of:

Customer Name Q1sales Q2sales Q3fcst Q4fcst
Cust 1 100 100 100 100
Cust 2 200 300 300 300

What I would like to do is provide a way for the person running the query to
change the values in the Q3fcst and Q4fcst fields for each record, and have
those changes update either the source table or create a new table. I notice
that generally a query does not allow you to edit the cells.

Any thoughts? If someone can point me in the right direction I can do
additional research with help materials, etc...

Thanks. Ari

 

[Rick Brandt]

Hello,

I have built a good crosstab query that dispays sales by customer by
quarter. Lets call it "History/Forecast". Basically is comes up with 200
records of:

Customer Name Q1sales Q2sales Q3fcst Q4fcst
Cust 1 100 100 100 100
Cust 2 200 300 300 300

What I would like to do is provide a way for the person running the query to
change the values in the Q3fcst and Q4fcst fields for each record, and have
those changes update either the source table or create a new table. I notice
that generally a query does not allow you to edit the cells.

Any thoughts? If someone can point me in the right direction I can do
additional research with help materials, etc...

A crossstab "cell" as you put it is the result of aggregating multiple records
and returning either the count, min, max, sum, etc.. Since each result is
derived from multiple rows in the source table(s) there is no way for an edit to
take place. Which rows would be changed and how?

 

[Guest]

Great point about the query result being the sum of a number of records. I
hadn't thought about that in my question. However, I wouldn't need the new
number to change the source table (as you point out impossible as it would
have to allocate over many records). Rather, the altered (and unaltered)
query records would upload to a separate table, creating new records there.
In that table the fields would be the same as the query - "customer name",
"Q1 Sales", "Q2 Sales" "Q3 Fcst" "Q4 Fcst".

In other words, I want to run a cross-tab query from table 1, edit some of
the data, and upload the records to table 2. I would want to be able to add
records to table 2 multiple times from different queries, though they would
all have the same field structure as the example query below.

I really appreciate any thoughts you have.

Best,

Ari Blum