Moving column data from one table to another

  • Thread starter Thread starter Aaron
  • Start date Start date
A

Aaron

Hi,

Here is my query:

INSERT INTO CartWeight ( WeightTotal, CartIDNumber, CartNumber )
SELECT
Sum(([Cart.Line1Weight]+[Cart.Line2Weight]+[Cart.Line3Weight]+[Cart.Line4Wei
ght]+[Cart.Line5Weight]+[Cart.Line6Weight]+[Cart.Line7Weight]+[Cart.Line8Wei
ght]+[Cart.Line9Weight]+[Cart.Line10Weight]+[Cart.Line11Weight]+[Cart.Line12
Weight])) AS Expr1, Cart.CartID, Cart.CartNumber
FROM Cart, CartWeight;


Here is my error:
You tried to execute a query that does not include the specified expression
<name> as part of an aggregate function.

I need to add CartIDNumber and CartNumber from Cart{table] in to
CartWeight
.

How can I do this?


TIA
 
Is there a reason why you have the CartWeight table in the FROM clause? What
happens if you try:

INSERT INTO CartWeight ( WeightTotal, CartIDNumber, CartNumber )
SELECT Sum(([Cart.Line1Weight]+[Cart.Line2Weight]+
[Cart.Line3Weight]+[Cart.Line4Weight]+[Cart.Line5Weight]+
[Cart.Line6Weight]+[Cart.Line7Weight]+[Cart.Line8Weight]+
[Cart.Line9Weight]+[Cart.Line10Weight]+[Cart.Line11Weight]+
[Cart.Line12Weight])) AS Expr1, Cart.CartID, Cart.CartNumber
FROM Cart;

Do you realize if any of the weight fields are null, this will propagate
across all fields in the record?
 
Hi, Duane,

Besides Duane's observation, Aaron, this is why I almost always use Access'
Query Design grid as my query designer, even when I'm going to copy and paste
the SQL into VBA: I have a tendency to forget the obvious. The error you got,
"You tried to execute a query ..." is always indicative of using a totals
query (using the Sum() aggregate function always indicates a totals query)
that hasn't been completed. All fields need some aggregate function. In your
case, you forgot to add "GROUP BY CartID, CartNumber" after the FROM clause.
If you would have used the Query Designer, Access would have put it in there
automatically.

HTH

Duane said:
Is there a reason why you have the CartWeight table in the FROM clause? What
happens if you try:

INSERT INTO CartWeight ( WeightTotal, CartIDNumber, CartNumber )
SELECT Sum(([Cart.Line1Weight]+[Cart.Line2Weight]+
[Cart.Line3Weight]+[Cart.Line4Weight]+[Cart.Line5Weight]+
[Cart.Line6Weight]+[Cart.Line7Weight]+[Cart.Line8Weight]+
[Cart.Line9Weight]+[Cart.Line10Weight]+[Cart.Line11Weight]+
[Cart.Line12Weight])) AS Expr1, Cart.CartID, Cart.CartNumber
FROM Cart;

Do you realize if any of the weight fields are null, this will propagate
across all fields in the record?
Hi,
Click to expand...
[quoted text clipped - 19 lines]
TIA
Click to expand...
Click to expand...
 
Back
Top