select records with Unique values in certain fieldsth

[ootkhopdi]

Hi..
i have a database in which in one table having employee posting details..
like . where he works in which month or year..

table view as
month year posting place
jan 2016 A
feb 2016 A
march 2016 B
April 2016 B
.....
Nov 2016 C
..........
Feb 2017 A
.......
Sep 2017 C

as you see. employee changes his posting palaces..
so i want result as
Month Year posting Place
Jan 2016 A
March 2016 B
Nov 2016 C
Feb 2017 A
Sep 2017 C

it mean i want only that months when employee data value change in posting place..

thanks in advance

 

[arnelgp]

put this in a Standard Module:

NOTE: change the name of table and fields to
the correct name of fields and table of your database.

Public Function fnLocation() As String
    Dim db As dao.Database
    Dim rs As dao.Recordset
    Dim strlocation As String

    Set db = CurrentDb
    Set rs = db.OpenRecordset("select [Year] & [Month], [posting place] from yourTable;", dbOpenSnapshot)
    With rs
        If Not (.BOF And .EOF) Then .MoveFirst
        strlocation = rs(1)
        fnLocation = rs(0)
        .MoveNext
        While Not .EOF
            If strlocation <> rs(1) Then
                strlocation = rs(1)
                fnLocation = fnLocation & "/" & rs(0)
            End If
            .MoveNext
        Wend
        .Close
    End With
    Set rs = Nothing
    Set db = Nothing
End Function

now create a query based on your table:

SELECT yourTable.Month, yourTable.Year, yourTable.[posting place]
FROM yourTable where Instr(1,fnLocation(),[Year] & [Month])>0

 

[ootkhopdi]

Thanks to All but i can't Understand what is the code and where i post it...

please give me simple steps..

 

[arnelgp]

What is the actual Name of your Table and the Actual Names of the fields.
Problem, always is OPs don't give the real names of their table and fieldnames at first, but they want real solution at once.


If you can give the real names then when i use them in the code, you will likely to follow it and gives you idea of what the function is doing.

 

[plog]

LOL Ariel, I've noticed in the last couple of years, the interaction with OP's has deteriorated! It has become more like:- "I have this problem fix it and give me the solution"......Really it boils down to "our purpose" I don't think the purpose is to do people's work for them.

I agree, but I don't blame the initial posters entirely. Most of this lies at the feet of respondents who simply post undocumented code without trying to find out the big picture issue or determine what the initial posters has done. Or if the solution they seek is what they truly need.