IIf or Select in Report??

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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
 
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 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


Ofer said:
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 said:
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
Click to expand...
Click to expand...
 
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


Steve Albert said:
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


Ofer said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 
Ofer,

That worked! Now, the report shows many [TypeofComplaints] with their
respective numbers grouped by [Quarter] and then by [SourceofComplaint]. How
do I find the total for those numbers for all [SourceofComplaint]. I cannot
reference the field in the report me.textbox from the example below. The
me.textbox control is the one that needs to be totaled.

Thanks.

- Steve



Ofer said:
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


Steve Albert said:
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


Ofer said:
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
Click to expand...
Click to expand...
Click to expand...
 
Back
Top