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
3
213 Views
Last Modified: 2012-05-11
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
Comment
Question by:tlahpalli
  • 2
3 Comments
 
LVL 84
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

by:tlahpalli
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

by:
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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Preparing an email is something we should all take special care with – especially when the email is for somebody you may not know very well. The pressures of everyday working life stacked with a hectic office environment can make this a real challen…
Access custom database properties are useful for storing miscellaneous bits of information in a format that persists through database closing and reopening.  This article shows how to create and use them.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question