groups of factors on one report for statistical purposes?

[misscrf]

I have a database, and I am looking to add a somewhat master statistical report.

The database is for candidates that apply at my work, for a job. The statistical report will be for counting who we have hired.

The factors are what department they were hired to, what office, and what candidate type they are.

The db is in 3rd normal form with lookup tables for any "type" or factor that has to do with a candidate. If you are really interested to see the table relationships, I have a jpg I can link to for you.

Anyway, my goal is to have the user enter a beginning and end date for their criteria, which will be pulled from the start date that the candidate was hired.

Then the report would show the statistics for hires during that time period. It would look like the following:

Office Hired to:

New York City - 10
Boston - 15
Chicago - 30


Department Hired to:

Human Resources - 8
IT - 12
Finance - 24
Marketing - 6


Candidate Type Hired:

Administrator - 13
Analyst - 27
entry level - 21
intern - 9

Total Hired Between dateA and dateB: 50

Then I would like to add a chart (time line of hire ups and downs, or maybe bars for each factor, not sure)


right now, no matter what I do, I get

office:

New York - 1

Candidate type:

Administrator - 1

Department - Human resources

for each record.

If anyone can help me to get this to group right, I would really appreciate it. Thank you.


misscrf

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

 

[misscrf]

Well, I'm not getting anywhere. I tried to have one main report with a sub report for each group. Problem is that I can't get each group to group together.

Here is a link to my table relationships. If someone can help to guide me in the right direction, I would really appreciate the help.

Thank you.

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

misscrf

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

 

[misscrf]

One forum post suggested that I post the sql for my queries. I hope that doing so might help bring some assistance.

The main query for the main report has the following sql:

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;

Each category has a subreport, and its underlying query has this appropriate sql statement:

SELECT Count(tblOffice.Office) AS CountOfOffice, tblOffice.Office, tblOffice.OfficeID, tblCandidate.CandID
FROM tblCandidate RIGHT JOIN (tblApplications RIGHT JOIN (tblOffice RIGHT JOIN tblActivity ON tblOffice.OfficeID = tblActivity.OfficeHired) ON tblApplications.AppID = tblActivity.ApplicationID) ON tblCandidate.CandID = tblApplications.CandID
WHERE (((tblActivity.StartDate) Is Not Null))
GROUP BY tblOffice.Office, tblOffice.OfficeID, tblCandidate.CandID;

What I get is:

Office
New York 1
Chicago 1


Office
New York 1
Chicago 1

That is just with 1 subreport in.

Any ideas what I am doing wrong?

Thank you.


misscrf

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