Lookup Function 1

[mouseman2006]

I have a class summary report and i would like to find the number of missing assignments. The missing assigment report is from another query. Is there a way i can create a formula in my summary report and obtain the information
from my missing assignment query. Thanks you in advance.

Steve

 

[dhookom]

Missing assignments by what? Date, student, class, ..?
Do you have any table, fields, sample records,... that you would like to share?

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Thanks for responding. I am sorry i was not clear. I am actually trying to count the Completed Assignments field, which is a "Yes" and "No" Data Type and place the information in the summary report. I am trying to obtain a count of students who missed their assignments. I am using the following formula: By the way, "Class Assignments Missed" is a Query

DCount("Completed Assignments","Class Assignments Missed")

Thanks

Steve

 

[dhookom]

To "count" the number of No values in a field named "MyField" in a report, add a text box to the report footer (or group footer) with a control source of:
=Sum([MyField]+1)
This works because the YesNo field stores a value of -1 for Yes and 0 for No. Adding 1 to these values results in yes=0 and no=1. Summing the expression results in a count of No values.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Thanks again for your help, but i decicded to try something different. I think it will be easier for me to count the students who scored "0" on the test (which will mean a student who has to retake an exam or missed the assignment). I am using the following formula:

=DCount("GradeA","Class Assignments Missed")

This formula counts all the missed assignments or who has to retake the exam. The only problem, i am obtaining the same count for every student. Is there way to link it to the count to each student?

Thanks again

 

[dhookom]

If you are counting results from the records within your report you should not use DCount(). To count the number of records in your report where [FieldA] = 0, your control source would be:
=Sum(Abs( [FieldA] = 0) )

Domain aggregate functions such as DCount() and DSum() are not necessary if the "domain" is your report's record source.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Thanks for your help! I see how the formula works. How would i use this formula to pull data from another query that is not part of my report. Thanks again

Stephen

 

[dhookom]

If you want to pull information that is not your report's record source then you can use:
[li]domain aggregate functions[/li]
[li]subreports[/li]
[li]code[/li]
[li]combo boxes[/li]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Thanks i used the combo box, it works like a charm!

Steve

 

[dhookom]

Combo box? That might have been my last choice since it isn't clear how to implement the solution.

Glad to hear you got it working.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Hi Duane,

Thanks for your help again. Last question, the combo box works great, but it does not seem to link the data to a specific student. For example, when i open the report, it has every student missing 5 assignments, which actually it is just from one student. Is there away to link the data to a specific student, so i obtain the information i want? How would i use the Combo Box in the formula?

Thanks again for all your help!

Stephen

 

[dhookom]

Your Row Source for the combo box would be two columns. The first column would be the Student primary key value. The second column would be the count of missing assignments.

The control source of the combo box would be your Student primary key value. The column widths would be 0,1" so the second column would show rather than the first (bound) column.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Hi Again,

I did exactly what you said with the combo box, but i obtain no information. I should have atleast 5 missed assignments for one student, but it is leaving it blank.
I used the following formula:

SELECT [Missed Assignments - Report].[Social Security Number], [Missed Assignments - Report].CountOfGradeA FROM [Missed Assignments - Report];

Any suggestions!

Thanks for your patience

 

[dhookom]

What do you see if you view the sql in datasheet view? What are your combo box properties:

Control Source:
Column Count:
Bound Column:
Row Source:
Column Widths:

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[mouseman2006]

Duane, Thanks again for your help! I got it working correctly with a subreport. Thanks

Stephen