uncle_scrooge

04-20-2004, 12:00 AM

Iam trying to display data from an access database. My database is splitted up in 4 groups with an emty line between each group. In this case i just want to select the first group. How do it do it? I tryed the example below but i seems like it taking all 4 groups.

Set rs1 = conn_db.Execute( _

"SELECT Brand, SUM([Sales Value (Euro)]) As SumValueEuro, SUM([Sales Units]) As SumUnits, SUM([Sales Units %]) As SumUnitsper, SUM([Sales Euro %]) As SumEuroper, AVG([EU-Price per Unit]) As AvgPerunit, SUM(Unwcalc) As SumUnwcalc, SUM(Wcalc) As SumWcalc FROM Table1 GROUP BY Brand ORDER BY SUM([Sales Units]) DESC", , _

adCmdText)

Do While Not rs1.Fields("Brand") = ""

excel_sheet.Cells(row, 1) = rs1.Fields("Brand").Value

excel_sheet.Cells(row, units_perc_col) = rs1.Fields("SumUnitsper").Value

excel_sheet.Cells(row, units_col) = rs1.Fields("SumUnits").Value

excel_sheet.Cells(row, value_perc_col) = rs1.Fields("SumEuroper").Value

excel_sheet.Cells(row, value_col) = rs1.Fields("SumValueEuro").Value

excel_sheet.Cells(row, price_per_unit_col) = rs1.Fields("AvgPerunit").Value

If rs1.Fields("SumUnwcalc").Value <> 0 Then

excel_sheet.Cells(row, unweighted_col) = ((rs1.Fields("SumUnwcalc").Value) / (rs1.Fields("SumUnits").Value))

End If

If rs1.Fields("SumWcalc").Value <> 0 Then

excel_sheet.Cells(row, weighted_col) = ((rs1.Fields("SumWcalc").Value) / (rs1.Fields("SumValueEuro").Value))

End If

row = row + 1

rs1.MoveNext

Loop

Set rs1 = conn_db.Execute( _

"SELECT Brand, SUM([Sales Value (Euro)]) As SumValueEuro, SUM([Sales Units]) As SumUnits, SUM([Sales Units %]) As SumUnitsper, SUM([Sales Euro %]) As SumEuroper, AVG([EU-Price per Unit]) As AvgPerunit, SUM(Unwcalc) As SumUnwcalc, SUM(Wcalc) As SumWcalc FROM Table1 GROUP BY Brand ORDER BY SUM([Sales Units]) DESC", , _

adCmdText)

Do While Not rs1.Fields("Brand") = ""

excel_sheet.Cells(row, 1) = rs1.Fields("Brand").Value

excel_sheet.Cells(row, units_perc_col) = rs1.Fields("SumUnitsper").Value

excel_sheet.Cells(row, units_col) = rs1.Fields("SumUnits").Value

excel_sheet.Cells(row, value_perc_col) = rs1.Fields("SumEuroper").Value

excel_sheet.Cells(row, value_col) = rs1.Fields("SumValueEuro").Value

excel_sheet.Cells(row, price_per_unit_col) = rs1.Fields("AvgPerunit").Value

If rs1.Fields("SumUnwcalc").Value <> 0 Then

excel_sheet.Cells(row, unweighted_col) = ((rs1.Fields("SumUnwcalc").Value) / (rs1.Fields("SumUnits").Value))

End If

If rs1.Fields("SumWcalc").Value <> 0 Then

excel_sheet.Cells(row, weighted_col) = ((rs1.Fields("SumWcalc").Value) / (rs1.Fields("SumValueEuro").Value))

End If

row = row + 1

rs1.MoveNext

Loop