Find Previous Record field data, or better suggestion

Anonymous
2020-01-13T19:45:54+00:00

I am trying to find the previous record value/field in a query to apply conditional formatting to my form. I have been researching using LAG function, but just can't make it work for what I need or am just not doing it right. 

What I have is a form that lists(and more): 

SERIES - TITLE

Series1 - Title1

Series1 - Title2

Series2 - Title3

Series3 - Title4

Series3 - Title5

Series3 - Title6

and I want to add a conditional format to hide the series values that repeat from the record above to show: 

SERIES - TITLE

Series1 - Title1

            - Title2

Series2 - Title3

Series3 - Title4

            - Title5

            - Title6

I tried to use the LAG Function, but dont think i'm adding it correctly.

Then i also found the below code but that just returns #NAME in the field. 

Function PrevRecVal(F As Form, Keyname As String, KeyValue, FieldNameToGet As

String)

End Function

    Dim RS As Recordset

On Error GoTo Err_PreRecVal

    'The default value is zero

    PrevRecVal = 0

    'Get the form recordset.

    Set RS = F.RecordsetClone

    'Find the current record.

    Select Case RS.Fields(ID).Type

        'Find using numeric data type key value?

        Case DB_INTEGER, DB_LONG, DB_CURRENCY, DB_SINGLE, DB_DOUBLE, DB_BYTE

            RS.FindFirst "[" & KeyName & "]=" & KeyValue

        'Find using date data type key value?

    Case DB_DATE

            RS.FindFirst "[" & KeyName & "]=#" & KeyValue & "#"

    Case DB_DATE

            RS.FindFirst "[" & KeyName & "]='" & KeyValue & "'"

    Case Else

            MsgBox "Error: Invalid key field data type!"

    End Select

        'Move to the previous record.

        RS.MovePrevious

        'Return the result

        PrevRecVal = RS(SeriesID)

Bye_PrevRecVal:

        Exit Function

Err_PrevRecVal:

        Resume Bye_PrevRecVal

        End Function

(in the control source of the field to show the previous record)

= PrevRecVal([Form],"SeriesID",[SeriesID],"SeriesID"

can anyone tell me 1)which one is better to use(above code or a lag function), 2)what im doing wrong or how to add a lag function if that is what i need here, 3)is there a better/easier solution you can suggest instead?Help! THANK YOU!

Microsoft 365 and Office | Access | For home | Windows

Locked Question. This question was migrated from the Microsoft Support Community. You can vote on whether it's helpful, but you can't add comments or replies or follow the question.

0 comments
Answer accepted by question author
Anonymous
2020-01-15T22:31:40+00:00

Let's confine it to one table to start with:

SELECT T1.SeriesID,

IIF(COUNT(*) = 1,T1.Title,"") AS Title,

IIF(COUNT(*) = 1,T1.SeriesPart,"") AS SeriesPart,

FROM TitleT AS T1 INNER JOIN TitleT AS T2

ON T2.SeriesID = T1.SeriesID

AND T2.Title <= T1.Title

GROUP BY T1.SeriesID, T1.Title, T1.SeriesPart

ORDER BY T1.SeriesID, T1.Title;

If that returns the correct result table then you can add the SeriesT table to the query and return the Series column rather than the SeriesID:

SELECT SeriesT.Series,

IIF(COUNT(*) = 1,T1.Title,"") AS Title,

IIF(COUNT(*) = 1,T1.SeriesPart,"") AS SeriesPart,

FROM SeriesT INNER JOIN (TitleT AS T1 INNER JOIN TitleT AS T2

ON T2.SeriesID = T1.SeriesID AND T2.Title <= T1.Title)

ON SeriesT.SeriesID = T1.SeriesID

GROUP BY SeriesT.Series, T1.Title, T1.SeriesPart

ORDER BY SeriesT.Series, T1.Title, T1.SeriesPart;

You can of course include the SeriesID and TitleID columns in the SELECT and GROUP BY clauses if you wish, but usually these would be surrogate keys with arbitrary values of no semantic significance.

Was this answer helpful?

1 person found this answer helpful.
0 comments

48 additional answers

