Solved

# 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?

Posted on 2011-02-20
214 Views
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
Question by:tlahpalli
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points
• 2

LVL 84

Expert Comment

ID: 34939185
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

Author Comment

ID: 34939272
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

LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 34939445
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

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

### Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
###### Suggested Courses
Course of the Month6 days, 19 hours left to enroll