Calculating Demographics throughout Application Process

[Kevin]

Hi all,

I am trying to calculate demographics for a report throughout a year
long program, beginning with recruitment and ending with graduation.
And I'm failing miserably.

I want to be able to figure out the grade, ethnicity, gender and alumni
status for all points throughout the program. The problem is the
status type builds on each other. In the table below, when figuring
out the demographics for accepted, I also want to include the info for
confirmed, enrolled, etc. because they all had to have been accepted at
one time.

I attempted to use a parameter query in which the user chooses a Status
and the parameter is set to the parameter field, however the only
parameters that worked are the ones with only a number. >=# returned
no results. While that wouldn't put everything on one page for me if
it worked, it would have been a start.

StatusID StatusType Parameter
1 Not Interested 1
2 Inquiry Rec >=1
3 Applicant >=3
4 Declined 4
5 Waitlisted 5
6 Accepted >=6
7 Confirmed >=7
8 Enrolled >=8
9 Missing in Action 9
10 Graduated 10
11 Resigned 11
12 Dismissed 12

If it helps, I would like my report to look like this:

Inquiries Applicants Accepted Confirmed
Enrolled Graduated
Male
Female
African-American
American-Indian
etc.
6th grade (grades are from another table)
7th grade
etc.
1st year (alumni status is from another table)
2nd year
etc.


I know this is a lot, but I haven't been able to figure out how to do
it after trying for weeks. I'm guessing it's going to take some code,
about which I know very little. Thanks if you can help out.

Best,
Kevin Jenkins
City Year New York
Young Heroes Program Manager

 

[Guest]

This is a cross tab query.

You can use a parameter to select records for your cross tab
query, but you can't put the comparison operator into the parameter.
The comparison operator has to be built into the query.

If you sometimes want your query to return = parameter,
and sometimes >= parameter, you have two choices: either

=, one for <=. Then when you run the query, the parameters

will specify the min and max status you want to see.

If you have any other questions, please post the SQL of your
query. Open the query in design view, then switch to SQL
view to see the SQL.

(david)

 

[Kevin]

Thanks a lot. I hand't thought about using a parmeter in a crosstab
query. Now Access is limiting me to one column heading when I need
four. Is there a way around this?

 

[Guest]

Access limits you to one column heading table, not one column heading.
You want your column heading source to be either StatusID, or
StatusType. It doesn't have to be a table, you can use a string list
if you haven't got a table for Status.

You can only have on Value field. You will get a value at the
intersection of every Column and Row. You can have multiple
Row fields, try it out and see the effect.

(david)

 

[Kevin]

Thanks again, David. I was able to get sorta the effect I was looking
for by basing a query on the query with the parameter and using
multiple subreports. And I was able to use a form with a drop down to
indirectly set both the upper and lower limits. It's not the most
elegant solution, but it works until I get a better grasp on Access and
VBA.

Thanks,
Kevin