emty line + recordset

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

Can you please explain a little bit better as to how your end results should look like.

Iam grouping products under their brands.

The database is splitted up in 4 parts with 1 empty line between each part. The first group of brands just contains around 10 brands(the total is around 60 brands).

The results that the code above displays(in one excel sheet) groups all 60 brands but i only want to display the 1 part(around 10 brands).

I was wondering if the SQL query has to to something about it or if its the loop.

The database is splitted up in 4 parts with 1 empty line between each part. The first group of brands just contains around 10 brands(the total is around 60 brands).


It is a little unclear what you mean by this "1 empty line", but I'm thinking that this is just what you SEE on the screen in the way the data has been input.

Your SQL does not have a WHERE clause, thus it will return ALL records in your database.

The "empty line" is blank cells in the database. In the example above iam checking onw of the columns called "Brand" i want it to stop when the recordset finds this cell empty.

I was thinking about the SQL query but is there a way to just select the first part?

The database is splitted up in 4 parts with 1 empty line between each part. The first group of brands just contains around 10 brands(the total is around 60 brands).

What do you mean by this? Can you show us an example as to how ur database looks like. Your problem is still unclear to me.

allright, like this but with more of rows and columns.

Column1 Column2 Column3[...]
data data data
data data data
data data data
emty line
data data data
data data data
data data data
emty line
data data data
data data data
data data data
emty line
data data data
data data data
data data data

OK, the text was alittle messed up above, each "data" should be under the columns but i hope you understand.

I think, the database design needs to restructured. It is not the right way to leave a empty line to group each 10 brands to a single group (If my understanding is correct). My Suggestion is why don't you design your table by including one more column.

Group Name
Brand
Qty

Then, your table records will look like

Group Name Brand Qty
G1 BR1 100
G1 BR2 200
G2 BR3 300
G2 BR4 20

If you design in this way, then you can have separate select statements for different groups.

thanks for the answer but i dont have the chance to redesign the database beacuse i get the input from another excel sheet and i think it's to complicated.

I dont know if you got it completely right but forget about grouping etc. Is there a way to just select the 1 part of the 4 ?

Dim qds As DAO.QueryDef
Dim rs As DAO.Recordset
Set qds = CurrentDb().QueryDefs("QUERY1")
Set rs = qds.OpenRecordset(dbOpenDynaset)
rs.MoveFirst
Do While Not rs.EOF
If Nz(rs.Fields(0)) = "" Then
MsgBox ("Null Value")
Else
MsgBox (rs.Fields(0))
End If
rs.MoveNext
Loop


Try this code. Hope this helps you out. You can put the values into 4 arrays for 4 brands. With the help of this.