Selecting Unique Records from Child Tables

[Guest]

Hi, I'm quite new to using the Access interface although I've worked on
bespoke databases before.

I have data on trainees (trainee table) with a child table holding their
career information - all the posts they undertake during their training, with
start and end dates, location etc (career table).

I'd like to select all trainees with a particular criteria and include some
info from the latest record (indicated by "career end date") in the career
table also - but only succeed in pulling out all the records - eg if a
trainee has 4 career entries, I get 4 lots of data.

It was easy in the bespoke database interface as there was a "child record"
function to specify which you wanted to include - but I'm struggling! Someone
here suggested a DISTINCT statement but that didn't work (although I may have
been using it wrongly I suppose).

Can anyone help please?

Thanks in advance.

 

[Guest]

For the Child record I would make a query and pull the foreign key feild that
relates to the parent table and date feild. Make it a summing query and group
on the foreign key and max value for the date feild. Then create another
query using the parent table and this query with a relationship between the
primary key of the parent table and the foreign key of the query

 

[Guest]

To do this with a single query you'll need to use a subquery for this which
means writing a little bit of SQL, e.g.

SELECT Firstname, LastName,
StartDate, EndDate, Company
FROM Trainee INNER JOIN Career
ON Trainee.TraineeID = Career.TraineeID
WHERE StartDate =
(SELECT MAX(StartDate)
FROM Career
WHERE Career.TraineeID = Trainee.TraineID);

This will restrict the rows returned by the outer query to those where the
StartDate for the current trainee equals the latest StartDate in the Career
table for that trainee. Using the start date rather than the end date to
correlate the outer and subqueries makes sure that even if a trainee hasn't
yet finished the last placement in the Career table, in which case the end
date would be NULL, the details for that placement will be returned. If you
only wanted details form the last 'completed' placement you'd correlate them
on the enddate.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks both for responding. I'll try these out. Ta!