Re: Crosstab query as data source for update query

Tech-Archive recommends: Fix windows errors by optimizing your registry

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

John W. Vinson wrote:
On Fri, 7 Aug 2009 07:47:01 -0700, cmk7471 <kaluscheatnphdotwelsdotnet> wrote:

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!



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)?
.

Relevant Pages

  • Re: Corstabquery or normal query
    ... You can create the crosstab similar to the suggestion by John. ... church, also church elders, we have many elders, only 1st elder is one person. ... I would first build a query to get the data ...
    (microsoft.public.access.reports)
  • Re: Access 2003 - Transpose
    ... John, I think your approach uses crosstab, which as I discovered yesterday, ... I want to transpose BOMPart#, ... Second query uses the above saved query and your table to get the desired ...
    (microsoft.public.access.queries)
  • Re: moving like column data to a new columns for related rows
    ... John, thanks again for looking at this. ... I saved the query as qRankQuery and it is the base for the crosstab ... In the Select View of the qRankQuery - The dataset looks good. ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... field very similar to the LOCATE field in the STATES2 that we created. ... I was certain that all I had to do was create a crosstab on RCROSS with RLOC ... do is to modify the main query to change your RFLY_RSTA to LOCATE and the ... locations for each species as rows with the ...
    (microsoft.public.access.queries)
  • Re: TWO Crosstab Query Ouput Questions
    ... we want to see and use those as the input to the crosstab. ... and only 1098 in the final query. ... locations for each species as rows with the ... concerning the Totals Query. ...
    (microsoft.public.access.queries)