Count NonParticipants that completed at least one program

[topwaya]

Hello -

Here is what I'm trying to do...

I want to say "this many non-members participated in at least one program" at each location (tblPersonal.LocW), for each member type (tblPersonal.MType), and total. (and yes, they may have a "member type" even if they are not a member yet)

I'm trying to figure out number if PIDs (personal ID auto primary key) based on MType (member type)that have participated in any of the following (is not null)
tblConsult.ConsD,
tblFCAssess.AssessD,
tblFCAssess.Wt,
tblFCAssess.Waist,
tblFCAssess.HR,
tblFCAssess.AssessBP,
tblFCAssess.BC,
tblFCAssess.VO2,
tblFCAssess.METS,
tblFCAssess.Curl,
tblFCAssess.Push,
tblFCAssess.Flex,
tblGASP.GaspD,
tblLNL.LnLD,
tblMDG.MDGSD,
tblNL.NLD,
tblOrient.OrD,
tblPed.PedSD,
tblPersonal.OLSD,
tblScr.ScrD,
tblTobCes.TobCesD,
tblWalk.WalkD,
tblWB.WBD

I only want to count the people (PIDs) that are not members.

If tblPersonal.FCSD is null they are not a member
OR
If tblPersonalFDSD is not null AND tblPersonal.XLD is not null, they are not a member.

Here is the SQL that I have, that of course is not working because I'm a newbie and I'm trying to figure it all out. It isn't even displaying the location (tblPersonal.LocW).

TRANSFORM Count(tblPersonal.PID) AS CountOfPID
SELECT tblPersonal.Mtype, Count(tblPersonal.PID) AS [Total NONFCParticipants]
FROM (((((((((((tblPersonal INNER JOIN tblConsult ON tblPersonal.PID = tblConsult.PID) INNER JOIN tblFCAssess ON tblPersonal.PID = tblFCAssess.PID) INNER JOIN tblGASP ON tblPersonal.PID = tblGASP.PID) INNER JOIN tblLNL ON tblPersonal.PID = tblLNL.PID) INNER JOIN tblMDG ON tblPersonal.PID = tblMDG.PID) INNER JOIN tblNL ON tblPersonal.PID = tblNL.PID) INNER JOIN tblOrient ON tblPersonal.PID = tblOrient.PID) INNER JOIN tblPed ON tblPersonal.PID = tblPed.PID) INNER JOIN tblScr ON tblPersonal.PID = tblScr.PID) INNER JOIN tblTobCes ON tblPersonal.PID = tblTobCes.PID) INNER JOIN tblWalk ON tblPersonal.PID = tblWalk.PID) INNER JOIN tblWB ON tblPersonal.PID = tblWB.PID
WHERE (((tblConsult.ConsD) Is Not Null) OR ((tblFCAssess.AssessD) Is Not Null) OR ((tblFCAssess.Wt) Is Not Null) OR ((tblFCAssess.Waist) Is Not Null) OR ((tblFCAssess.HR) Is Not Null) OR ((tblFCAssess.AssessBP) Is Not Null) OR ((tblFCAssess.BC) Is Not Null) OR ((tblFCAssess.VO2) Is Not Null) OR ((tblFCAssess.METS) Is Not Null) OR ((tblFCAssess.Curl) Is Not Null) OR ((tblFCAssess.Push) Is Not Null) OR ((tblFCAssess.Flex) Is Not Null) OR ((tblGASP.GaspD) Is Not Null) OR ((tblLNL.LnLD) Is Not Null) OR ((tblMDG.MDGSD) Is Not Null) OR ((tblNL.NLD) Is Not Null) OR ((tblOrient.OrD) Is Not Null) OR ((tblPed.PedSD) Is Not Null)  OR ((tblPersonal.OLSD) Is Not Null) OR ((tblScr.ScrD) Is Not Null) OR ((tblTobCes.TobCesD) Is Not Null) OR ((tblWalk.WalkD) Is Not Null) OR ((tblWB.WBD) Is Not Null)) AND  (((tblPersonal.FCSD)) Is Null) AND (((tblPersonal.XLD) Is Not Null))
GROUP BY tblConsult.PID, tblConsult.ConsD, tblFCAssess.PID, tblFCAssess.AssessD, tblFCAssess.Wt, tblFCAssess.Waist, tblFCAssess.HR, tblFCAssess.AssessBP, tblFCAssess.BC, tblFCAssess.VO2, tblFCAssess.METS, tblFCAssess.Curl, tblFCAssess.Push, tblFCAssess.Flex, tblGASP.PID, tblGASP.GaspD, tblLNL.PID, tblLNL.LnLD, tblMDG.PID, tblMDG.MDGSD, tblNL.PID, tblNL.NLD, tblOrient.PID, tblOrient.OrD, tblPed.PID, tblPed.PedSD, tblPersonal.OLSD, tblPersonal.XLD, tblScr.PID, tblScr.ScrD, tblTobCes.PID, tblTobCes.TobCesD, tblWalk.PID, tblWalk.WalkD, tblWB.PID, tblWB.WBD, tblPersonal.Mtype
ORDER BY tblPersonal.LocW
PIVOT tblPersonal.LocW;