Sort by: Newest
Most helpful Newest Oldest
  1. Anonymous
    2020-01-17T20:09:02+00:00

    Just for my own sanity, i created a query in design mode and got the below SQL. This is exactly what i need EXCEPT that the UniverseGrouping field should be the one that uses the iif(count(*)=1 

    Also, this is using Right join again instead of Inner.

    SELECT UniverseT.Universe, SeriesT.Series, IIf(IsNull([Universe]),[Series],[Universe]) AS UniverseGrouping, IIf(IsNull([Series]),[Title],([Series] & " " & Format([SeriesPart],"000") & " • " & [Title])) AS FullTitleNUM, IIf(IsNull([Universe]),([Series] & " " & [SeriesPart] & " • " & [Title]),IIf(IsNull([Series]),[Universe] & " " & [UniverseOrder] & " • " & [Title],([Universe] & " " & [UniverseOrder] & " • " & [Series] & " " & [SeriesPart] & " • " & [Title]))) AS FullTitle, TitleT.Title, TitleT.TitleID

    FROM UniverseT RIGHT JOIN (SeriesT RIGHT JOIN TitleT ON SeriesT.SeriesID = TitleT.SeriesID) ON UniverseT.UniverseID = TitleT.UniverseID

    WHERE (((SeriesT.Series) Is Not Null)) OR (((UniverseT.Universe) Is Not Null))

    ORDER BY IIf(IsNull([Universe]),([Series] & " " & [SeriesPart] & " • " & [Title]),IIf(IsNull([Series]),[Universe] & " " & [UniverseOrder] & " • " & [Title],([Universe] & " " & [UniverseOrder] & " • " & [Series] & " " & [SeriesPart] & " • " & [Title])));

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-01-17T19:49:17+00:00

    Ken, can I ask for a bit more assistance?

    I need to add another grouping now that could be grouped by series (as you have shown) OR universe. i thought i had it figured out with the below sql but now it only shows the titles that have BOTH. if i use OR in the from it doesnt show either series or universe counts, if i use AND it shows them both.

    SELECT IIF(COUNT(*)=1,SeriesT.Series,"") AS SeriesGroup, IIF(COUNT(*)=1,UniverseT.Universe,"") AS UniverseGroup, T1.Title AS Title, T1.SeriesPart, IIf(IsNull([SeriesT.Series]),[T1.Title],([SeriesT.Series] & " " & [T1.SeriesPart] & " • " & [T1.Title])) AS FullTitle

    FROM SeriesT INNER JOIN (UniverseT INNER JOIN (TitleT AS T1 INNER JOIN TitleT AS T2 ON ((T2.SeriesID = T1.SeriesID) OR (T2.UniverseID = T1.UniverseID)) OR (T2.Title <= T1.Title)) ON UniverseT.UniverseID = T1.UniverseID) ON SeriesT.SeriesID = T1.SeriesID

    GROUP BY SeriesT.Series, UniverseT.Universe, T1.Title, T1.SeriesPart

    ORDER BY UniverseT.Universe, SeriesT.Series, T1.Title, T1.SeriesPart;

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-01-16T14:48:34+00:00

    Thank you Ken!

    I had RIGHT join instead of INNER, and breaking it down like you did really helped me to understand what was happening. Thank you! I was able to adjust and is now exactly what I was looking for it to do!

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-01-15T18:49:59+00:00

    The Series should be ordered by Series,SeriesPart. The Title is only on the TitleT so there's nothing to compare it to and there are no duplicates. 

    I have a table for all titles which includes a field to select a series ID, then another table (SeriesT) that only has the SeriesID and Series name fields. 

    I have the below code which already lists and orders the Series and Titles, i just cant seem to figure out the last part of that JOIN to make the IIF function work and make the values not visible. 

    SELECT TitleT.TitleID, TitleT.Title, TitleT.SeriesID, TitleT.SeriesPart, SeriesT.SeriesID, SeriesT.Series

    FROM SeriesT RIGHT JOIN TitleT ON SeriesT.SeriesID = TitleT.SeriesID

    WHERE (((SeriesT.SeriesID) Is Not Null)) ORDER BY SeriesT.Series, TitleT.SeriesPart;

    you suggested the Title would need to be used for the JOIN in place of your TransactionID <= join, what else could it be matched to?

    Was this answer helpful?

    0 comments