2 Replies - 2101 Views - Last Post: 12 February 2013 - 02:05 PM Rate Topic: -----

#1 zcast  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-February 13

Total sum of column for each record in access?

Posted 12 February 2013 - 01:34 PM

Hello All,

I was wondering if someone could point me in the right direction of how to go about querying an access database in vb2010 to:

a) Get sum of a column? (Which I believe, "SELECT sum(rate) from Rates" would work)

and

b)retrieving all records and getting a sum of a column for each record, ie.

Looking for all records for California, and a sum of all of the records, and then

Looking for all records for New Mexico, and a sum of all of the records, and then.

and so forth.

Maybe, I should just say, I need to get all the records for each state, and then a sum of a certain column for each state??

Sorry, if I'm not being clear, still pretty new to access and databases.

Thanks in advance for any help.

Zcast

Is This A Good Question/Topic? 0
  • +

Replies To: Total sum of column for each record in access?

#2 modi123_1  Icon User is online

  • Suitor #2
  • member icon



Reputation: 9388
  • View blog
  • Posts: 35,248
  • Joined: 12-June 08

Re: Total sum of column for each record in access?

Posted 12 February 2013 - 01:58 PM

You would want to use the 'group by' term. Basically you would determine how you would like your data to be grouped around one major characteristic (well there can be more than one, but that's not material now), and then you can do all sorts of fun aggregate functions like: count, average, sum, etc.


http://msdn.microsof...sql.105%29.aspx
http://msdn.microsof...=sql.90%29.aspx

Example:


create table #foo_table(state_id int, alien_sightings int)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (1, 20)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (2, 20)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (3, 30)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (4, 20)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (1, 60)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (1, 2)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (3, 3)

INSERT INTO #foo_table(state_id, alien_sightings)
VALUES (3, 11)

-- shows me all the rows
select state_id, alien_sightings
from #foo_table

-- says get me the sum of alien sightings in the subgroups where state_id is matching.
select state_id, sum(alien_sightings)
from #foo_table
group by state_id


drop table #foo_table

Was This Post Helpful? 0
  • +
  • -

#3 zcast  Icon User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 2
  • Joined: 12-February 13

Re: Total sum of column for each record in access?

Posted 12 February 2013 - 02:05 PM

Thank you modi123_1 for all your help, this works perfectly!
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1