Need help with Totals in reports

[Nour469]

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

[nico5038]

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:

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)

[ADezii]

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

   SELECT tblApplications.[Date Application Rcvd], tblApplications.[Date Application Given], 
   DateDiff("d",[Date Application Rcvd],[Date Application Given]) AS Delay
   FROM tblApplications;

2. To calculate how many Applicants applied within any given Month, assuming a Table Name of tblApplications:
   

   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]);

3. To calculate the number of Males/Females who applied in any given Month, assuming a Table Name of tblApplications:
   

   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.

[nico5038]

Hmmm, what about a "year overlapping" dataset ADezii <LOL>

Nic;o)

[ADezii]

@nico5038
Please Nico, I already said that SQL is not my area! (LOL).