Any help would be appreciated.
Thank you!

 

[lespaul]

are these all Yes/No field types?

It appears that you are 'committing spreadsheet' with your table design. Is it possible at this point to re-design the structure? You will find it much easier to extract the information you need if it's normalized (for more info on normalization read this paper.

If you can't change the structure, you should write a query that normalizes your data. A normalized scenario would be:

Person
PersonID
Other data

Programs
ProgramID
ProgramName

PersonPrograms
PersonID
ProgramID

with a little more information about your tables and relationships, someone should be able to help you create a normalized query to use as the source of your counting query.




Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

 

[topwaya]

Thanks so much for your response. You made me realize I had a bunch in there that didn't need to be in there.

These are the programs that I'm looking to see if anyone participated in, they are all dates (the date that they participated in that program):

tblConsult.ConsD,
tblFCAssess.AssessD,
tblGASP.GaspD,
tblLNL.LnLD,
tblMDG.MDGSD,
tblNL.NLD,
tblOrient.OrD,
tblPed.PedSD,
tblPersonal.OLSD,
tblScr.ScrD,
tblTobCes.TobCesD,
tblWalk.WalkD,
tblWB.WBD

If a date is in that field, then I want to count them, IF they are NOT a member. So I ran another query that listed all the NONmembers (qryNONMembers) and joined that query with this one.

So here is my crosstab query code now.

TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM ((((((((((qryNONMembers INNER JOIN tblFCAssess ON 
qryNONMembers.PID = tblFCAssess.PID) INNER JOIN tblGASP ON 
qryNONMembers.PID = tblGASP.PID) INNER JOIN tblLNL ON 
qryNONMembers.PID = tblLNL.PID) INNER JOIN tblMDG ON 
qryNONMembers.PID = tblMDG.PID) INNER JOIN tblNL ON 
qryNONMembers.PID = tblNL.PID) INNER JOIN tblPed ON 
qryNONMembers.PID = tblPed.PID) INNER JOIN tblScr ON 
qryNONMembers.PID = tblScr.PID) INNER JOIN tblTobCes ON 
qryNONMembers.PID = tblTobCes.PID) INNER JOIN tblWalk ON 
qryNONMembers.PID = tblWalk.PID) INNER JOIN tblWB ON 
qryNONMembers.PID = tblWB.PID) INNER JOIN tblConsult ON 
qryNONMembers.PID = tblConsult.PID
WHERE (((tblConsult.ConsD) Is Not Null)) OR
(((tblFCAssess.AssessD) Is Not Null)) OR 
(((tblGASP.GaspD) Is Not Null)) OR 
(((tblLNL.LnLD) Is Not Null)) OR 
(((tblMDG.MDGSD) Is Not Null)) OR 
(((tblNL.NLD) Is Not Null)) OR 
(((tblPed.PedSD) Is Not Null)) OR 
(((tblScr.ScrD) Is Not Null)) OR 
(((tblTobCes.TobCesD) Is Not Null)) OR 
(((tblWalk.PID) Is Not Null)) OR 
(((tblWalk.WalkD) Is Not Null)) OR 
(((tblWB.WBD) Is Not Null))
GROUP BY qryNONMembers.Mtype
PIVOT qryNONMembers.LocW;

It is still not working. Is that how you right multiple "OR" criteria in the WHERE statement? And the Location (LocW) column isn't showing up either.

The only thing I have showing up are the MemberType row heading, and the TotalNONMemberParticipants column heading. No location, no data.

Thanks for any help!!!

 

[PHV]

Have you tried to replace all INNER JOIN by LEFT JOIN ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[topwaya]

Thank You! That made the Locations show up .

I'm still struggling, the numbers that are coming up aren't right and I already saw one other mistake - but don't have any more time today to work on it.

Thanks so much!!!

 

[topwaya]

OK - this is what I have now

