By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
434,960 Members | 2,238 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 434,960 IT Pros & Developers. It's quick & easy.

Need help with Totals in reports

P: 18
Hello,
I have a question about reports, it might be complicated but I will try to simplify what I need.
I have a table where some of many fields is "name" "date application rcvd" "date appointment given". "male or female" (Yes/No field).
I need to print reports to know:

1) How many applicants applied in a given month. I Just need the report to mention, April: 45 application received. 20 applications processed. 6 males. Is this possible??

2) The delay between "dateApplicationRcvd" and "dateApptGiven" in days, I don't care about working days, I just need to calculate the No of days it took to get appt.

Hope someone can help.
Thanks
Nour
Dec 14 '08 #1
Share this Question
Share on Google+
4 Replies


nico5038
Expert 2.5K+
P: 3,072
About
1) How many applicants applied in a given month. I Just need the report to mention, April: 45 application received. 20 applications processed. 6 males. Is this possible??
You'll first have to indicate which date to use for the Male/Female.
A general approach will be to use a goup by query to total the dates per Year/Month.
One solution is to use:
Expand|Select|Wrap|Line Numbers
  1. select format([date application rcvd],"yyyy-mm") as YearMonth, count(*), 0 from tblX Group By format([date application rcvd],"yyyy-mm")
  2. UNION
  3. select format([date appointment given],"yyyy-mm") as YearMonth, 0, count(*) from tblX Group By format([date appointment given],"yyyy-mm")
  4.  
By using this query as the basis for a new group by on the YearMonth and taking the Sum() or Max().

About 2)
For the delay just subtract the dates in the report query like:
[dateApplicationRcvd] - [dateApptGiven]

Nic;o)
Dec 14 '08 #2

ADezii
Expert 5K+
P: 8,638
@Nour469
Here is a couple of things to get you started:
  1. To calculate the number of Days it took to get an Application, assuming a Table Name of tblApplications:
    Expand|Select|Wrap|Line Numbers
    1. SELECT tblApplications.[Date Application Rcvd], tblApplications.[Date Application Given], 
    2. DateDiff("d",[Date Application Rcvd],[Date Application Given]) AS Delay
    3. FROM tblApplications;
  2. To calculate how many Applicants applied within any given Month, assuming a Table Name of tblApplications:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Month([Date Application Rcvd]) AS [Month],
    2. Count(Month([Date Application Rcvd])) AS [Total Apps For Month]
    3. FROM tblApplications
    4. GROUP BY Month([Date Application Rcvd]);
  3. To calculate the number of Males/Females who applied in any given Month, assuming a Table Name of tblApplications:
    Expand|Select|Wrap|Line Numbers
    1. SELECT Month([Date Application Rcvd]) AS [Month], IIf([Male]=True,"Male","Female") AS Sex, 
    2. Count(IIf([Male]=True,"Male","Female")) AS [Count of Sex]
    3. FROM tblApplications
    4. WHERE (((tblApplications.[Date Application Rcvd]) Is Not Null))
    5. GROUP BY Month([Date Application Rcvd]), IIf([Male]=True,"Male","Female");

P.S. - SQL is not my area, better Replys will probably be forthcoming.
Dec 14 '08 #3

nico5038
Expert 2.5K+
P: 3,072
Hmmm, what about a "year overlapping" dataset ADezii <LOL>

Nic;o)
Dec 14 '08 #4

ADezii
Expert 5K+
P: 8,638
@nico5038
Please Nico, I already said that SQL is not my area! (LOL).
Dec 15 '08 #5

Post your reply

Sign in to post your reply or Sign up for a free account.