Select first record in linked table

[mtagliaferri]

I have a query containing two tables "tblContacts" and "tblTimeline" the table timeline has various records relating to the contact timeline, I need to run the query showing all contacts but only the first record on the timeline table.
How can I achieve this?
Thanks

 

[plog]

You will need a subquery. You didn't provide field names so I let's say you have ContactID which is the primary key in tblContacts and a foreign key to tblTimeline. Additionally tblTimeLine has a ContactTime field with the date which determines the 'first' record.

You would create a query on tblTimeline like so:

SELECT ContactID, MIN(ContactTime) AS FirstContact From tblTimeline;

Call that query 'subFirstContact'. It identifies the record you want from tblTimeline for each contact. You can then build your query.

You would bring in tblContacts, tblTimeLine and subFirstContact. Link tblContacts to subFirstContact via ContactID fields, then link subFirtContact to tblTimeline via ContactID fields and FirstContact to ContactTime. Bring down all the fields you want to show.

 

[llkhoutx]

Remember Access tables are not specifically ordered, but order row comes form column characteristics. Of course, every table has a first row, i.e. a first dataset row, it may not be what you expect.

What are you expecting in the first dataset row?

 

[mtagliaferri]

I am a bit confused, here is the query I currently have, Basically I need a list with certain information for every person and the date of joining of this person which the "TimelineDate" is not a date format but a text format as the information could be either dd/mm/yyyy or mm/yyyy; therefore the first record of each individual in the timeline table will be the date of joining. So the query should return the list of all contact and the first record of the timeline which is the date of joining.
Hope this makes sense!!

SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base, tblCrewMember.Nationality, tblCrewMember.Resined, tblCrewMember.DOB, tblCrewMember.IDCrewMember, tblCrewMember.Gender, tblCrewMember.CrewType, tblCrewMember.CallSign, tblCrewMember.MaidenName, tblCrewMemberTimeline.TimelineDate
FROM tblCrewMember INNER JOIN tblCrewMemberTimeline ON tblCrewMember.IDCrewMember = tblCrewMemberTimeline.IDCrewMember
ORDER BY tblCrewMember.StaffNumber;

 

[mtagliaferri]

Can I modify the INNER JOIN to select only the first record in the timeline table or I am getting the concept completely wrong?

 

[jdraw]

You might consider "cleaning up" the data. That is take advantage of the date/time data type. I'm not saying you should, but if it is something you use for related purposes, then using the date data type in queries /forms /reports may make things easier and consistent.

You could investigate the CDate() function. I don't think modifying the Inner Join is where I'd start.
Did you try using plog's approach as per post#2?

 

[mtagliaferri]

Thanks jdraw, the problem is that i cannot use the date format as the field is an uncertain value and can be recorded in details dd/mm/yy but sometimes the exact date is not known so the value might be moe generic as mm/yy hence using a text format.
This is why I need the first record of the linked table as I am sure that will be the record I need.
The query I have now returns duplicate records from the table contact as many as the entries in the timeline example:
name surname Date
carl john 10/2010
carl john 01/2012
carl john 05/2013
louise smith 01/2007
louise smith 02/2008
adam leaf 07/2009
adam leaf 03/2011
adam leaf 07/2015

What I need is:
carl john 10/2010
louise smith 01/2007
adam leaf 07/2009

 

[jdraw]

Did you look a plog's post? Does it apply to your set up?

So what does 07/2015 mean to you? July 2015 --what if it was 01 Jul 2015?
Since you don't always record day, any day should be OK or (why not?)

What readers are saying is - there is no inherent order in a table. If you wan the oldest Date,or the newest Date it could be done. But first and last are really meaningless generally. If you need Order , you build a query and use Order By.

Can you show us the designs of the tables involved?

 

[plog]

AI isn't here yet. You can't just feed the computer data and have it make sense of it. You have to explicitly tell it how to make sense of it.

