count categories, group by category not date

[misscrf]

I posted this, but no one has responded, so I thought I would try again.

I have an application that is for candidates that apply for a job. I am looking to capture a statistics report that will show the counts for how many that have been hired, in a certain time period.

The counts would be for what department they were hired to, what office they were hired to, and how they applied.

There are set office types, in an office types table, departments in a department table, and so on.

I would like to set up a form to ask the user for the beginning and end date of when they would like to see stats for. Then the stats would be a count for that range.

If seeing my table relationships will help, you can view an image here:

http://www.geocities.com/misscrf/dbrelationships5-12-05.jpg

It is normalized. The activities table is where an activity type of offer extended is captured. When that is the activity, there is an offer result. If that offer result is accept, than there is a start date for the hire, department the candidate is hired too, office and title (not important as I wont count those.)

The application source is captured in the application table, where an application is stored for a candidate.


My issue is that I make a main report, with the start date as the only field, since that is the only one that will have criteria.

Then I have a sub report for each group with the category and count of it.

My problem is that ( with my testing as an example)

I have 2 hired people and I put my criteria to between 2 form fields. Filled in to capture both hires, I should get

Office
New York 1
Chicago 1


Instead I get

Office
New York 1
Chicago 1

Office
New York 1
Chicago 1


Almost like it is giving 2 times, once for each hire.

If anyone can help me to sort this out (lol) I would really appreciate the help.
Thank you.


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[abig99]

I found it hard to understand what you mean, any chance you could post a link to a picture of the report your getting? and tell me what table is linked to the main report and what table is linked to the sub report.

 

[misscrf]

sure. it will take me time to set it up, but I will get it ready.

Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot

 

[misscrf]

here is a link to an image of the report:

http://www.geocities.com/misscrf/statreport.jpg

the main report and sub report for this come from queries.

Here is the sql of the main report:

SELECT tblActivity.StartDate
FROM tblApplications INNER JOIN tblActivity ON tblApplications.AppID = tblActivity.ApplicationID
WHERE (((tblActivity.StartDate) Is Not Null And (tblActivity.StartDate) Between #1/1/2005# And #12/12/2005#))
GROUP BY tblActivity.StartDate;

here is the sql of the sub report:

SELECT tblActivity.StartDate
FROM tblApplications INNER JOIN tblActivity ON tblApplications.AppID = tblActivity.ApplicationID
WHERE (((tblActivity.StartDate) Is Not Null And (tblActivity.StartDate) Between #1/1/2005# And #12/12/2005#))
GROUP BY tblActivity.StartDate;

I know that I am doing something wrong, and that this is probably really easy, but I can't seem to figure it out.

I want each category to show counts for each of its types for the range of the dates. I do not want a count for each date. It should be more like a total of how many candidates hired to the New York office for a time period, and so on.

Thank you for responding.
I look forward to any help that you or anyone else can give.
:-D


misscrf

It is never too late to become what you could have been ~ George Eliot

 

[abig99]

This is partly guess work as i cant see your report in design view (my fault i know, i should have asked), but if you have your subreport inside your detail section on your report then what you are doing is displaying a duplicate set of info per detail section.

Your main report has 2 records, for each record they get their own detail section, so in each detail section they will show your sub report, In this instance I would either remove the query from your main report all together or move the sub report to the report footer/header section.

Yell if this doesnt help and ill have another look.

Hope this helps
H Jones

 

[misscrf]

I will try putting it in the footer, I guess, but if I remove the query from the main report, how do I pass the date range criteria to the sub reports..put them in each sub-report criteria to the same form report menu fields? I will try all this out and let you know how it goes. Thanks.

misscrf

It is never too late to become what you could have been ~ George Eliot