Grouping problem?

  • Thread starter Thread starter Bob Duffy
  • Start date Start date
B

Bob Duffy

Guest
Hi there,

Got a problem getting my head round this one:

I have to generate a report that displays the results of the selections from 9 combo boxes. These are answers to equal opportunities questions on a job application form (e.g are you Male, Female, No answer!). They are stored in a "Job applicant" table.

I want to group the total responses over a time period specified by the user entering 2 date values. These date values are compared to the job closing date, stored in the "Job_Details" table.

So, for example, i want to see the total number of applicants who said they were male, the total number who said they were female, etc over the time period chosen.

I have 9 questions in this manner (e.g age, ethnic origin), all of which have to appear with totals on the same report.

Would it be easier to do 9 reports, one for each question?

Any help would be greatly appreciated on the SQL/report grouping.

Cheers
 
If you've got the answers to all nine combos in one record, one solution would be to use just one report, but make a union query to feed it. Something like:

SELECT "Gender" AS Category, Gender AS Response, Count(ID) AS Total FROM Job_Details GROUP BY "Gender", Gender
UNION ALL SELECT "Ethnic Origin", EthnicOrigin, Count(ID) FROM Job_Details GROUP BY "Ethnic Origin", EthnicOrigin
UNION ALL SELECT "Age", Age, Count(ID) ... etc etc

Then your report would sort and group by category and sort by the response value. If the saved response isn't particularly meaningful (i.e. you've just saved a code for ethnicity which doesn't have any intrinsic meaning), you'd have to change the individual select statements to do something like link to a lookup table and return an appropriate description.

Simon
 
Cheers for responding. Someone suggested that i try using the DCount function - which i did and it solved my problem.

Cheers for your suggestion - i'll save it for when the situation arises again!
 

Users who are viewing this thread

Back
Top Bottom