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: Most helpful
Most helpful Newest Oldest
  1. Anonymous
    2020-01-15T17:56:04+00:00

    It's the IIF function call which determines whether the value in the second column is visible or not, e.g. in my example:

        IIF(COUNT(*) = 1, FirstName & " " & LastName,"") AS Customer

    As this is dependant on the use of the COUNT operator the query has to be grouped in such a way to return serial numbers form 1 upwards per Series value in your case.  So you have to replicate my JOIN of the two instances of the table (SeriesT in your case) to achieve what you are attempting.  I described the basis for this in my last post, though the current arbitrary  substitution of asterisks is confusing things a little.  My last paragraph should have read:

    So, in your case you need to identify a column per Series by which the results are to be ordered.  This appears to be Title from the sample data you posted, so this would be used in the JOIN in place of TransactionID in my example, joined on <= .  If there can be duplicates of the Title value per Series value you'd need to add the tie breaker expression to the JOIN, otherwise not.  Series is analogous to CustomerID in my example, so the JOIN would be on these being equal.

    Was this answer helpful?

    0 comments
  2. Anonymous
    2020-01-15T14:30:29+00:00

    So i got part of it worked out...

    SELECT TitleT.Title, TitleT.SeriesID, TitleT.SeriesPart, SeriesT.SeriesID, SeriesT.Series AS SeriesGroup

    FR****eT.SeriesID

    WHERE (((SeriesT.SeriesID) Is Not Null)) 

    ORDER BY SeriesT.Series, TitleT.SeriesPart;

    and that gives me the columns i want, but the extra series names are still visible. If i try t****es,"") AS SeriesGroup

    OR if i try to add in the GROUP BY part it tells me that "your query does not include the specified expression 'Title' as part of an aggregated function"

    Was this answer helpful?

    0 comments
  3. Anonymous
    2020-01-15T12:30:20+00:00

    The number of tables is not really material.  The JOIN is rather more complex, however.  If you look at my example, the two instances of the Transactions table are essentially joined on the CustomerID values being equal and the TransactionDate in one instance being equal to or less than the TransactionDate in the other.  The result table is ordered by  CustomerID, and within each customer group by TransactionDate. The COUNT consequently returns the number of rows per customer before or equal to the current row, so the rows are numbered incrementally per customer.

    However, there might be two or more transactions on the same date, in which case the same rows would get the same number, i.e. they'd be ranked per customer rather than numbered sequentially.  To ensure that all rows per customer are numbered sequentially the primary key TransactionID is brought in as the tie breaker, using the following expression in the JOIN:

    (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)

    In a situation where there can be no ties this can be omitted from the JOIN.

    So, in your case you need to identify a column per Series by which the results are to be ordered.  This appears to be Title from the sample data you posted, s****ace ****f there can be duplicates of the Title value per Series value you'd need to add the tie breaker expression to the JOIN, otherwise n****e, so the JOIN would be on these being equal.

    Was this answer helpful?

    0 comments
  4. Anonymous
    2020-01-14T16:30:13+00:00

    Thanks Ken, That's basically what I'm looking for. Now i just have to adjust for my items. This is a BIT confusing as i am only working with 2 tables to join instead of 3 and I'm getting a JOIN syntax error (never used ON before so thats new :) )

    Here's what I changed it to: 

    SELECT Title, SeriesPart, Series

    FROM TitleT INNER JOIN(SeriesT ON TitleT.SeriesID=SeriesT.SeriesID) 

    GROUP BY SeriesID, 

    ORDER BY Series,SeriesPart;

    Here's the original: 

    SELECT IIF(COUNT(*) = 1, FirstName & " " & LastName,"") AS Customer,

    T1.TransactionDate, T1.TransactionAmount

    FROM Customers INNER JOIN (Transactions AS T1 INNER JOIN Transactions AS T2

    ON (T2.TransactionID<=T1.TransactionID OR T2.TransactionDate<>T1.TransactionDate)

    AND (T2.TransactionDate<=T1.TransactionDate) AND (T2.CustomerID=T1.CustomerID))

    ON Customers.CustomerID = T1.CustomerID

    GROUP BY LastName, FirstName, FirstName & " " & LastName, T1.TransactionDate,

    T1.TransactionAmount, T1.TransactionID

    ORDER BY LastName, FirstName, T1.TransactionDate;

    Was this answer helpful?

    0 comments