access2k & duplicate records/value check

all-

heres the table definition

Scores
userid - foreign key; may dupe
course_id - foreign key- may dupe
comp_id - foreign key- may dupe
score - # value
date - mm/dd/yyyy - interested in last value
course_type- ='13'

scoreElements
course_id- foriegn key; may dupe
comp_id- may dupe
course_type ='13'

ok- tbl scores has all records for students per course per component. component may be taken multiple times- ie chapter 1 quiz. i need to return users who have a record in scores for all components defined in scoreelements for each course_id.

heres an example dataset-
scores:
userid, course_id,comp_id,score,date,course_type
usr1,crs1,quiz1,100,1/1/01,13
usr1,crs1,quiz2,100,1/2/01,13
usr1,crs1,quiz3,100,1/3/01,13
usr2,crs1,quiz1,100,1/1/01,13
usr2,crs1,quiz1,100,1/1/01,13
usr2,crs1,quiz1,100,1/1/01,13
usr1,crs2,quiz1,100,1/1/01,13
usr1,crs2,quiz2,100,1/1/01,13

scoreelements-
course_id,comp_id,course_type
crs1,quiz1,13
crs1,quiz2,13
crs1,quiz3,13
crs2,quiz1,13
crs1,quiz2,13
crs1,quiz3,13

desired query results
userid,course_id,score,date,pass
usr1,crs1,avg(score),last(date),[pass if score > 75]

usr1 has all com_id's for crs1, but not crs2. usr2 has not completed the course- quiz2 & 3 have not been completed. note: i think a coun(*) wont work becuase usr2 would have 3 records, but they are for quiz1

help pls!
duncan

sorry, last 2 records in score elements should read (crs# is crs2):
scoreelements-
course_id,comp_id,course_type
crs1,quiz1,13
crs1,quiz2,13
crs1,quiz3,13
crs2,quiz1,13
crs2,quiz2,13
crs2,quiz3,13

hi again duncan

want me to sit down and shut up and let somebody else take a shot at this?

because i thought i had already solved this for you in that other thread

in fact, i still have my access database where i tested it


rudy

sorry- i thought it got buried- that query returned a couple wierd results- ill see if i got a syntax err in it

thanks,
duncan