need Code

[shenberry]

Gosh it has been awhile, but I think I need a code to generate a special report. I have the database setup, with relationships. With these relationship I have generated a query. Now I need specific information to be output as a filter. Now I would like for this query/report/filter to auto update, if possible.



I need to know how many students a teacher has sent to the office. So I have query Teacher Name as well I have Student Name. Now lets say 1 teacher sent the same child to the office, I still want that to be 1. So Teacher: Williams Referral: 1.

It simple but I forgot what to use, as well I was doing this back in 2000, and now it is 2010.

The code doesn't need to duplicate when it gets the same string combination for first and last name (two separate fields), pertaining to a particular teacher; will use students ids later when I get them.

[shenberry]

example of what I need
Data Query
Last Name First Name Teacher Name
London Wil Alexander
London Wil Alexander
London Wil Alexander
London Wil Alexander
Jordan Ty Bates
Hub Nigel Bates
Jordan Ty Bates
Jordan Ty Bates
Green Green Bates
Curry Shayne Bates
Derange Isiah Bates
Tarregano Rob
Bates Green
Green Bates

Alexander = 1
Bates = 6

Thanks and it has been a while so please be a dear and simplify it as much as possible.

[shenberry]

okay I set up a query that just gave me the max per teacher, but the max or not accurate, because it contains duplicates, as well this might be a question for query and not programming, but I will let a mod decide that one.

So Count of Students: Count(*)
Expression

Yields 4 instead of 1, but atleast I converted that data into a number. how can I convert a query to not pick up duplicates?

[LillMcGill]

Group by teacher
Count student

[ssanfu]

You could also try using

SELECT DISTINCT ............

then doing the count .......

Just a thought...