Crosstab query as data source for update query

  • Thread starter Thread starter cmk7471
  • Start date Start date
C

cmk7471

I'm trying to use the results of a crosstab query to update values in another
table. I'm getting the error that the "operation must use an updateable
query".

This is my crosstab query to summarize labor hours by job number and
operation number:
TRANSFORM Sum([TBL-BLOCS-Labor].LABH_HOURS) AS SumOfLABH_HOURS
SELECT [TBL-BLOCS-Labor].LABH_JOB
FROM [TBL-BLOCS-Labor]
WHERE ((([TBL-BLOCS-Labor].LABH_DEPT)="53"))
GROUP BY [TBL-BLOCS-Labor].LABH_JOB
PIVOT Right([LABH_OPER],3);

This is the update query where I'm trying to update TBL-JobActuals with the
figures as summarized by the crosstab query:
UPDATE DISTINCTROW [Crosstab-Dept54] INNER JOIN [TBL-ActualsSummary] ON
[Crosstab-Dept54].LABH_JOB = [TBL-ActualsSummary].JobNum SET
[TBL-ActualsSummary].A10 = [010], [TBL-ActualsSummary].A20 = [020],
[TBL-ActualsSummary].A30 = [030], [TBL-ActualsSummary].A40 = [040],
[TBL-ActualsSummary].A50 = [050], [TBL-ActualsSummary].A260 = [260],
[TBL-ActualsSummary].A270 = [270], [TBL-ActualsSummary].A280 = [280],
[TBL-ActualsSummary].A290 = [290];

There is a one-to-one relationship between the job number in the crosstab
query and the job number in the table I'm trying to update. How can I make
this work?

Thanks!
 
Try it this way --
UPDATE [TBL-ActualsSummary] INNER JOIN [Crosstab-Dept54] ON
[Crosstab-Dept54].LABH_JOB = [TBL-ActualsSummary].JobNum SET
[TBL-ActualsSummary].A10 = [010], [TBL-ActualsSummary].A20 = [020],
[TBL-ActualsSummary].A30 = [030], [TBL-ActualsSummary].A40 = [040],
[TBL-ActualsSummary].A50 = [050], [TBL-ActualsSummary].A260 = [260],
[TBL-ActualsSummary].A270 = [270], [TBL-ActualsSummary].A280 = [280],
[TBL-ActualsSummary].A290 = [290];
 
I'm trying to use the results of a crosstab query to update values in another
table. I'm getting the error that the "operation must use an updateable
query".

This is my crosstab query to summarize labor hours by job number and
operation number:
TRANSFORM Sum([TBL-BLOCS-Labor].LABH_HOURS) AS SumOfLABH_HOURS
SELECT [TBL-BLOCS-Labor].LABH_JOB
FROM [TBL-BLOCS-Labor]
WHERE ((([TBL-BLOCS-Labor].LABH_DEPT)="53"))
GROUP BY [TBL-BLOCS-Labor].LABH_JOB
PIVOT Right([LABH_OPER],3);

This is the update query where I'm trying to update TBL-JobActuals with the
figures as summarized by the crosstab query:
UPDATE DISTINCTROW [Crosstab-Dept54] INNER JOIN [TBL-ActualsSummary] ON
[Crosstab-Dept54].LABH_JOB = [TBL-ActualsSummary].JobNum SET
[TBL-ActualsSummary].A10 = [010], [TBL-ActualsSummary].A20 = [020],
[TBL-ActualsSummary].A30 = [030], [TBL-ActualsSummary].A40 = [040],
[TBL-ActualsSummary].A50 = [050], [TBL-ActualsSummary].A260 = [260],
[TBL-ActualsSummary].A270 = [270], [TBL-ActualsSummary].A280 = [280],
[TBL-ActualsSummary].A290 = [290];

There is a one-to-one relationship between the job number in the crosstab
query and the job number in the table I'm trying to update. How can I make
this work?

Thanks!
Click to expand...

No Crosstab query (or any othe rtotals query) is ever updateable, nor is any
query joining such a query. You can base an Append query on a crosstab to
create new records, but to update you will need to update each field to a
DLookUp expression looking up the corresponding record in the crosstab.

