[thelist] Data Shaping and Count

[Matt Warden]

Michele,

On 5/9/05, Michele Foster (WizarDev) <michele at wizardev.ca> wrote:
> j.JobID ContactID otherjobs.JobID
> 74     23909         74
> 72     23635         72
> 72     23635         52
> 72     23635         63
> 72     23635         54
> 70     23905         70
> 69     23852         69
> 69     23890         69
> 69     23831         69
> 69     23831         67
> 
> Where I'm confused, and why I abandoned earlier approaches was my looping
> through the recordset.  Could you, or anyone really, give me some guidance
> on how to approach this.  I was getting lost with my do until, do while, and
> if not end of RS statements.  Something rough is fine, I don't want someone
> else to do this for me ... just a push in the right direction would be
> helpful.  :)

Basically, you would do something like this (very rough, like you
asked for, so you will have to alter it -- not to mention i havent
done ASP in a while):

do while not rs.eof
        iJobID = rs(x)
        ' print out job
        do while rs(x)=iJobID and not rs.eof   ' loop while same job
               iApplicantID = rs(y)
               ' print out applicants for this job
               do while rs(y)=iApplicantID and not rs.eof  ' loop
while same applicant
                        ' print out applicant's other applications
               loop
         loop
loop

To get this to work, you will have to make sure your data set is
ordered by jobid, then applicantid, then otherjobid.

> One thing I do notice with the above query .. it doesn't give me the jobs
> even if there are no applicants, i.e. Project71 currently has no applicants
> for it yet.  With the data shaping because I in essence had two recordsets I
> could check that while it looped.  It's not a big deal though, just thought
> I would ask if it were possible to get that into the mix somehow.

You need to outer join on the first jobs-volapply join rather than
just inner join. You need to use the formal join syntax (like you used
in your original query and I arrogantly destroyed due to my hatred of
it) to do this in Access, I believe.



-- 
Matt Warden
Miami University
Oxford, OH, USA
http://mattwarden.com


This email proudly and graciously contributes to entropy.