How to select ONLY Done

[Emilio]

I have a table (JobSchedule) with 3 fields:
Scheduled, JobDate and Status

Say I have:
4445-Jones-W-3 08/01/03 Done
4445-Jones-W-3 08/02/03 Done
4445-Jones-W-3 08/03/03 InstallerTold
4456-Smith-A-2 08/01/03 Done
4456-Smith-A-2 08/01/03 Done
4487-Wan-A-4 08/05/03 Done
4487-Wan-A-4 08/06/03 Done
4487-Wan-A-4 08/07/03 InstallerTold
4487-Wan-A-4 08/08/03 Scheduled

How can I select only the jobs that have been
completely "Done"
in this case:
4456-Smith-A-2 Done

Any help appreciated.
Emilio

 

[Michel Walsh]

Hi,


There are many, many solutions. Here one

SELECT j.JobName
FROM JobStates As j
GROUP BY JobName
HAVING COUNT(*)=( SELECT COUNT(*)
FROM JobStates As i
WHERE i.JobName = j.JobName)



Another solution, in 3 queries:

Q1: SELECT JobName, COUNT(*) AS cnt FROM JobStates GROUP BY JobName ;

Q2: SELECT JobName, COUNT(*) As cnt FROM JobStates WHERE JobState='Done'
GROUP BY JobName ;

Q3: SELECT q1JobName FROM q1 INNER JOIN q1 ON ( q1.JobName=q2.JobName AND
q1.cnt=q2.cnt )




where q1 count the number of sub-contract; q2 count those that are done; q3
keep those from q1 that have the same count than in q2.


If you wish to keep also all the "sub-contrast", then you can try, if there
is no NULL in the JobState field: (--UNTESTED--)

SELECT *
FROM JobStates As j
WHERE 'Done' = ALL( SELECT i.JobState
FROM JobStates As i
WHERE i.JobName = j.JobName)


where you probably can change the = ALL to <> ANY , if there is no NULL, to
get the same info, but for contract having at least one subcontract not
done.



Hoping it may help,
Vanderghast, Access MVP

 

[Emilio]

Thanks a lot for your help,
Unfortunately I forgot to mention that I am not very good
with Access at all and I am confused, I just need some
clarification and I am on my way.

What exactly is "j.JobName","i.Jobname","j" and "i" in
relation to my table "JobSchedule" and my three
fields: "Scheduled", "JobDate" and "Status"

Thanks again,
Emilio

 

[Michel Walsh]

Hi,


i and j, in this context, are just "alias" (very light copy) for the
table. I just found I have miss a line:


SELECT j.JobName
FROM JobStates As j
GROUP BY JobName
HAVING COUNT(*)=( SELECT COUNT(*)
FROM JobStates As i
WHERE i.JobName = j.JobName
AND i.JobState='Done' )




Here, "i" is the table where we consider just the subcontracts "done", and j
is the whole table, so, in short, the code say to keep the jobName that, for
a given one, the count (of the number of records) is the same in "i" and in
"j".


You can try the second solution, implying 3 queries, it is may be easier for
a start, and for customization.



Hoping it may help,
Vanderghast, Access MVP

 

[Emilio]

Thanks a lot for everything, I tried the second solution
and worked perfectly.

Emilio