Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
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
Medium Priority
?
218 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
[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
  • Learn & ask questions
  • 2
3 Comments
 
LVL 85
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 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

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.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
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…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

610 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