[zcast]

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

[modi123_1]

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

[zcast]

Thank you modi123_1 for all your help, this works perfectly!