the below code works in excel, where both the tables are in to different sheet of the same woorbook
now to write this in access.
Public Sub test()
Sheets("Sheet1").Activate
lrow1 = Cells(Rows.Count, 1).End(xlUp).Row
ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Clear
ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("B2:B" & lrow1), SortOn:=xlSortOnValues, Order:=xlAscending, DataOption:= _
xlSortNormal
ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort.SortFields.Add Key:=Range _
("A2:A" & lrow1), SortOn:=xlSortOnValues, Order:=xlDescending, DataOption:= _
xlSortNormal
With ThisWorkbook.Worksheets("Sheet1").AutoFilter.Sort
.Header = xlYes
.MatchCase = False
.Orientation = xlTopToBottom
.SortMethod = xlPinYin
.Apply
End With
Sheets("Casesview").Activate
lrow = Cells(Rows.Count, 1).End(xlUp).Row
For i = 2 To lrow
cnt = Application.WorksheetFunction.CountIf(Sheets("Sheet1").Range("B:B"), Cells(i, "I").Value)
CreatedDate = Cells(i, "f").Value
FindString = Cells(i, "I").Value
For j = 1 To cnt
Sheets("Sheet1").Activate
With Sheets("Sheet1").Range("B:B")
Set Rng = .Find(What:=FindString, _
After:=.Cells(.Cells.Count), _
LookIn:=xlValues, _
LookAt:=xlWhole, _
SearchOrder:=xlByRows, _
SearchDirection:=xlNext, _
MatchCase:=False)
If Not Rng Is Nothing Then
Application.Goto Rng, True
Else
MsgBox "Nothing found"
End If
End With
If cnt = 1 Then
Sheets("CasesView").Range("AD" & i) = ActiveCell.Offset(0, 1).Value
Sheets("CasesView").Range("AE" & i) = ActiveCell.Offset(0, 2).Value
Sheets("CasesView").Range("AF" & i) = ActiveCell.Offset(0, 3).Value
Sheets("CasesView").Range("AG" & i) = ActiveCell.Offset(0, 4).Value
Sheets("CasesView").Range("AH" & i) = ActiveCell.Offset(0, 5).Value
Else
If CreatedDate >= ActiveCell.Offset(j - 1, -1).Value Then
Sheets("CasesView").Range("AD" & i) = ActiveCell.Offset(0, 1).Value
Sheets("CasesView").Range("AE" & i) = ActiveCell.Offset(0, 2).Value
Sheets("CasesView").Range("AF" & i) = ActiveCell.Offset(0, 3).Value
Sheets("CasesView").Range("AG" & i) = ActiveCell.Offset(0, 4).Value
Sheets("CasesView").Range("AH" & i) = ActiveCell.Offset(0, 5).Value
End If
End If
Sheets("CasesView").Activate
Next
Next
End Sub