Selecting records with missing values 2

[nlbertram]

Hello---

Here is an example of what I am trying to do.

I am trying to select records based on the Number field. I would like to return Row IDs 4-7 when I search for Number = 800004.



Row ID Number Name Points

1 100019 CCF RTE 155
2 242
3 132
4 800004 CCF AHP 1
5 3
6 14
7 15
8 687899 CCF PUP 8
9 9
10 17




Any ideas?

 

[Andrzejek]

Only RowID 4 is equal to Number = 800004

How do you know that RowID 5, 6, and 7 also are Number = 800004?

You either need to 'walk' the records down until you hit another Number, or have you data like this
[tt]
Row ID Number Name Points
1 100019 CCF RTE 155
2 100019 CCF RTE 242
3 100019 CCF RTE 132
4 800004 CCF AHP 1
5 800004 CCF AHP 3
6 800004 CCF AHP 14
7 800004 CCF AHP 15
8 687899 CCF PUP 8
9 687899 CCF PUP 9
10 687899 CCF PUP 17
[/tt]
or (and that would be my choice) split this table into 2 tables:
TableA: Row ID Number Name
TableB: Number (Name) Points

Have fun.

---- Andy

 

[MajP]

you need to fix your data to normalize it.

Public Sub fixData()
  Dim rs As DAO.Recordset
  Dim strSql As String
  Dim lngNumber As Long
  strSql = "Select * from tblData order by [Row ID]"
  Set rs = CurrentDb.OpenRecordset(strSql, dbOpenDynaset)
  Do While Not rs.EOF
    If IsNull(rs![Number]) Then
      rs.Edit
      rs![Number] = lngNumber
      rs.Update
    Else
      lngNumber = rs![Number]
    End If
    rs.MoveNext
  Loop

Row ID	Number
1	100019
2	100019
3	100019
4	800004
5	800004
6	800004
7	800004
8	800004
9	687899
10	687899

 

[MajP]

you can add to the code to fill in the other fields.

 

[PHV]

From my reply on your other thread:

SELECT A.[Row ID], B.Number, B.Name, A.Points
FROM [ExistingTable] A, [ExistingTable] B
WHERE B.Name > ' ' [!] AND B.Number='800004'[/!]
AND B.[Row ID] = (SELECT MAX([Row ID]) FROM  [ExistingTable] WHERE [Row ID]<=A.[Row ID] AND Name>' ')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[nlbertram]

Lots of good help--

Hi MajP

I am somewhat of a novice with VBA. I could use some help executing your code. A started by creating a class module. Is there a certain type of form object that I should be using?

Thanks again!

 

[MajP]

I believe PHV demonstrated a SQL solution to update your table or make a new table. I believe if you can do it in sql then always go with that route. 99% of the time a sql solution is more efficient and faster. I do a lot of recordset solutions because I am too lazy to think of a good sql solution. However, if you want to use code then it goes in a standard module not a class module. A class module is for developing objects for object oriented programming. So this would simply go in a standard module.

http://msdn.microsoft.com/en-us/library/aa190118(v=office.10).aspx

 

[nlbertram]

I initially tried the straight SQL approach, but ran into performance issues. I am actually working with about 100 fields and 1 million records. I think MS Access was getting hung up due to the sheet volume of data being returned via SQL.


Since the VBA approach is only handling one field, MS Access does not choke. This has solved my problem.


Bravo!