update query

[fredk]

I received data that has a field called "group number" and a field called subs - the rows look like this:

groupnumber subs
01-12345 2
00-12345 1
85-11111 6
01-11111 4
00-12121 1
00-12888 2


I want to get rid of the first 3 digits (in other words the 01-, 00-, 85-, etc)

I was able to run an update query using the right function to trim off the first three. My question now is in regards to totalling the subs and getting rid of the duplicats -

Using the sample data my new table would be:

groupnumber subs
12345 3
11111 10
12121 1
12888 2

Any help would be greatly appreciated!!!!

Fred

 

[fredk]

okay, I just realized how to get rid of the duplicates (copy the table structure and then set the group number field to primary and run an append query into the new table)

But still not sure how to get the total subs ????


Thanks!!!

Fred

 

[fredk]

Okay - I figured it out - although I am curious to see if there is a more efficient way.

I used a "create table" query based off of my table and was able to sum into the new table -

Hope that makes sense -

Thanks!

Fred

 

[PHV]

A one shot query, from the orinal table to the desired result:
SELECT CLng(Mid(4, groupnumber)) As groupnumber, Sum(subs) As subs
INTO NewTable
FROM OldTable
GROUP BY CLng(Mid(4, groupnumber));

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244