Find nearest value in same field (1 Viewer)
- Thread starter Olops
- Start date
- Local time
- Today, 19:17
- Joined
- Jul 9, 2003
- Messages
- 16,279
Are you saying that you want to match an entry in weight column that matches a name in the name column?
Or are you saying that from the names column you want all of the products with the same weight?
Or something else?
Sent from my SM-G925F using Tapatalk
Or are you saying that from the names column you want all of the products with the same weight?
Or something else?
Sent from my SM-G925F using Tapatalk
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 02:17
- Joined
- May 7, 2009
- Messages
- 19,230
if you're purpose is to add this to second table, you can do it through vba.
paste this code in a module and press F5, while the cursor is inside the Sub, to execute.
paste this code in a module and press F5, while the cursor is inside the Sub, to execute.
Code:
Private Sub test()
Dim rs As DAO.recordSet
Dim db As DAO.Database
Dim i As Long
Dim s As String
set db=Currentdb
Set rs = db.OpenRecordset("select [name], [weight (kg)] from table1 order by 2;", dbOpenSnapshot)
With rs
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
s = ![name] & " - " & ![weight (kg)]
.MoveNext
If Not .EOF Then s = s & " and " & ![name] & " - " & ![weight (kg)]
db.execute ("Insert Into table2 ([Nearest Weight]) SELECT '" & s & "';")
If .EOF Then Exit Do
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub- Local time
- Today, 19:17
- Joined
- Jul 9, 2003
- Messages
- 16,279
If you added:-
Name .......... weight
jjj ..................... 15
What would Table 2 look like?
Sent from my SM-G925F using Tapatalk
Name .......... weight
jjj ..................... 15
What would Table 2 look like?
Sent from my SM-G925F using Tapatalk
Nearest Weight
fff - 5 and ggg - 10
jjj - 15 and bbb - 60
aaa - 100 and hhh - 205
eee - 210 and ccc - 300
ddd - 500
Since the output desired by the OP has the weights paired up it doesn't work with an odd number of input records.
I tried arnelgp's code in the attached database. I had to change the "Order by 2" to "order by [weight (kg)]" to get it to work but other than that it produces the desire output if the number of records is an even number.
I also added the line
to the code to the clear the table.
I suggest that you use a difference name than "name" for your field name as "name" is a reserved word.
I also added the line
Code:
CurrentDb.Execute "DELETE * FROM table2" 'Clear contents of table2to the code to the clear the table.
I suggest that you use a difference name than "name" for your field name as "name" is a reserved word.
I don't understand what you want. If there's a duplicate name that name should be thrown out all together? I'd like to see an example of what you mean, i.e., input data with duplicate and the desired output.
Input data
[Name] [Weight]
aaa 100
aaa 103
aaa 103
bbb 60
ccc 300
ddd 500
eee 210
fff 4
fff 5
ggg 10
hhh 205
Desired output
Nearest Weight
fff - 4 and ggg - 10
fff - 5 and jjj - 15
bbb - 60 and aaa - 100
aaa - 103 and hhh - 205
aaa - 103 and eee - 210
ccc - 300 and ddd - 500
as you can see, the name aaa and fff has same name in input data but they cant matched each other. The output cannot be something like this
fff - 4 and fff - 5
aaa - 103 and aaa - 103
apologies to my english
arnelgp
..forever waiting... waiting for jellybean!
- Local time
- Tomorrow, 02:17
- Joined
- May 7, 2009
- Messages
- 19,230
hello, its me again.
test this one:
test this one:
Code:
Private Sub test()
Dim rs As DAO.Recordset
Dim db As DAO.Database
Dim arr() As Long
Dim i As Long
Dim s As String
Dim strName As String
Dim dblWeight As Double
Set db = CurrentDb
Dim bm As Variant
Set rs = db.OpenRecordset("select [id], [name], [weight (kg)] from table1 order by 3;", dbOpenSnapshot)
With rs
ReDim arr(0)
arr(0) = -1
If Not (.BOF And .EOF) Then .MoveFirst
Do While Not .EOF
If Not InArray(!id, arr) Then
strName = ![Name]
dblWeight = ![weight (kg)]
s = ![Name] & " - " & ![weight (kg)]
bm = .Bookmark
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = !id
Do While Not .EOF
.MoveNext
If Not .EOF Then
If strName <> !Name And Not InArray(!id, arr) Then
s = s & " and " & ![Name] & " - " & ![weight (kg)]
ReDim Preserve arr(UBound(arr) + 1)
arr(UBound(arr)) = !id
Exit Do
End If
End If
Loop
db.Execute ("Insert Into table2 ([Nearest Weight]) SELECT '" & s & "';")
.Bookmark = bm
End If
.MoveNext
Loop
.Close
End With
Set rs = Nothing
Set db = Nothing
End Sub
Private Function InArray(value As Long, aray As Variant) As Boolean
Dim i As Long
For i = LBound(aray) To UBound(aray)
If value = aray(i) Then
InArray = True
Exit For
End If
Next
End FunctionSabi ko na nga ba pinoy.. sir pano ba kita ma private message? Trial and error plang kc ako.. bka may follow-up question pa ko... maraming salamat po..
