[thelist] Data Shaping and Count

[Michele Foster (WizarDev)]

Matty,

----- Original Message ----- 
From: "Matt Warden" <mwarden at gmail.com>

> As rudy says: "I have seen data shaping and it is the spawn of satan."

Ok, point taken ... I didn't realize it was "evil".   I'll remember that for
next time.  I should have asked thelist before abandoning my first approach.
:)

>
> SELECT a.VolApplyID, a.VolApplyContactID, a.DateSubmitted,
> j.PositionEn, c.ContactID, c.FirstName, c.LastName,
> otherjobs.PositionEn
> FROM VolApply a, Contacts c, Jobs j, VolApply otherapplications, Jobs
otherjobs
> WHERE a.VolApplyContactID = c.ContactID
> AND a.Position = j.JobID
> AND j.Uniterra = 'Yes'
> AND otherapplications.VolApplyContactID = a.VolApplyContactID
> AND otherapplications.Position = otherjobs.PositionEn
> ORDER BY j.ApplicationDeadline, a.DateSubmitted


This is very slick ... and in Access it is returning the record set as I
would like it.  Thank you!!!  I made a few minor changes, and this is what
I'm working with now ..

SELECT a.VolApplyID, a.VolApplyContactID, a.DateSubmitted, j.PositionEn,
j.JobID, c.ContactID, c.FirstName, c.LastName, otherjobs.PositionEn,
otherjobs.JobID
FROM VolApply AS a, Contacts AS c, Jobs AS j, VolApply AS otherapplications,
Jobs AS otherjobs
WHERE a.VolApplyContactID=c.ContactID
And a.Position=j.JobID
And j.Uniterra=true
And otherapplications.VolApplyContactID=a.VolApplyContactID
And otherapplications.Position=otherjobs.JobID
ORDER BY j.jobID DESC, j.ApplicationDeadline, a.DateSubmitted;

Now, below is a sample of the data (only the numbers, easier to see the
patterns),

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.  :)

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.

Thanks again,

Mich