Not Updatable Query problem

  • Thread starter Thread starter MichaelK
  • Start date Start date
M

MichaelK

I have a table where I have fields SalesRepId and Commissions and some
other fields.
I have a query where I calculate sum of commissions grouped by
SalesRepId for some period of time from another table. And result of the
query 2 fields:
SalesRepId and TotCommiss.
Now I want to run Update query where for each SalesRepId in the first
table
update Commissins with TotCommiss from the query.

It gives me a message that it suppose to be an updatable query.

(I use the way around by creating the temp table with results of the sum
query,
and after when the same data in the table I can run Update query based on
tables,
and it working fine. But I hate this way.)

Any knows what the problem is and why this query not updatable..
Just hope all this not too confusing.

Thanks,
Michael
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Common bug... er, feature, of Access SQL. I usually use a DLookup()
function in the UPDATE query to avoid that PITA. Like this:

UPDATE table_name
SET column_name = DLookup("TotCommiss","query_name","SalesRepID=" &
[SalesRepID])

The [SalesRepID] will refer to the column in the "table_name" table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQP8c8IechKqOuFEgEQKGmwCePTzPwSmGeEDBemE9qswQgJVUCMQAn1GN
KRgGkDEdJ69HK/vtE7sD3/gc
=WfYk
-----END PGP SIGNATURE-----
 
Thanks, very interesting idea.
I tested it, and unfortunately taking too long.
I'm faster creating temp table with sum result, run update query INNER JOIN
two tables on RepId, and killing temp table.
Just seems I had this not updatable query problem long time ago and found
the way to handle it, can't remember though. May be it was a dream :-)

Regards
Michael

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Common bug... er, feature, of Access SQL. I usually use a DLookup()
function in the UPDATE query to avoid that PITA. Like this:

UPDATE table_name
SET column_name = DLookup("TotCommiss","query_name","SalesRepID=" &
[SalesRepID])

The [SalesRepID] will refer to the column in the "table_name" table.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQP8c8IechKqOuFEgEQKGmwCePTzPwSmGeEDBemE9qswQgJVUCMQAn1GN
KRgGkDEdJ69HK/vtE7sD3/gc
=WfYk
-----END PGP SIGNATURE-----

I have a table where I have fields SalesRepId and Commissions and some
other fields.
I have a query where I calculate sum of commissions grouped by
SalesRepId for some period of time from another table. And result of the
query 2 fields:
SalesRepId and TotCommiss.
Now I want to run Update query where for each SalesRepId in the first
table
update Commissins with TotCommiss from the query.

It gives me a message that it suppose to be an updatable query.

(I use the way around by creating the temp table with results of the sum
query,
and after when the same data in the table I can run Update query based on
tables,
and it working fine. But I hate this way.)

Any knows what the problem is and why this query not updatable..
Just hope all this not too confusing.
Click to expand...
Click to expand...
 
Back
Top