Page 2 - SQL Query- subquery? (im lost)

ok- heres 2 from each tbl

scores:
usrid,crsid,comp_id,score,date
usr1,crs1,quiz1,100,1/1/01
usr1,crs1,quiz2,100,1/1/01
usr1,crs1,quiz3,100,1/1/01
usr2,crs1,quiz1,100,1/1/01

score elements:
crs_id,comp_id,comp_lbl
crs1,quiz1,"quiz1"
crs1,quiz2,"quiz2"
crs1,quiz3,"quiz3"

how can i make a qry to check if usr's in scores have completed each component per course, which is defined in scoreelements?

ideal return:
usrid,crsid,complete,avg_score
usr1,crs1,y,100
usr2,crs1,n,100

ps- if the y/n isnt possible- i would like only usr1

i can build a table for the other usr's & append to above record set with 'n' for complete

okay, create this query and save it as crs_components:

SELECT crs_id, count(*) AS components
FROM tblElements
GROUP BY crs_id;

now create this query and save it as usr_crs_scores:

SELECT usr_id, crs_id
, count(*) AS scores
, avg(score) as avgscore
FROM tblScores
GROUP BY usr_id, crs_id;

now run this query for your report:

select S.usr_id, S.crs_id
, iif(S.scores=C.components,'y','n') as complete
, S.avgscore
from usr_crs_scores S
left outer
join crs_components C
on S.crs_id = C.crs_id

rudy

ok- but the issue is 'count(*) ' wont work since you can have multiple records for each component id per course-

ie you can take the quiz in crs 1 unlimited times- so the count would be greater or equal to; i need to check that values in scores.comp_id are equal to score_elements.comp_id for each comp_id per course

further- crs Y has 3 comp id's. if usrY has 3 records in scores for course for quiz1- he will meet the count(*) logic, but its not a desired return. this is the root of my problem

thanks,
duncan

ps- thanks for the IIF; think i get how to use those now

i see you are beginning to answer your own questions

you need a new query

select usr_id, count(*) as courses
from usr_crs_scores
group by usr_id

this tells how many different courses a user has tried

now join this to crs_components to see if the user has tried all the components of a course

this time the counts should be comparable

ok- the cogs are beginning to turn- i guess the join does the check for each component per crs id

thanks again,
duncan

im spinning in circles here- im still unsure why its a count(*) since i want to compare values in the column

heres my end query- but it doesnt look right

SELECT usr_crs_scores.USERLOGONID, Count(*) AS courses, usr_crs_scores.scores, usr_crs_scores.avgscore, usr_crs_scores.LastOfDATE_TIME
FROM crs_components LEFT JOIN usr_crs_scores ON crs_components.COURSENUMBER = usr_crs_scores.COURSENUMBER
GROUP BY usr_crs_scores.USERLOGONID, usr_crs_scores.scores, usr_crs_scores.avgscore, usr_crs_scores.LastOfDATE_TIME;

ok- lets start from step 1 again(sorry but im confused)

i have this query to get crs/comp_id relationship:
SELECT scoreElements.COURSENUMBER, scoreElements.COMPONENTID
FROM scoreElements
GROUP BY scoreElements.COURSENUMBER, scoreElements.COMPONENTID, scoreElements.COURSETYPE
HAVING (((scoreElements.COMPONENTID)<>"00000000") AND ((scoreElements.COURSETYPE)=13));

output:
COURSENUMBER COMPONENTID
13205 031
13205 032
13205 033
13205 034
13205 141
13205 231
13205 331
13205 431

the above is all of the id's for crs 13205 within scoreelements (theres 146 crs's). now i have to join this to a scores query with a where & an and statement(thats what i think)

this query returns about 300 records more than what i think i should get(1792 vs 1413 w/another tool) also, i get a blank row with a count(*) of 511- which is wierd

help?
duncan
SELECT Scores.USERLOGONID, Scores.COURSENUMBER, Avg(Scores.SCORE) AS AvgOfSCORE, Last(Scores.DATE_TIME) AS LastOfDATE_TIME, Count(*) AS Expr1
FROM qryComp_IDS LEFT JOIN Scores ON (qryComp_IDS.COMPONENTID = Scores.COMPONENTID) AND (qryComp_IDS.COURSENUMBER = Scores.COURSENUMBER)
GROUP BY Scores.USERLOGONID, Scores.COURSENUMBER;

y- that query is bad-

i have a usr with 1 record for a crs/comp where teh crs has 6 comp defined. i think htis is a join prob

i switched to a right join * i get the field is too small or insert less data err

i'm having a hard time following what you're doing

start with the two queries that compute a count

that way, you can join the queries rather than trying to do complex joins with GROUP BY and HAVING

y- this is getting a bit complex- i am trying to avoid counts because a user can have multiple records for each component, but let me try a distinctrow qry-

when you save the queries that contain counts, they will act like a VIEW in any other database

heres where my inexperience comes in, but cant i have a statement like:
where scoreelements.componentid = scores.componentid for scoreelements.coursenumber EXISTS in scores?

or something to this effect?

the "something to this effect" is a join, possibly a left outer join

do the two count queries, and then take stock of where you are

when you select all the rows from the count queries, they will act just like tables

then join those together!