Prev: pdf thumbnail in access report
Next: Absolute Value on Report
From: Jason on 6 May 2010 02:45
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
From: XPS350 on 6 May 2010 04:16
On 6 mei, 08:45, Jason <jason.wu...(a)gmail.com> wrote:
> 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
From: Marshall Barton on 6 May 2010 09:15
Jason wrote:
>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

--
Marsh
MVP [MS Access]
 | 
Pages: 1
Prev: pdf thumbnail in access report
Next: Absolute Value on Report