TRANSFORM Count(qryNONMembers.PID) AS CountOfPID
SELECT qryNONMembers.Mtype, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM ((((((((((qryNONMembers LEFT JOIN tblFCAssess ON qryNONMembers.PID=tblFCAssess.PID) LEFT JOIN tblGASP ON qryNONMembers.PID=tblGASP.PID) LEFT JOIN tblLNL ON qryNONMembers.PID=tblLNL.PID) LEFT JOIN tblMDG ON qryNONMembers.PID=tblMDG.PID) LEFT JOIN tblNL ON qryNONMembers.PID=tblNL.PID) LEFT JOIN tblPed ON qryNONMembers.PID=tblPed.PID) LEFT JOIN tblScr ON qryNONMembers.PID=tblScr.PID) LEFT JOIN tblTobCes ON qryNONMembers.PID=tblTobCes.PID) LEFT JOIN tblWalk ON qryNONMembers.PID=tblWalk.PID) LEFT JOIN tblWB ON qryNONMembers.PID=tblWB.PID) LEFT JOIN tblConsult ON qryNONMembers.PID=tblConsult.PID
WHERE (((tblConsult.ConsD) Is Not Null)) OR (((tblFCAssess.AssessD) Is Not Null)) OR (((tblGASP.GaspD) Is Not Null)) OR (((tblLNL.LnLD) Is Not Null)) OR (((tblMDG.MDGSD) Is Not Null)) OR (((tblNL.NLD) Is Not Null)) OR (((tblPed.PedSD) Is Not Null)) OR (((tblScr.ScrD) Is Not Null)) OR (((tblTobCes.TobCesD) Is Not Null)) OR (((tblWalk.WalkD) Is Not Null)) OR (((tblWB.WBD) Is Not Null))
GROUP BY qryNONMembers.Mtype
PIVOT qryNONMembers.LocW;

This is an image of what it gives me:

http://narcy.com/query1.gif

There is an arbitrary "<>" column, I'm not sure what it is supposed to represent?

Also, the numbers don't total up right. Is it counting more than one program that they participated in?
Any help is appreciated - I'm a complete newbie trying to figure out SQL

 

[topwaya]

OK, the arbitrary "<>" column are people without a location, nothing in the qryNONMembers.LocW field.

Now, I'm trying to figure out how to display this for each month of the year. For instance, how many NONMembers participated in at least one program in Jan, Feb, Mar, etc.

 

[lespaul]

I took out the TRANSFORM and PIVOT clauses because I don't work much with cross tab queries, but here's what you need to do to change this one to get the month.

SELECT qryNONMembers.Mtype, Month(DateField) As ParticipatingMonth, Count(qryNONMembers.PID) AS TotalNONMemberParticipants
FROM ((((((((((qryNONMembers LEFT JOIN tblFCAssess ON qryNONMembers.PID=tblFCAssess.PID) LEFT JOIN tblGASP ON qryNONMembers.PID=tblGASP.PID) LEFT JOIN tblLNL ON qryNONMembers.PID=tblLNL.PID) LEFT JOIN tblMDG ON qryNONMembers.PID=tblMDG.PID) LEFT JOIN tblNL ON qryNONMembers.PID=tblNL.PID) LEFT JOIN tblPed ON qryNONMembers.PID=tblPed.PID) LEFT JOIN tblScr ON qryNONMembers.PID=tblScr.PID) LEFT JOIN tblTobCes ON qryNONMembers.PID=tblTobCes.PID) LEFT JOIN tblWalk ON qryNONMembers.PID=tblWalk.PID) LEFT JOIN tblWB ON qryNONMembers.PID=tblWB.PID) LEFT JOIN tblConsult ON qryNONMembers.PID=tblConsult.PID
WHERE (((tblConsult.ConsD) Is Not Null)) OR (((tblFCAssess.AssessD) Is Not Null)) OR (((tblGASP.GaspD) Is Not Null)) OR (((tblLNL.LnLD) Is Not Null)) OR (((tblMDG.MDGSD) Is Not Null)) OR (((tblNL.NLD) Is Not Null)) OR (((tblPed.PedSD) Is Not Null)) OR (((tblScr.ScrD) Is Not Null)) OR (((tblTobCes.TobCesD) Is Not Null)) OR (((tblWalk.WalkD) Is Not Null)) OR (((tblWB.WBD) Is Not Null))
GROUP BY qryNONMembers.Mtype, Month(DateField) As Month;

you'll need to change the bold DateField to the actual fieldname.

HTH

Leslie