Return only first record in set of records???

[zack]

This query has been driving me crazy, and would be grateful for a
nudge in the right direction. Still pretty much a beginner using
Access 2003, I just cannot figure the solution to the following in the
query design grid.

I have 4 tables:
tblApplicant Person
Person ID PK

tblCourses Applied
CourseAppID PK
Course ID FK
Person ID FK
Date Created

tblCompendium
Course ID PK
Course Name (text)

tblInterview
InterviewID PK
PersonID FK
Interview Outcome

As you would expect one applicant may apply for 1 or more courses.
However once interviewed they will be given an interview outcome based
their first choice of course. As they can only go on to enrol on one
course.

I need to able to create a query that returns the following fields;
Person ID
Course ID (but only for the first CourseAppID. EG their first choice
of couse)
Course Name
Interview Outcome (text)

I have tried using 'First' in the totals field of the QBE, but I dont
think this is the correct use of this. I would really appreciate a
nudge in the right direction.
Many thanks
Carl

[Allen Browne]

Use a subquery to select what their first choice is. You can then build
another query based on the results to get all the other fields you want.

The first query will be something like this (though I'm not clear how you
choose the "first" choice):

SELECT [tblApplicant Person].*,
(SELECT TOP 1 [Course ID]
FROM [tblApplication Person]
WHERE [tblApplication Person].[Person ID] =
[tblApplication Person].[Person ID]
ORDER BY [tblApplication Person].[Date Created],
[tblApplication Person].CourseAppID)
FROM [tblApplicant Person];

If subqueries are new, see:
http://allenbrowne.com/subquery-01.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"zack" <za******@yahoo .co.ukwrote in message
news:11******** ************@b7 5g2000hsg.googl egroups.com...

This query has been driving me crazy, and would be grateful for a
nudge in the right direction. Still pretty much a beginner using
Access 2003, I just cannot figure the solution to the following in the
query design grid.

I have 4 tables:
tblApplicant Person
Person ID PK

tblCourses Applied
CourseAppID PK
Course ID FK
Person ID FK
Date Created

tblCompendium
Course ID PK
Course Name (text)

tblInterview
InterviewID PK
PersonID FK
Interview Outcome

As you would expect one applicant may apply for 1 or more courses.
However once interviewed they will be given an interview outcome based
their first choice of course. As they can only go on to enrol on one
course.

I need to able to create a query that returns the following fields;
Person ID
Course ID (but only for the first CourseAppID. EG their first choice
of couse)
Course Name
Interview Outcome (text)

I have tried using 'First' in the totals field of the QBE, but I dont
think this is the correct use of this. I would really appreciate a
nudge in the right direction.
Many thanks
Carl

[GH]

Zack,

Depending on your exact result needs, you can accomplish your
requirement a couple of ways. If you really only want ONE record to
be returned, use the MIN([Date Created]), assuming this is the field
that determines which course was chosen as a first choice. If you want
all records, but need the first choice to be at the top, you can sort
your query results using the ORDER BY clause (Sort: if you are using
query designer). If you sort by [Date Created] Ascending, the
earliest date will be first in the record set. If you are also
pulling more than one individual in your results, just sort by the
[Person ID] then the [Date Created]. You can sort and limit your query
results by this date, even if you do not want the date to be part of
the query results by unchecking the Show checkbox for that field.
Hope this helps you solve your dilemma. Good luck!

- GH

On Mar 15, 7:06 am, "zack" <zack6...@yahoo .co.ukwrote:

This query has been driving me crazy, and would be grateful for a
nudge in the right direction. Still pretty much a beginner using
Access 2003, I just cannot figure the solution to the following in the
query design grid.

I have 4 tables:
tblApplicant Person
Person ID PK

tblCourses Applied
CourseAppID PK
Course ID FK
Person ID FK
Date Created

tblCompendium
Course ID PK
Course Name (text)

tblInterview
InterviewID PK
PersonID FK
Interview Outcome

As you would expect one applicant may apply for 1 or more courses.
However once interviewed they will be given an interview outcome based
their first choice of course. As they can only go on to enrol on one
course.

I need to able to create a query that returns the following fields;
Person ID
Course ID (but only for the first CourseAppID. EG their first choice
of couse)
Course Name
Interview Outcome (text)

