select and sum record with different values

  • Thread starter Thread starter eugene
  • Start date Start date
E

eugene

hello
have the following table

field1 f2 f3 f4
land 90 west 88889
land 50 north 88855
land 60 east 88873
land 50 west 88789
land 100 south 88777

what i would like to do is sum the value of field 2 and if the total is <400
(which it is) i would like to
do 2 things

1) Select all these records as they are and make a new table with these
values
2) Delete these records out of the existing table.

the problem is that field 3 and 4 have different values but i still want
them to appear in the new table.

can someone tell me how to do this?

thank
eugene
 
Try this:

SELECT field1, f2, f3, f4
INTO NewTable
FROM OldTable
WHERE 400 >
(SELECT Sum(f2)
FROM OldTable As T2
WHERE T2.field1 = OldTable.field1)

... but Access might declare the query not updatable because of the totals
subquery, so you might have to do:

SELECT field1, f2, f3, f4
INTO NewTable
FROM OldTable
WHERE 400 >
DSum("f2", "OldTable", "field 1 = '" & [OldTable].[field1])

After you get the new table built, delete the old rows with:

DELETE *
FROM OldTable
WHERE field1 IN (SELECT field1 FROM NewTable)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Back
Top