May I ask why you want to store this denormalized data in a table? What will
you do with it that you can't do with the crosstab query directly? Or will the
results of the query need to be edited (so they aren't actually redundant)?
 
I examined your post and it doesn't seem like I am getting the correct picture.

Does your table [TBL-ActualsSummary] have fields A10, A20, A30, A40, A50,
A260, A270, A280, and A290?

You also have a field for job number so why have individual fields for each
job number?

Your table view would look like this --
Job_number A10 A20 A30 A40 A50 A260 A270 A280 A290
A10 3
A20 6
A30 2
A40 7
A50 3
A260 1
A270 6
A280 9
A290 5
 
You can use the Crosstab in a make table query to make a temporary table with
the values and then you probably can use the temporary table to update records
in [TBL-ActualsSummary].

As John has stated. Why do you need to do this? It is usually (not always)
not a good idea since one change in the source data [TBL-BLOCS-Labor] then
makes the data in [TBL-ActualsSummary] inaccurate.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to use the results of a crosstab query to update values in another
table. I'm getting the error that the "operation must use an updateable
query".

This is my crosstab query to summarize labor hours by job number and
operation number:
TRANSFORM Sum([TBL-BLOCS-Labor].LABH_HOURS) AS SumOfLABH_HOURS
SELECT [TBL-BLOCS-Labor].LABH_JOB
FROM [TBL-BLOCS-Labor]
WHERE ((([TBL-BLOCS-Labor].LABH_DEPT)="53"))
GROUP BY [TBL-BLOCS-Labor].LABH_JOB
PIVOT Right([LABH_OPER],3);

This is the update query where I'm trying to update TBL-JobActuals with the
figures as summarized by the crosstab query:
UPDATE DISTINCTROW [Crosstab-Dept54] INNER JOIN [TBL-ActualsSummary] ON
[Crosstab-Dept54].LABH_JOB = [TBL-ActualsSummary].JobNum SET
[TBL-ActualsSummary].A10 = [010], [TBL-ActualsSummary].A20 = [020],
[TBL-ActualsSummary].A30 = [030], [TBL-ActualsSummary].A40 = [040],
[TBL-ActualsSummary].A50 = [050], [TBL-ActualsSummary].A260 = [260],
[TBL-ActualsSummary].A270 = [270], [TBL-ActualsSummary].A280 = [280],
[TBL-ActualsSummary].A290 = [290];

There is a one-to-one relationship between the job number in the crosstab
query and the job number in the table I'm trying to update. How can I make
this work?

Thanks!
Click to expand...

No Crosstab query (or any othe rtotals query) is ever updateable, nor is any
query joining such a query. You can base an Append query on a crosstab to
create new records, but to update you will need to update each field to a
DLookUp expression looking up the corresponding record in the crosstab.

May I ask why you want to store this denormalized data in a table? What will
you do with it that you can't do with the crosstab query directly? Or will the
results of the query need to be edited (so they aren't actually redundant)?
Click to expand...
 
I think I may be able to created the reports I need by joining 2 crosstab
queries and 2 other tables into one query that I can use for the datasource
for the various reports they want. If I'm not able to connect them all I'll
append the crosstab queries into temporary tables which I can then use for
the reports.

Thanks!
 
The job numbers are different 5 digit numbers. The summary table does have
fields A10, A20, etc. They are operation codes for labor done for each job.
Each job has some or all of these operation codes. We are tracking estimated
vs. actual costs for each of these operations for each job. The final result
should look like:

Job # E10(est) A10(act) E20 A20 ....
12345 3.0 3.6 1.5 1.3
23456 2.0 2.2

I think I will be able to use the crosstab queries for each dept joined with
the estimates table to create the reports directly, as John suggested,
instead creating the summary table, which I was going to join with the
estimates table to create the reports.

Thanks for the help.

KARL DEWEY said:
I examined your post and it doesn't seem like I am getting the correct picture.

Does your table [TBL-ActualsSummary] have fields A10, A20, A30, A40, A50,
A260, A270, A280, and A290?

You also have a field for job number so why have individual fields for each
job number?

Your table view would look like this --
Job_number A10 A20 A30 A40 A50 A260 A270 A280 A290
A10 3
A20 6
A30 2
A40 7
A50 3
A260 1
A270 6
A280 9
A290 5

--
Build a little, test a little.


cmk7471 said:
I made the change but unfortunately I get the same error. Any more ideas?
Click to expand...
Click to expand...
 
Back
Top