Do You Have A SQL Query For This Problem?

J

Jason

Hello,

I have a table in an Access database that I want to create an SQL
Query for. Here
is the table structure:

Location Number
Area1 2
Area1 3
Area1 5
Area2 4
Area2 2
Area3 1

I would like to create an update SQL statement so that it adds up the
total of numbers for a particular location and puts that in the row.
For example, for Area 1 the number would be 2 + 3 + 5, which equals
10. Here's the final result I would like:

Location Number
Area1 10
Area1 10
Area1 10
Area2 6
Area2 6
Area3 1

Does anyone have an SQL update query that would do this?

Thanks
Jason
 
X

XPS350

Hello,

I have a table in an Access database that I want to create an SQL
Query for.  Here
is the table structure:

Location        Number
Area1   2
Area1   3
Area1   5
Area2   4
Area2   2
Area3   1

I would like to create an update SQL statement so that it adds up the
total of numbers for a particular location and puts that in the row.
For example, for Area 1 the number would be 2 + 3 + 5, which equals
10.  Here's the final result I would like:

Location        Number
Area1   10
Area1   10
Area1   10
Area2   6
Area2   6
Area3   1

Does anyone have an SQL update query that would do this?

Thanks
Jason

That would like:
UPDATE YourTable SET [Number] = DSum("Number","YourTable","Location='"
& [Location] & "'");

Groeten,

Peter
http://access.xps350.com
 
M

Marshall Barton

Jason said:
I have a table in an Access database that I want to create an SQL
Query for. Here
is the table structure:

Location Number
Area1 2
Area1 3
Area1 5
Area2 4
Area2 2
Area3 1

I would like to create an update SQL statement so that it adds up the
total of numbers for a particular location and puts that in the row.
For example, for Area 1 the number would be 2 + 3 + 5, which equals
10. Here's the final result I would like:

Location Number
Area1 10
Area1 10
Area1 10
Area2 6
Area2 6
Area3 1

Does anyone have an SQL update query that would do this?


That kind of value should never be stored in a table.
Instead, you should use a query to recalculate the sum as
needed. In the simplest situations, the query could be
like:

SELECT Location, Sum([Number]) As Total
FROM table
GROUP BY Location
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top