IIf or Select in Report??

[Steve Albert]

In a report, which is based on a query, I have a field called
[TypeofComplaint]. I have other fields called [EnrollNoSchools],
[PlacementNoSchools], [APENoSchools] , and [VacancyNoSchools], which are
numbers. In the report, I want to have the total number of each of the fields
for each [TypeofComplaint]. I can do it for only one of them, but I am having
trouble with the synatx for nested IIf statements.

The report would look like:

"Type of Complaint" "Number of Schools"
Enrollment 14
Placement 5
APE 3
Vacancy 4

Any help will be appreciated.

- Steve

 

[Ofer]

This query should give you the total for each complaint

SELECT MyTable2.TypeofComplaint, Sum(MyTable2.PlacementNoSchools) AS
SumPlacementNoSchools, Sum(MyTable2.APENoSchools) AS SumAPENoSchools,
Sum(MyTable2.VacancyNoSchools) AS SumVacancyNoSchools
FROM MyTable2
GROUP BY MyTable2.TypeofComplaint

asuming that all fields in the same table.

 

[Steve Albert]

In the report, the group footer has the field [TypeofComplaint] which could
be Enrollment, Placement, etc. There is only one other text box, and it
should contain the sum of [EnrollNoSchools], [PlacementNoSchools],
[APENoSchools] , or [VacancyNoSchools], depending on the Value in
[TypeofComplaint].

If [TypeofComplaint]= Placement, then the other text box should display the
sum of [PlacementNoSchools]. If [TypeofComplaint]= Enrollment, then that same
text box should show the sum of [EnrollNoSchools]. If [TypeofComplaint]=
APE, then sum of APENoSchools]. If [TypeofComplaint]= Vacancy, then it
should show the sum of [VacancyNoSchools].

Wouldn't this need some type of nested IIf statement to go in the text box
in the report? How would I write it? I was having some trouble with the
syntax, particularly with all the parentheses.

Thanks again.

- Steve

This query should give you the total for each complaint

SELECT MyTable2.TypeofComplaint, Sum(MyTable2.PlacementNoSchools) AS
SumPlacementNoSchools, Sum(MyTable2.APENoSchools) AS SumAPENoSchools,
Sum(MyTable2.VacancyNoSchools) AS SumVacancyNoSchools
FROM MyTable2
GROUP BY MyTable2.TypeofComplaint

asuming that all fields in the same table.

In a report, which is based on a query, I have a field called
[TypeofComplaint]. I have other fields called [EnrollNoSchools],
[PlacementNoSchools], [APENoSchools] , and [VacancyNoSchools], which are
numbers. In the report, I want to have the total number of each of the fields
for each [TypeofComplaint]. I can do it for only one of them, but I am having
trouble with the synatx for nested IIf statements.

The report would look like:

"Type of Complaint" "Number of Schools"
Enrollment 14
Placement 5
APE 3
Vacancy 4

Any help will be appreciated.

- Steve

 

[Ofer]

let say that you have on your group footer all four total, they all visible
false.
You have another text box unbound.
on the on print event of the footer you write

select case [TypeofComplaint]
case "Placement"
me.textbox = me.PlacementNoSchools
case "Enrollment"
me.textbox = me.EnrollNoSchools
etc
etc
etc
end select

In the report, the group footer has the field [TypeofComplaint] which could
be Enrollment, Placement, etc. There is only one other text box, and it
should contain the sum of [EnrollNoSchools], [PlacementNoSchools],
[APENoSchools] , or [VacancyNoSchools], depending on the Value in
[TypeofComplaint].

If [TypeofComplaint]= Placement, then the other text box should display the
sum of [PlacementNoSchools]. If [TypeofComplaint]= Enrollment, then that same
text box should show the sum of [EnrollNoSchools]. If [TypeofComplaint]=
APE, then sum of APENoSchools]. If [TypeofComplaint]= Vacancy, then it
should show the sum of [VacancyNoSchools].

Wouldn't this need some type of nested IIf statement to go in the text box
in the report? How would I write it? I was having some trouble with the
syntax, particularly with all the parentheses.

Thanks again.

- Steve

This query should give you the total for each complaint

SELECT MyTable2.TypeofComplaint, Sum(MyTable2.PlacementNoSchools) AS
SumPlacementNoSchools, Sum(MyTable2.APENoSchools) AS SumAPENoSchools,
Sum(MyTable2.VacancyNoSchools) AS SumVacancyNoSchools
FROM MyTable2
GROUP BY MyTable2.TypeofComplaint

asuming that all fields in the same table.

In a report, which is based on a query, I have a field called
[TypeofComplaint]. I have other fields called [EnrollNoSchools],
[PlacementNoSchools], [APENoSchools] , and [VacancyNoSchools], which are
numbers. In the report, I want to have the total number of each of the fields
for each [TypeofComplaint]. I can do it for only one of them, but I am having
trouble with the synatx for nested IIf statements.

The report would look like:

"Type of Complaint" "Number of Schools"
Enrollment 14
Placement 5
APE 3
Vacancy 4

Any help will be appreciated.

- Steve