tlahpalli
asked on
How do I set up a report to show the historical statistics of the count of people per position they hold in each stage in the process we have them in?
I was given these formulas below to use based off the setup of my database:
Approved: Sum(IIf([2VisaProcessCheck list]![Lod ged]<=[Up To This Date],1,0))
FiledLodged: Sum(iif([2VisaProcessCheck list]![Vis aApprovedD ate]<=[Up To This Date] AND ([2VisaProcessChecklist]![ VisaApprov edDate]>[U p To This Date] or isnull([2VisaProcessCheckl ist]![Visa ApprovedDa te])),1,0) )
SenttoFragomen: Sum(iif([2VisaProcessCheck list]![Sen ttoFragome nDate]<=[U p To This Date] AND ([2VisaProcessChecklist]![ Lodged]>[U p To This Date] or isnull([2VisaProcessCheckl ist]![Lodg ed])),1,0) )
CollectingCandidateInfo: Sum(iif([2VisaProcessCheck list]![Fir stContacte d]<=[Up To This Date] AND ([2VisaProcessChecklist]![ SenttoFrag omenDate]> [Up To This Date] or isnull([2VisaProcessCheckl ist]![Sent toFragomen Date])),1, 0))
CandidateConsideringOpport unity: Sum(iif([2VisaProcessCheck list]![Rec eivedName] <=[Up To This Date] AND ([2VisaProcessChecklist]![ FirstConta cted]>[Up To This Date] or isnull([2VisaProcessCheckl ist]![Firs tContacted ])),1,0))
Basically I have 5 stages in a work visa process. There is a position table, a visa status table, the employee table, a checklist table that holds the dates when each employee reaches each stage of the process. Let me know if you have questions for me on this as well.
Approved: Sum(IIf([2VisaProcessCheck
FiledLodged: Sum(iif([2VisaProcessCheck
SenttoFragomen: Sum(iif([2VisaProcessCheck
CollectingCandidateInfo: Sum(iif([2VisaProcessCheck
CandidateConsideringOpport
Basically I have 5 stages in a work visa process. There is a position table, a visa status table, the employee table, a checklist table that holds the dates when each employee reaches each stage of the process. Let me know if you have questions for me on this as well.
ASKER
I've already done a query with those 5 statuses. The problem is that I don't know if the formulas I posted above are wrong or wording is wrong because when I enter a date into the [Up To This Date] field the counts it results in don't add up right. I need help making a formula for each status that will show the right results for any given date I want to enter.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you need details - like names, phone numbers, etc - then you'll need to first develope the queries that will drive that report. Once you've done that, you'd just use those queries to build a Report.