selecting two records

[mtagliaferri]

I have a staff members table with a related timeline table (one to many), this timeline table contains various records related to the staff member. I have a query that creates a list of staff members and the first records in the timeline table which is the Date Of Joining as follows:

SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMemberTimeline.TimelineDate, tblCrewMember.Resigned, tblCrewMember.ResignedDate, tblCrewMember.DOB, tblCrewMember.Gender, tblCrewMember.CrewType, tblCrewMember.CallSign, tblCrewMember.MaidenName
FROM (qryCrewMemberTimelineFirstRecord INNER JOIN tblCrewMemberTimeline ON qryCrewMemberTimelineFirstRecord.MinOfIDTimelineCrewMember = tblCrewMemberTimeline.IDTimelineCrewMember) INNER JOIN tblCrewMember ON qryCrewMemberTimelineFirstRecord.IDCrewMember = tblCrewMember.IDCrewMember
ORDER BY tblCrewMember.StaffNumber;

This works perfectly, however I would like to have in the list also the Resigned record from the timeline table related to the staff member; would it be possible to select two records from the timeline table which contains the words "Date Of Joining" and "Resigned" from the "TimelineDescription" table, bearing in mind that "TimelineDescription" may contain other words as Resigned from...and it would need to pick this records because it contains the word resigned
Hope it makes sense

 

[llkhoutx]

It's not clear if you want one query or merely a second on.

If two, take a look at "Union" queries.

Additionally look at "SELECT TOP 2 etc."

 

[Uncle Gizmo]

If you provided a lookup table to give you the choices "Resigned" "Resigned from" and any other entries that are required you would eliminate the problem of having complicated code to identify the correct entry. You also leave yourself open to the problem that someone may not spell "Resigned" correctly they might spell it like this "Resignd" they might put "Left" "fed up and got another job". So I would recommend that you have a look up table and then you would store the lookup reference number (ID) instead of the text.

ID ....... "TimelineDescription"
1 ----------- "Date Of Joining"
2 ----------- "Resigned Reason"
3 ----------- "Resigned From"
4 ---------- "Resigned"

 

[smig]

you get your FirstTimLine using the qryCrewMemberTimelineFirstRecord query.
Create similar one that will get the LastTimLine and join it to your query, the same way.
You don't need the tblCrewMemberTimeline table in your query as you have the DateOfJoining in qryCrewMemberTimelineFirstRecord

 

[arnelgp]

SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMemberTimeline.TimelineDate, tblCrewMember.Resigned, tblCrewMember.ResignedDate, tblCrewMember.DOB, tblCrewMember.Gender, tblCrewMember.CrewType, tblCrewMember.CallSign, tblCrewMember.MaidenName, IIF(Instr(tblCrewMemberTimeLine.TimeLineDescription & "","Date Of Joining")>0, tblCrewMemberTimeLine.TimeLineDescription,"") As [Date Of Joining],IIF(Instr(tblCrewMemberTimeLine.TimeLineDescription & "","Resigned")>0, tblCrewMemberTimeLine.TimeLineDescription,"") As [Resigned]
FROM (qryCrewMemberTimelineFirstRecord INNER JOIN tblCrewMemberTimeline ON qryCrewMemberTimelineFirstRecord.MinOfIDTimelineCrewMember = tblCrewMemberTimeline.IDTimelineCrewMember) INNER JOIN tblCrewMember ON qryCrewMemberTimelineFirstRecord.IDCrewMember = tblCrewMember.IDCrewMember
ORDER BY tblCrewMember.StaffNumber;

 

[mtagliaferri]

Thanks smig, but just realised there is a fault in my logic as the staff member that is still employed will not have Resigned in the timeline so I will be picking on records that give generic info.

arnelgp:
I have copied your code but I get the following error:
Syntax error (missing operator) in query expression 'IIF(Instr(tbiCrewMemberTimeUne.TimeUneDescriptio n & -~ "Da te Of Joiningj >O, tbiCrewMemberTimeUne.TimeUneDescription1 j ',

 

[arnelgp]

that is because after i posted the code, the words get broken, ie: Description becomes Descriptio n

 

[mtagliaferri]

the query works partially, what I need is to return the value "date" for those records that are marked date of joining, and the value date for those are marked resigned

 

[arnelgp]

IIF(Instr(tblCrewMemberTimeLine.TimeLineDescription & "","Date Of Joining")>0, tblCrewMemberTimeLine.TimeLineDate,null) As [Date Of Joining],IIF(Instr(tblCrewMemberTimeLine.TimeLineDescription & "","Resigned")>0, tblCrewMemberTimeLine.TimeLineDate,null) As [Resigned]

 

[mtagliaferri]

Bizarre.... the code

IIf(InStr(tblCrewMemberTimeLine.TimeLineDescription & "","Date Of Joining")>0,tblCrewMemberTimeLine.TimeLineDate,Null) AS [Date Of Joining]

is working as it is returning a value
but the code

IIf(InStr(tblCrewMemberTimeLine.TimeLineDescription & "","Resigned")>0,tblCrewMemberTimeLine.TimeLineDate,Null) AS [Resigned]

does not return any value.
I have checked the table and there are records with 'Resigned' word present and speed correctly, why are there no values returned?