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
215 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

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 developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
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…

728 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