Update with summary

[Guest]

I have an excel table I'm importing. It has line items keyed on PO and
project containing dollars. I want to sum up the dollars by PO, then post the
summed dollars to the PO table (a different table, already in Access). I had
no trouble summing up the dollars by PO in a query. But when I tried to
create an update query that posted the dollars to the PO table, Access says I
must use an updateable query. I've run into this before. I don't want to do a
make-table query to create the summary, then delete it. Any way to do it?

 

[Douglas J. Steele]

What's the code you're trying to run, and the SQL of the query?

 

[Guest]

Final Query:
UPDATE qrySummarizePODollarAmtImport INNER JOIN tblPO ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];

qrySummarizePODollarAmtImport:
SELECT AllPOImport.[PO NO] AS PONbr, Sum(AllPOImport.[GROUP LINE COST]) AS
DlrVal, Count(AllPOImport.[PO NO]) AS LineCount
FROM AllPOImport
GROUP BY AllPOImport.[PO NO]
ORDER BY Count(AllPOImport.[PO NO]) DESC;

 

[Douglas J. Steele]

I believe that should be

UPDATE tblPO INNER JOIN qrySummarizePODollarAmtImport ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Final Query:
UPDATE qrySummarizePODollarAmtImport INNER JOIN tblPO ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];

qrySummarizePODollarAmtImport:
SELECT AllPOImport.[PO NO] AS PONbr, Sum(AllPOImport.[GROUP LINE COST]) AS
DlrVal, Count(AllPOImport.[PO NO]) AS LineCount
FROM AllPOImport
GROUP BY AllPOImport.[PO NO]
ORDER BY Count(AllPOImport.[PO NO]) DESC;

--

Jim

What's the code you're trying to run, and the SQL of the query?

 

[Guest]

I understand what you were saying there. I copied your sql statement exactly
into the sql window and executed it. Got the same result. I even gave tblPO
an alias to relieve any confusion. No joy.
--
Jim

I believe that should be

UPDATE tblPO INNER JOIN qrySummarizePODollarAmtImport ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Final Query:
UPDATE qrySummarizePODollarAmtImport INNER JOIN tblPO ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];

qrySummarizePODollarAmtImport:
SELECT AllPOImport.[PO NO] AS PONbr, Sum(AllPOImport.[GROUP LINE COST]) AS
DlrVal, Count(AllPOImport.[PO NO]) AS LineCount
FROM AllPOImport
GROUP BY AllPOImport.[PO NO]
ORDER BY Count(AllPOImport.[PO NO]) DESC;

--

Jim

What's the code you're trying to run, and the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an excel table I'm importing. It has line items keyed on PO and
project containing dollars. I want to sum up the dollars by PO, then
post
the
summed dollars to the PO table (a different table, already in Access).
I
had
no trouble summing up the dollars by PO in a query. But when I tried to
create an update query that posted the dollars to the PO table, Access
says I
must use an updateable query. I've run into this before. I don't want
to
do a
make-table query to create the summary, then delete it. Any way to do
it?

 

[Douglas J. Steele]

I seem to recall hearing about something similar to this before, but I'm
afraid I don't remember the details.

Sorry.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I understand what you were saying there. I copied your sql statement
exactly
into the sql window and executed it. Got the same result. I even gave
tblPO
an alias to relieve any confusion. No joy.
--
Jim

I believe that should be

UPDATE tblPO INNER JOIN qrySummarizePODollarAmtImport ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

Final Query:
UPDATE qrySummarizePODollarAmtImport INNER JOIN tblPO ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt
=
[dlrval];

qrySummarizePODollarAmtImport:
SELECT AllPOImport.[PO NO] AS PONbr, Sum(AllPOImport.[GROUP LINE COST])
AS
DlrVal, Count(AllPOImport.[PO NO]) AS LineCount
FROM AllPOImport
GROUP BY AllPOImport.[PO NO]
ORDER BY Count(AllPOImport.[PO NO]) DESC;

--

Jim


:

What's the code you're trying to run, and the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an excel table I'm importing. It has line items keyed on PO
and
project containing dollars. I want to sum up the dollars by PO, then
post
the
summed dollars to the PO table (a different table, already in
Access).
I
had
no trouble summing up the dollars by PO in a query. But when I tried
to
create an update query that posted the dollars to the PO table,
Access
says I
must use an updateable query. I've run into this before. I don't
want
to
do a
make-table query to create the summary, then delete it. Any way to
do
it?

 

[Guest]

Hard to believe, but I found the answer on a Microsoft site:
http://office.microsoft.com/en-us/access/HP030702011033.aspx

They tell you to use a dsum() term in the update. Hardly seems efficient,
but I got it to work. Thanks Doug.
--
Jim

I seem to recall hearing about something similar to this before, but I'm
afraid I don't remember the details.

Sorry.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

I understand what you were saying there. I copied your sql statement
exactly
into the sql window and executed it. Got the same result. I even gave
tblPO
an alias to relieve any confusion. No joy.
--
Jim

I believe that should be

UPDATE tblPO INNER JOIN qrySummarizePODollarAmtImport ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt =
[dlrval];


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Final Query:
UPDATE qrySummarizePODollarAmtImport INNER JOIN tblPO ON
qrySummarizePODollarAmtImport.PONbr = tblPO.PONbr SET tblPO.PODollarAmt
=
[dlrval];

qrySummarizePODollarAmtImport:
SELECT AllPOImport.[PO NO] AS PONbr, Sum(AllPOImport.[GROUP LINE COST])
AS
DlrVal, Count(AllPOImport.[PO NO]) AS LineCount
FROM AllPOImport
GROUP BY AllPOImport.[PO NO]
ORDER BY Count(AllPOImport.[PO NO]) DESC;

--

Jim


:

What's the code you're trying to run, and the SQL of the query?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


I have an excel table I'm importing. It has line items keyed on PO
and
project containing dollars. I want to sum up the dollars by PO, then
post
the
summed dollars to the PO table (a different table, already in
Access).
I
had
no trouble summing up the dollars by PO in a query. But when I tried
to
create an update query that posted the dollars to the PO table,
Access
says I
must use an updateable query. I've run into this before. I don't
want
to
do a
make-table query to create the summary, then delete it. Any way to
do
it?