[AccessD] XPOSTED Where to put a query.

Assume tblEmployees has 1 row, tblTitles has 3 rows, and 
tblEmployeeTitles has one row:

EmpID 123
TitleID 1, 2, 3
EmpTitleID 1, EmpID 123, TitleID 1

You want to populate the combo for the associative table by "minus-ing" 
the titles already assigned. There are several ways to do this, and I 
don't profess to know which would best suit you or the size of your 
database tables....

SELECT TitleID, TitleName
FROM tblTitles
WHERE TitleID NOT IN( SELECT TitleID FROM tblEmpoyeeTitles WHERE EmpID = 
123 )

SELECT TitleID, TitleName
FROM tblTitles OUTER JOIN tblEmployeeTitles ON tblTitles.TitleID = 
tblEmployeeTitles.TitleID
WHERE tblEmployeeTitles.TitleID IS NULL

You might want to add an ORDER BY TitleName to each of these queries.

Arthur





Joe Hecht wrote:

>I am working on an inherited database.
>
> 
>
>The original database there was a 1->1 relationship between employees and
>job titles.
>
> 
>
>At the request of the client, I have made a 1->many relationship between
>employees and job titles.
>
> 
>
>This sql was the after update to the form:
>
> 
>
>INSERT INTO TrainDate ( EmpID, DocID )
>
>SELECT Employee.EmpID, JobDoc.DocID
>
>FROM Employee INNER JOIN JobDoc ON Employee.JobTitleID = JobDoc.JobTitleID
>
>WHERE (((Employee.EmpID)=[Forms]![Employees]![EmpID]) AND
>((Employee.JobTitleID)=[JobDoc].[JobTitleID]));
>
> 
>
>I need to know please:
>
>          1. Where do I put the sql statement to check each record on the
>subform.
>
>          2. How do I ensure it does not add duplicate records if a
>potentional situation
>
>                   a. existing employee is in the system
>
>                   b. if they are assigned a second or more job title 
>
> 
>
>Hope this is clear.
>
> 
>
>TIA
>
> 
>
>JOE HECHT
>
>LOS ANGELES CA 
>
>jmhla at earthlink.net
>
> 
>
>  
>


-- 
No virus found in this outgoing message.
Checked by AVG Anti-Virus.
Version: 7.0.289 / Virus Database: 265.4.1 - Release Date: 11/19/2004