If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Do You Have A SQL Query For This Problem?
Hello,
I have a table in an Access database that I want to create an SQL Query for. Here is the table structu 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 |
#2
|
|||
|
|||
Do You Have A SQL Query For This Problem?
On 6 mei, 08:45, Jason wrote:
Hello, I have a table in an Access database that I want to create an SQL Query for. *Here is the table structu 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 |
#3
|
|||
|
|||
Do You Have A SQL Query For This Problem?
Jason wrote:
I have a table in an Access database that I want to create an SQL Query for. Here is the table structu 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 -- Marsh MVP [MS Access] |
Thread Tools | |
Display Modes | |
|
|