Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 223
  • Last Modified:

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([2VisaProcessChecklist]![Lodged]<=[Up To This Date],1,0))

FiledLodged: Sum(iif([2VisaProcessChecklist]![VisaApprovedDate]<=[Up To This Date] AND ([2VisaProcessChecklist]![VisaApprovedDate]>[Up To This Date] or isnull([2VisaProcessChecklist]![VisaApprovedDate])),1,0))

SenttoFragomen: Sum(iif([2VisaProcessChecklist]![SenttoFragomenDate]<=[Up To This Date] AND ([2VisaProcessChecklist]![Lodged]>[Up To This Date] or isnull([2VisaProcessChecklist]![Lodged])),1,0))

CollectingCandidateInfo: Sum(iif([2VisaProcessChecklist]![FirstContacted]<=[Up To This Date] AND ([2VisaProcessChecklist]![SenttoFragomenDate]>[Up To This Date] or isnull([2VisaProcessChecklist]![SenttoFragomenDate])),1,0))

CandidateConsideringOpportunity: Sum(iif([2VisaProcessChecklist]![ReceivedName]<=[Up To This Date] AND ([2VisaProcessChecklist]![FirstContacted]>[Up To This Date] or isnull([2VisaProcessChecklist]![FirstContacted])),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.
0
tlahpalli
Asked:
tlahpalli
  • 2
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That would depend on what you want your report to show (i.e. how much detail, etc). If you just want to show Counts, like the Number of people who are Approved, then just build a query that includes those 5 fields, and show that on your report. You'd have to "Join" your various tables together correctly, of course, but we really can't help with that, given the information in the question.

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.
0
 
tlahpalliAuthor Commented:
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.
0
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
That's very difficult to say without knowing more about your table structure. I'd encourage you to work with EACH individual field at one time. Build a query with ONLY the first calculated field, and get that one right. Then build a query with the second calculated field and work with that one, and continue on until you get all of them right. Once you've done that, combine all of them into a single query (or use the individual queries to drive Subreports, which you'd then combine into a Main report).

I'd also strongly encourage you to NOT use IIF statements, since that can cause some troubles with your calculations. You're much better off building proper JOIN queries, and using those to make your calculations. For example in your first example, you'd perhaps do something like this:

SELECT COUNT(YourIDField) As Approved FROM [2VisaProcessChecklist] WHERE [Lodged] = [Enter Date]

If you build a query like this, and SAVE that query, you can then use that query as a  source table when you build your "final" query. This query will ouput nothing more than a COUNT of the number of Records in your table where the value in the Lodged field is = to whatever value the user enters.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now