SQL Query- subquery? (im lost)

hey-

got 2 tbls, 1 tbl, tblscores, has records for all activity for each course. each course has compid's defined in tblScoreElements. a user has completed the course if all compid's have a valid record per course in the scores tbl. so i need to check if teh usr has a rec as described. help?

tbl scores:
usr id
crs id
compid
date
score

tbl scorelements:
crs id
compid

ex:
course 1 has 3 compid's defined in scoreelements(eg- a,b,c). usr 1 has a score for comp id A & B in scores- so the course isnt complete.

usr 2 has 2 'A' compid records in scores, 'c'- so again, crs not complete

usr 3 has a record in scores for a,b & c- so usr 3 is complete- how would i retrieve this record? ive tried having an 'in' clause for scoreelements.compid- but i thinking im going the wrong way..

thanks-
duncan

ps- ive been trying to do this myself for a week now- so pls help

thanks- it seems to work- but got way fewer results that expect (146 out of 27k records)

note- the usr can have multiple records for each component id defined in tblscoreelements

ie- 1 usr has 7 records for i comp id in a particular crs

i am unsure- but i think this checks if the record counts are the same. maybe i need to intsert some code to check if the values match- kinda like an array i think?

i do appreciate your help Mr boogie

im trying to fix it myslef- but i get a syntax err on: count(distinct compid) = (select count(compid)
from tblScorelements
where crsId = t.crsId)

Could you post your exact query and error message?

select userlogonId,coursenumber from Scores as t
group by userlogonId,coursenumber
having count(distinct componentid) = (select count(componentid)
from ScoreElements where coursenumber = t.coursenumber);
----------------------------------

syntax error (missing operator) in qery expression 'count(distinct componentid) = (select count(componentid) from scoreelements where coursenumber = t.coursenumber

So the query is executable with using count distinct? If so it looks like a strange limitation in SQL server.
You could try

y- im using access 2k- so it is weak, im researching the EXISTS keyword no- this is frustrating, should be simple!

thanks fro your help tho-
duncan

well, golly, if you post an access problem in the sql server forum and don't even mention you're using access....

access doesn't support COUNT DISTINCT

so what you have to do is define a query and save it

select crsid, count(*) as components
from scorelements
group by crsid

save that as comp_query

then

select usrid, crsid, count(*)
from scores
group by usrid, crsid
having count(*) =
( select components from comp_query
where crsid = scores.crsid )

or something very similar

rudy
http://r937.com/

ok- thanks- learned that access really sucks

you're welcome

and no, it doesn't

you just happened to catch it on one of the things it doesn't do well

let's see any other database do a crosstab query, though

let me rephrase- i suck @ access.

this isnt working- i have 27k records and this only returns 146

heres the subquery:
SELECT scoreElements.COURSENUMBER, Count(*) AS IDS
FROM scoreElements
GROUP BY scoreElements.COURSENUMBER, scoreElements.COURSETYPE
HAVING (((scoreElements.COURSETYPE)=13));

ok works good/correctly

2nd query to find out who has a valid entry for each id in scorelements(works not so good)-
SELECT Scores.USERLOGONID, Scores.COURSENUMBER, count(*)
FROM Scores
group by USERLOGONID, COURSENUMBER
having count(*) =
(select IDS from qryGetIDS
where IDS = scores.COMPONENTID);

let me rephrase q- 2 tbl's
1- scores
usr id
compid- reference of compID in Score Elements(and has '00000' for current score & 'complete' for completion entered by usr[so not 100% accurate])
score -# 1-100
date- timestamp

2- scorelements
crsid - crs id; not key
compid- defines name of componnent id for each crs- no set format or # per course
crs_type- must EQ 13;

help?
Duncan

i thougth this should be simple- but im striking out

ps- compID in score elements are either #- 013,032,033; 141,231,351;quiz1,quiz2,quiz3;sim1,sim2,sim3

which can vary per course/chapter- to be a 'completed' course- i would like to check if the usrid has a valild record for each compid per crsid

(with a avg score of > 75%, but i think i can build that in by myself)

you have comma-delimited lists inside a column?

that's gonna screw up your queries no matter what database you're running

plus, i'm afraid i still do not understand your tables

could you generate a DDL script for them please?

and show a few sample rows

thanks

rudy