Returning First (or just one) Record

[Don]

Sorry if this is a duplicate, but a search of my notes and a Google groups
did not turn up the question or any answers.

Basically, I have a table with 4 fields:

EmployeeID Unique ID to link back to the employee
EMailAddress An e-mail address for the employee
Primary Boolean indicating if this EMail is the primary
e-mail address
Comments Misc comments relative to that e-mail address

An employee could have several e-mails and therefore several entries in the
e-mail table. Only one of the e-mails should be flagged "Primary" (i.e.
Primary = TRUE). Unfortunately, for a number of reasons, the table now has
several cases where 'Primary' is TRUE for 2 or more of a single employee's
e-mails.

What I am trying to do is develop a query which will return only one of the
e-mails marked "Primary" if there is more than one. Not surpringsly, my
starting point is:

SELECT tblEmail.EmployeeID, tblEmail.Primary, tblEmail.EMail
FROM tblEmail
WHERE (((tblEmail.Primary)=True));

which returns all the 'Primary' e-mails for an employee. I have tried
incorporating the FIRST function, but have not had any success.

Any suggestions or thoughts will be greatly appreciated!

Thanks!

Don

 

[Dave Patrick]

What happens if you;
SELECT TOP 1 tblEmail.EmployeeID, tblEmail.Primary, tblEmail.EMail
FROM tblEmail
WHERE (((tblEmail.Primary)=True));

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Sorry if this is a duplicate, but a search of my notes and a Google groups
| did not turn up the question or any answers.
|
| Basically, I have a table with 4 fields:
|
| EmployeeID Unique ID to link back to the employee
| EMailAddress An e-mail address for the employee
| Primary Boolean indicating if this EMail is the primary
| e-mail address
| Comments Misc comments relative to that e-mail address
|
| An employee could have several e-mails and therefore several entries in
the
| e-mail table. Only one of the e-mails should be flagged "Primary" (i.e.
| Primary = TRUE). Unfortunately, for a number of reasons, the table now
has
| several cases where 'Primary' is TRUE for 2 or more of a single employee's
| e-mails.
|
| What I am trying to do is develop a query which will return only one of
the
| e-mails marked "Primary" if there is more than one. Not surpringsly, my
| starting point is:
|
| SELECT tblEmail.EmployeeID, tblEmail.Primary, tblEmail.EMail
| FROM tblEmail
| WHERE (((tblEmail.Primary)=True));
|
| which returns all the 'Primary' e-mails for an employee. I have tried
| incorporating the FIRST function, but have not had any success.
|
| Any suggestions or thoughts will be greatly appreciated!
|
| Thanks!
|
| Don
|
|

 

[Don]

Dave,

The query returns just one record. But, I think you have given me an idea:
a subquery to return the "TOP 1" of e-mails for an employee. With the main
query returning the records for all employees.

Now I need to go back and review subqueries! :O

Thanks!

Don

 

[Dave Patrick]

You're welcome.

--
Regards,

Dave Patrick ....Please no email replies - reply in newsgroup.
Microsoft Certified Professional
Microsoft MVP [Windows]
http://www.microsoft.com/protect

:
| Dave,
|
| The query returns just one record. But, I think you have given me an
idea:
| a subquery to return the "TOP 1" of e-mails for an employee. With the
main
| query returning the records for all employees.
|
| Now I need to go back and review subqueries! :O
|
| Thanks!
|
| Don