Pick most recent but present in a column not rows (1 Viewer)

FrostByte

Registered User.
Local time
Today, 09:18
Joined
Jan 15, 2015
Messages
56
Hi,

In a one to many situation, i wanted to pull a data set that will give me the last 3 results but present in 3 columns rather than 3 rows.

So my example would be...

Reference Note1 Note2 Note3
XXX Text Text Text

Now I know I could have a formula rather than the field but I don't know what the formula would look like to pick 1st then 2nd then 3rd note.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:18
Joined
May 7, 2009
Messages
19,170
is it similar to union query?
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:18
Joined
May 7, 2009
Messages
19,170
select top 3 * from table where note1 = note2 and note2 = note3 and note3 = "TheText"
 

FrostByte

Registered User.
Local time
Today, 09:18
Joined
Jan 15, 2015
Messages
56
Hi Arnelgp,

The note field isn't 3 fields, so I'm looking to use a formula that picks row1, row2 and row3 and presents them in a separate column rather than 3 rows
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:18
Joined
May 7, 2009
Messages
19,170
create a function in a Module:
Code:
Public Function nextRow(pkField As String, pkValue As Variant, ByVal idx As Integer, fldName As String) As Variant

    'replace doc with your TableName
    Const TABLE_NAME As String = "doc"
    
    Dim DB As DAO.Database
    Dim RS As DAO.Recordset
    Dim i As Integer
    Dim sCriteria
    Select Case VarType(pkValue)
    Case vbString
        pkValue = "'" & pkValue & "'"
    Case vbDate
        pkValue = "#" & Format(pkValue, "MM/DD/YYYY") & "#"
    End Select
    sCriteria = BuildCriteria(pkField, vbInteger, pkValue)
    Set DB = CurrentDb
    Set RS = DB.OpenRecordset("SELECT TOP 3 [" & fldName & "] FROM [" & TABLE_NAME & "] WHERE " & sCriteria & ";", dbOpenSnapshot)
    With RS
        If Not (.BOF And .EOF) Then
            .MoveFirst
        End If
        Do Until .EOF
            i = i + 1
            If i = idx Then
                nextRow = .Fields(0)
                Exit Do
            End If
            .MoveNext
            If .EOF Then
                Exit Do
            End If
        Loop
        .Close
    End With
    Set RS = Nothing
    Set DB = Nothing
End Function

your query:

SELECT table1.Reference, nextRow("Reference",[Reference],1,"NoteField") AS Note1, nextRow("Reference",[Reference],2,"NoteField") AS Noter2, nextRow("Reference",[Reference],3,"NoteField") AS Note3
FROM table1
GROUP BY table1.Reference, nextRow("Reference",[Reference],1,"NoteField"), nextRow("Reference",[Reference],2,"NoteField"), nextRow("Reference",[Reference],3,"NoteField");
 

bastanu

AWF VIP
Local time
Today, 02:18
Joined
Apr 13, 2010
Messages
1,401
I think you need a Top 3 query sorting the date field descending then build a crosstab on that to transpose the records into columns.

Cheers,
 

jdraw

Super Moderator
Staff member
Local time
Today, 05:18
Joined
Jan 23, 2006
Messages
15,364
I recommend you post your current work/ database and/or related file(s) to give readers/responders something to work with.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:18
Joined
May 7, 2009
Messages
19,170
sample demo again.
see Table1, see Query1.
unfortunately the query is not updateable.
 

Attachments

  • NotesAsColumn.accdb
    512 KB · Views: 134
Last edited:

Users who are viewing this thread

Top Bottom