combining Rows(is it possible)

[wren17]

hello everyone:

I have some rows with all the same data except for an amount field.

for instance:
Column1:Row 1 contains "apples"
Column2:Row 1 contains "$1.00"

Column1:Row 2 contains "apples"
Column2:Row 2 contains "$2.00"

Is there an SQL statement that could go thru my table and merge Rows 1 and 2 so that I only have one row with "apples" for "$3.00".

I know that this can be done by writing a make table query and doing group bys with a sum on Column2, but I would like to just update my table and not create another one.

if any one can help me with this matter. I would greatly appreciate it.

thanks,
Matt

 

[smozgur]

Matt,

Table Name : Table1
Field1 = Names (apples, bananas etc..)
Field2 = Amounts

Then use the SQL string below. You will see it will work for you.

SELECT Table1.Field1, Sum(Table1.Field2) AS SumOfField2
FROM Table1
GROUP BY Table1.Field1;

To do same thing in Query design. Just add the Name field and Amount Field into a new query and click View_Totals (Or right click on fields section (where you inserted fields) in query window and select Totals). You will see an additional row - Total - will be shown just above Sort section.

Now let it go with Group By choice for the Names field but select Sum for the Amounts in Total section. Run Query.

That's it. I hope it helps.

Suat

 

[smozgur]

Oh, sorry. I just read that you already know this.

But then why not create SAME table with a Make Table Query (with the one you created by Group) and delete the other one ? It is the only way to do that easily I think.

Suat

 

[smozgur]

This code may help. Sorry, still not query (I am not sure if it could be done by a Query) but code .<TABLE BORDER=0 ALIGN=CENTER WIDTH=85%><TR><TD><HR></TD></TR><TR><TD><FONT SIZE=3><PRE>
Sub Macro()
'Microsoft DAO Object Library Required
Dim rcset As DAO.Recordset
Dim rcset2 As DAO.Recordset
Dim i As Long
'Table1 = Table
'Field1 = name
'Field2 = amount

Set rcset = CurrentDb.OpenRecordset _
("SELECT Table1.Field1, Sum(Table1.Field2) AS SumOfField2 FROM Table1 GROUP BY Table1.Field1;")
Do Until rcset.EOF
Set rcset2 = CurrentDb.OpenRecordset _
("SELECT * FROM Table1 WHERE Field1 =""" & rcset.Fields(0).Value & """;")
i = 1
Do Until rcset2.EOF
If i = 1 Then
rcset2.Edit
rcset2.Fields("Field2").Value = rcset.Fields(1).Value
rcset2.Update
Else
rcset2.Delete
End If
i = i + 1
rcset2.MoveNext
Loop
rcset.MoveNext
Loop
End Sub</PRE></FONT></TD></TR><TR><TD><HR></TD></TR></TABLE></FONT>

Opens the table and groups data then modifies on of the current name record by using grouped data total amount and deletes the others.

Hope this helps.
Suat
This message was edited by smozgur on 2003-01-23 07:50

 

[wren17]

Thanks for all your help I will try the VBA code you provide and see what that does.

Wren