434,960 Members | 2,238 Online
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
4 Replies

 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 select format([date application rcvd],"yyyy-mm") as YearMonth, count(*), 0 from tblX Group By format([date application rcvd],"yyyy-mm") UNION select format([date appointment given],"yyyy-mm") as YearMonth, 0, count(*) from tblX Group By format([date appointment given],"yyyy-mm")   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

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

 Expert 2.5K+ P: 3,072 Hmmm, what about a "year overlapping" dataset ADezii Nic;o) Dec 14 '08 #4

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