What you have done with your date data and what you want to achieve with that data is incongruous and ambigous. You can't sometimes treat a field like text and other times treat it like dates and expect it to all sort correctly.

Suppose you had these 3 "date" values:

7/2/2010
7/2010
7/30/2010

I know the first one comes before the third one, but I have no idea where to place that second value.

Your initial issue is all about order and you don't have a valid way to apply order to your table because some of your "dates" are dates and some are strings. You need to clean up your data.

 

[arnelgp]

SELECT tblCrewMember.StaffNumber, tblCrewMember.Surname, tblCrewMember.Name, tblCrewMember.Position, tblCrewMember.Base,tblCrewMember.Nationality , tblCrewMember.Resined, tblCrewMember.DOB, tblCrewMember.IDCrewMember, tblCrewMember.Gender, tblCrewMember.CrewType, tblCrewMember.CallSign, tblCrewMember.MaidenName, (SELECT TOP 1 TimelineDate FROM (SELECT TimeLineDate From tblCrewMemberTimeline WHERE tblCrewMemberTimeline.IDCrewMember=tblCrewMember.IDCrewMember ORDER BY TimeLineDate ASC)) From tblCrewMember ORDER BY tblCrewMember.StaffNumber;

 

[mtagliaferri]

I have attached a simplified version of the DB, hope it can make sense on what I am trying to achieve, I need to display only the FIRST record which is contained in the tblCrewMemberTimeline linked to tblCrewMember which will ALWAYS be the Date Of Joining all the other records are subsequent records with no interest. The Date Field is just a text field to support the various formats.
The pic is a simulation of what the query should return

 

[plog]

I need to display only the FIRST record which is contained in the tblCrewMemberTimeline

You keep saying this and we keep telling you that there is no FIRST in a table. Tables have no order.

There is no first record in a table. No next, no prior, no 532nd record and no last record. Tables are just big buckets of memory that records get thrown into willy-nilly. Order exists on data only when you explicitly tell the computer to apply order to your data.

So you need to tell us more logically how to pull the data you want--without using any order terms unless you tell us how to apply said order. If you had a classroom of kids you could order them in a lot of ways--by height, by age, by grade point, alphabetically by name. You can't just say 'I want the first kid' because that makes no sense without how they are ordered. 'I want the first kid by height, starting with the tallest' does makes sense.

You need to tell us how to order your data to determine first. Let me cut you off before you say 'By the Date field' because that won't work. Your 'Date' field isn't a date. Its text that's formatted to look like a date. If you would like to order by the value that is intended to be in that field, you need to clean up your data. See my previous post about the ambiguouty of records like '7/2014'.

 

[sneuberg]

Does this table have an Autonumber? If so maybe that could be used to establish which is first.

 

[mtagliaferri]

Plog: Now I understand what you mean!!!
Sneuberg: IDTimelineCrewMember is a autonumber qould I be able to use that as a reference? the lowest number per each linked record will certainly the data I need to extract, is there a way round with this condition?

 

[jdraw]

Autonumbers are guaranteed to be unique---Not necessarily positive nor sequential.

If you want to find first or last you should be using Min or Max, and preferably a Date data type.(oldest/newest; earliest/latest)

 

[sneuberg]

jdraw, I'm curious. How could Autonumbers get out of sequence or become negative? I never seen this happen.

 

[mtagliaferri]

How can I use this on my SQL? bare with me as I am a novel with this

 

[jdraw]

Autonumbers

 

[sneuberg]

I've attached a modification of your database which contains the query qryCrewMembersDateOfJoining which at the moment seems to produce what you want. The query uses the query qryMinAutoNumberOfCrewMemberTimeLine which get the lowest Autonumber for any given IdCrewMember. You can use these query as a models to do this right after you change Date field into a date type and re-enter the data. With so few records it would not make sense to rely on the Autonumber which according to the reference provided by jdraw might somehow be screwed up by a compact and repair.

 

[mtagliaferri]

That's exactly what I was trying to get to!!!