I have tried using 'First' in the totals field of the QBE, but I dont
think this is the correct use of this. I would really appreciate a
nudge in the right direction.
Many thanks
Carl

[zack]

>
Depending on your exact result needs, you can accomplish your
requirement a couple of ways. If you really only want ONE record to
be returned, use the MIN([Date Created]), assuming this is the field
that determines which course was chosen as a first choice. If you want
all records, but need the first choice to be at the top, you can sort
your query results using the ORDER BY clause (Sort: if you are using
query designer). If you sort by [Date Created] Ascending, the
earliest date will be first in the record set. If you are also
pulling more than one individual in your results, just sort by the
[Person ID] then the [Date Created]. You can sort and limit your query
results by this date, even if you do not want the date to be part of
the query results by unchecking the Show checkbox for that field.
Hope this helps you solve your dilemma. Good luck!

- GH

>

This query has been driving me crazy, and would be grateful for a
nudge in the right direction. Still pretty much a beginner using
Access 2003, I just cannot figure the solution to the following in the
query design grid.

I have 4 tables:
tblApplicant Person
Person ID PK

tblCourses Applied
CourseAppID PK
Course ID FK
Person ID FK
Date Created

tblCompendium
Course ID PK
Course Name (text)

tblInterview
InterviewID PK
PersonID FK
Interview Outcome

As you would expect one applicant may apply for 1 or more courses.
However once interviewed they will be given an interview outcome based
their first choice of course. As they can only go on to enrol on one
course.

I need to able to create a query that returns the following fields;
Person ID
Course ID (but only for the first CourseAppID. EG their first choice
of couse)
Course Name
Interview Outcome (text)

I have tried using 'First' in the totals field of the QBE, but I dont
think this is the correct use of this. I would really appreciate a
nudge in the right direction.
Many thanks
Carl- Hide quoted text -

- Show quoted text -

Many thanks for the suggestions.
Your correct I am looking for 1 applicant, 1 course (their first
choice, using data created) and 1 interview outcome.
I am still stuck as 'Min' is still bringing back all the applicants
course choices.
Any other suggestions to move forward with this. for the sake of my
sanity HELP!!

[onedaywhen]

On Mar 15, 12:06 pm, "zack" <zack6...@yahoo .co.ukwrote:

I am looking for 1 applicant, 1 course (their first
choice, using data created) and 1 interview outcome.

How do you determine the applicant's first choice course? Shouldn't
there be a column in the tblInterview table to show this?

Jamie.

--

[GH]

Zack,

If I am understanding correctly, your query is missing one last
element, which is a parameter or that narrows down to the selected
applicant. If you are accessing the query through a form, the form
can filter on the Person ID. If you are manually inputting a person's
ID, you can just add a WHERE clause that prompts the user for Person
ID. A simplistic test of this is to add WHERE [Person ID] = ? and
Access will prompt the user to enter the value for ?, then only the
record for the entered person would be included in the recordset.

If the issue is actually that you are getting all records for a single
applicant, this could be due to the need to make sure your criteria
has the [Date Created] = to the Min([Date Created]). This will ensure
only one course record returns, the one with the minimum date.
Alternatively, you can use Allen Browne's suggestion because it pulls
only the first record, and sorting by date ensures the earliest date
is the first record.

- GH

>...
Many thanks for the suggestions.
Your correct I am looking for 1 applicant, 1 course (their first
choice, using data created) and 1 interview outcome.
I am still stuck as 'Min' is still bringing back all the applicants
course choices.
Any other suggestions to move forward with this. for the sake of my
sanity HELP!!- Hide quoted text -

- Show quoted text -

[GH]

I forgot to add that if the same create date is possible for a single
person, you could still get too many records using MIN, but you might
not necessarily get the correct record using Top 1 either if there is
no way to differentiate which course actually got created first.

- GH

On Mar 15, 8:06 am, "zack" <zack6...@yahoo .co.ukwrote:

>
...
Many thanks for the suggestions.
Your correct I am looking for 1 applicant, 1 course (their first
choice, using data created) and 1 interview outcome.
I am still stuck as 'Min' is still bringing back all the applicants
course choices.
Any other suggestions to move forward with this. for the sake of my
sanity HELP!!- Hide quoted text -

- Show quoted text -