finding unmatched records 2

[jadams0173]

I'm trying to find unmatched records in 2 tables. Should be pretty straightforward and I've done it numerous times but I'm having a "spell" on this one.

Table temp_req
jobnumber (text)
PreReq (text)

Temp Records
EmpNo (text)
CourseNo (text)


temp_req data
JobNumber	PreReq
100		0001
100		0002
100		0003
100		0004
100		0005


TempRecords
EmpNo		CourseNo
12345		0001
12345		0002
12345		0003

Expected Output
EmpNo   prereq  courseno
12345	0001	0001
12345	0002	0002
12345	0003	0003	
12345	0004	
12345	0005

my query that I have will return only the matched rows.

SELECT tblJobReq.PreReq, tblJobReq.JobNumber, TempRecords.CourseNo, TempRecords.EmpNo
FROM tblJobReq LEFT JOIN TempRecords ON tblJobReq.PreReq = TempRecords.CourseNo
GROUP BY tblJobReq.PreReq, tblJobReq.JobNumber, TempRecords.CourseNo, TempRecords.EmpNo
HAVING (((tblJobReq.JobNumber)="100") AND ((TempRecords.EmpNo)="12345"))

I tried to have patience but it took to long! -DW

 

[PHV]

The culprit is red flagged:
...
HAVING (((tblJobReq.JobNumber)="100") [!]AND ((TempRecords.EmpNo)="12345")[/!])

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

PHV,

Is there a workable solution aside from making 2 queries?

I tried to have patience but it took to long! -DW

 

[r937]

you don't need the GROUP BY, and the HAVING should be split up as follows:

SELECT tblJobReq.PreReq
     , tblJobReq.JobNumber
     , TempRecords.CourseNo
     , TempRecords.EmpNo
  FROM tblJobReq 
LEFT 
  JOIN TempRecords 
    ON TempRecords.CourseNo = tblJobReq.PreReq
   [b]AND[/b] TempRecords.EmpNo = "12345"
 [b]WHERE[/b] tblJobReq.JobNumber = "100"

r937.com | rudy.ca

 

[jadams0173]

thanks r937 I'll give this a try.

I tried to have patience but it took to long! -DW

 

[jadams0173]

r937,

When I try your solution I get a message stating:

Join Expression Not Supported.

I tried to have patience but it took to long! -DW

 

[r937]

darn, i keep forgetting how fragile jet sql is...

try this --

LEFT 
  JOIN TempRecords 
    ON (
       TempRecords.CourseNo = tblJobReq.PreReq
   AND TempRecords.EmpNo = "12345"
       )
 WHERE tblJobReq.JobNumber = "100"

r937.com | rudy.ca

 

[PHV]

SELECT J.PreReq, J.JobNumber, T.CourseNo, T.EmpNo
FROM tblJobReq AS J LEFT JOIN (
SELECT * FROM TempRecords WHERE EmpNo='12345'
) AS T ON J.PreReq = T.CourseNo
WHERE J.JobNumber='100'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[jadams0173]

PHV,

Your solution worked perfectly

r937,

Your solution also produced the desired results.

Stars to you both and another post for the archives!

Thanks very much to the both of you!!!

I tried to have patience but it took to long! -DW

 

[jadams0173]

To expand a little on this. I've added another table and now I get an error when I try to run the query from VB6.

I would like to modify it as follows:

Table temp_req
jobnumber (text)
PreReq (text)

Temp Records
EmpNo (text)
CourseNo (text)
[red]
tblSubCourse
SubCourseNumber (text)
SubCourseName (text)
ExpirationInMonths(int)
[/red]

[blue] temp_req.prereq=temp records.courseno=tblsurcourse.subcoursenumber[/blue]
temp_req data
JobNumber    PreReq
100        0001
100        0002
100        0003
100        0004
100        0005


TempRecords
EmpNo        CourseNo
12345        0001
12345        0002
12345        0003

Expected Output
EmpNo   prereq  courseno [red]SubCourseName[/red]
12345    0001    0001     [red] MyCourse[/red]
12345    0002    0002      [red]Another Course[/red]
12345    0003    0003      [red] something different[/red]
12345    0004             [red] A Different One[/red]
12345    0005              [red]SQL For Dummies[/red]

I tried to have patience but it took to long! -DW

 

[PHV]

SELECT J.PreReq, J.JobNumber, T.CourseNo, T.EmpNo, S.SubCourseName
FROM (tblJobReq AS J LEFT JOIN (
SELECT * FROM TempRecords WHERE EmpNo='12345'
) AS T ON J.PreReq = T.CourseNo)
LEFT JOIN tblSubCourse AS S ON J.PreReq = S.SubCourseNumber
WHERE J.JobNumber='100'

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[r937]

SELECT tblJobReq.PreReq
     , tblJobReq.JobNumber
     , TempRecords.CourseNo
     , TempRecords.EmpNo
     , tblSubCourse.SubCourseName
  FROM (
       tblJobReq 
LEFT 
  JOIN TempRecords 
    ON (
       TempRecords.CourseNo = tblJobReq.PreReq
   AND TempRecords.EmpNo = "12345"
       )
       )
LEFT 
  JOIN tblSubCourse 
    ON tblSubCourse.subcoursenumber = TempRecords.CourseNo    
 WHERE tblJobReq.JobNumber = "100"

r937.com | rudy.ca

 

[jadams0173]

phv,

That worked. However, I'm trying to expand on what you have provided for me without asking or having you 'do it for me' but I'm running up against a wall. In access the query runs fine. In VB I get the error of no value given for one or more required parameters. Here is what I have.

also temp_records is the same as tblTrainingRecords. Earlier I was useing temp tables but have switched to my production (test) tables.

sql = "SELECT J.PreReq, J.JobNumber, T.CourseNo, Max(T.CourseDate) AS MaxOfCourseDate, T.EmpNo, S.SubCourseName, [red]Max(CDate(Format(IIf(IsNull([T].[EmpNo]),#1/1/1900#,DateAdd('m',(s.[expirationInMonths]),[coursedate])),'mm/dd/yyyy'))) AS ExpireDate[/red] " _
 & "FROM (tblJobReq AS J LEFT JOIN [Select * from tblTrainingRecords WHERE EmpNo='12345' " _
 & "]. AS T ON J.PreReq = T.CourseNo) LEFT JOIN tblSubCourse AS S ON J.PreReq = S.SubCourseNumber " _
 & "WHERE (((J.JobNumber)='100')) " _
 & "GROUP BY J.PreReq, J.JobNumber, T.CourseNo, T.EmpNo, S.SubCourseName"

Everything is fine until I add what is in red above. I add the MAX to CourseDate and to my function in red because I want the Max date from those fields as there could be the same courseno and empno but different dates when the courses were taken.

I tried to have patience but it took to long! -DW