[thelist] Data Shaping and Count
Matt Warden
mwarden at gmail.com
Mon May 9 16:02:55 CDT 2005
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.
More information about the thelist
mailing list