if statement

[ncm]

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[KARL DEWEY]

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

 

[ncm]

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

 

[KARL DEWEY]

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

 

[Mark]

Now that you have a query that works you may find it easy to create a report
that will provide you the information you need.

 

[ncm]

This is the statement I entered:

SELECT tblCalculatations.Parent, tblCalculatations.School,
tblCalculatations_child_count.CountOfChild AS [Children
this School], tblCalculatations.Child, tblCalculatations.Grade
FROM tblCalculatations INNER JOIN tblCalculatations_child_count ON
(tblCalculatations.School = tblCalculatations_child_count.School)
AND (tblCalculatations.Parent = tblCalculatations_child_count.Parent)
ORDER BY tblCalculatations.Parent, tblCalculatations.School,
tblCalculatations.Grade DESC;

Am I taking your code to literally?
I get an error: invalid braceting of name 'children in this school'

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

 

[KARL DEWEY]

I get an error: invalid braceting of name 'children in this school'
Check for added return in 'AS [Children this School]' because copying and
pasting from post can insert an additional character.

--
KARL DEWEY
Build a little - Test a little

This is the statement I entered:

SELECT tblCalculatations.Parent, tblCalculatations.School,
tblCalculatations_child_count.CountOfChild AS [Children
this School], tblCalculatations.Child, tblCalculatations.Grade
FROM tblCalculatations INNER JOIN tblCalculatations_child_count ON
(tblCalculatations.School = tblCalculatations_child_count.School)
AND (tblCalculatations.Parent = tblCalculatations_child_count.Parent)
ORDER BY tblCalculatations.Parent, tblCalculatations.School,
tblCalculatations.Grade DESC;

Am I taking your code to literally?
I get an error: invalid braceting of name 'children in this school'

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[ncm]

i fixed me other error. But this is not working. Do I need to have 2
tables, 1 ncm w/ parent, school, child, grade...what does the other one have.

I created an identical table, 1) there is no count, 2) I get duplicate
records.

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

 

[KARL DEWEY]

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little

i fixed me other error. But this is not working. Do I need to have 2
tables, 1 ncm w/ parent, school, child, grade...what does the other one have.

I created an identical table, 1) there is no count, 2) I get duplicate
records.

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[ncm]

The statement you gave me to use did not work. If I use it as it I get inner
join errors.

I guess what I am asking is, do I need 2 tables to run this query? My
original table that has parent, school, student and grade, and another table
called child_count table? Is that correct? If so, what fields should be in
the child_count table?

I did create a child_count table with fields of parent, school and
CountOfChild (if I didn't include CountOfChild in the table, when you run the
query if asks for the value). When I run the query nothing is in the
CountofChild field.

This is the exact statement in my query:
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Student, ncm.Grade
FROM ncm Left JOIN ncm_child_count ON ncm.Parent=ncm_child_count.Parent
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

My ultimate goal is to be able to determine how much tuition each child will
pay. If there are more than 1 children in a family in the same school, they
will get a discount.

Thank you very much for your help.

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little

i fixed me other error. But this is not working. Do I need to have 2
tables, 1 ncm w/ parent, school, child, grade...what does the other one have.

I created an identical table, 1) there is no count, 2) I get duplicate
records.

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[ncm]

Karl -
I have been playing with this and I think the problem is, I don't need to
count how the number of students but I need to assign them a number depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little

i fixed me other error. But this is not working. Do I need to have 2
tables, 1 ncm w/ parent, school, child, grade...what does the other one have.

I created an identical table, 1) there is no count, 2) I get duplicate
records.

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[KARL DEWEY]

do I need 2 tables to run this query?
You do not need two tables. "child_count" is the earlier query that
counts children.
My query sorts the oldest (highest grade level) first in the school. I
do not know how you plan to use the data later.
--
KARL DEWEY
Build a little - Test a little

Karl -
I have been playing with this and I think the problem is, I don't need to
count how the number of students but I need to assign them a number depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little

i fixed me other error. But this is not working. Do I need to have 2
tables, 1 ncm w/ parent, school, child, grade...what does the other one have.

I created an identical table, 1) there is no count, 2) I get duplicate
records.

:

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[KARL DEWEY]

Maybe this is what you need --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], Max(ncm.Grade) AS [Highest Grade]
FROM ncm INNER JOIN ncm_child_count ON (ncm.Parent = ncm_child_count.Parent)
AND (ncm.School = ncm_child_count.School)
GROUP BY ncm.Parent, ncm.School, ncm_child_count.CountOfChild
ORDER BY ncm.Parent, ncm.School, Max(ncm.Grade) DESC;

It omits child's name.
Parent School Children this School Highest Grade
Sally Jones County High 1 11
Sally Jones St. Elizabeth 2 3

--
KARL DEWEY
Build a little - Test a little

Karl -
I have been playing with this and I think the problem is, I don't need to
count how the number of students but I need to assign them a number depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little

i fixed me other error. But this is not working. Do I need to have 2
tables, 1 ncm w/ parent, school, child, grade...what does the other one have.

I created an identical table, 1) there is no count, 2) I get duplicate
records.

:

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School = ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each school.
Now what I need is to know who is the first, second, third, etc. student in
each school. The ultimate goal here is to calculate the tuition for each
student. There is a tuition break for each kid a family has in the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student discount
and so on. the grade level is what determines which student is first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child attends in a
table. I need to determine the number of students each parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade 10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count for Sally's
kids at the other school

 

[John Spencer]

Take a look at RANKING queries

Select Parent, School, Grade, Child
, DCount("*","YourTable","Grade<=" & Grade & " AND Parent =""" & Parent &
""" AND School = """ & School & """") as Rank
FROM YourTable
Order By School, Parent, Grade

The above assumed that Grade was a number field. If Grade is not a number
field but is text and can contain values that are not numbers then we have
further work to do.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Maybe this is what you need --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], Max(ncm.Grade) AS [Highest Grade]
FROM ncm INNER JOIN ncm_child_count ON (ncm.Parent =
ncm_child_count.Parent)
AND (ncm.School = ncm_child_count.School)
GROUP BY ncm.Parent, ncm.School, ncm_child_count.CountOfChild
ORDER BY ncm.Parent, ncm.School, Max(ncm.Grade) DESC;

It omits child's name.
Parent School Children this School Highest Grade
Sally Jones County High 1 11
Sally Jones St. Elizabeth 2 3

--
KARL DEWEY
Build a little - Test a little

Karl -
I have been playing with this and I think the problem is, I don't need to
count how the number of students but I need to assign them a number
depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The
oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little


:

i fixed me other error. But this is not working. Do I need to have
2
tables, 1 ncm w/ parent, school, child, grade...what does the other
one have.

I created an identical table, 1) there is no count, 2) I get
duplicate
records.

:

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS
[Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School =
ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each
school.
Now what I need is to know who is the first, second, third, etc.
student in
each school. The ultimate goal here is to calculate the tuition
for each
student. There is a tuition break for each kid a family has in
the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student
discount
and so on. the grade level is what determines which student is
first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child
attends in a
table. I need to determine the number of students each
parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade
10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count
for Sally's
kids at the other school

 

[ncm]

This is exactly what I need. My grade is text due to Pre-K and K but I can
work around that.

Thank you very much!

Take a look at RANKING queries

Select Parent, School, Grade, Child
, DCount("*","YourTable","Grade<=" & Grade & " AND Parent =""" & Parent &
""" AND School = """ & School & """") as Rank
FROM YourTable
Order By School, Parent, Grade

The above assumed that Grade was a number field. If Grade is not a number
field but is text and can contain values that are not numbers then we have
further work to do.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Maybe this is what you need --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], Max(ncm.Grade) AS [Highest Grade]
FROM ncm INNER JOIN ncm_child_count ON (ncm.Parent =
ncm_child_count.Parent)
AND (ncm.School = ncm_child_count.School)
GROUP BY ncm.Parent, ncm.School, ncm_child_count.CountOfChild
ORDER BY ncm.Parent, ncm.School, Max(ncm.Grade) DESC;

It omits child's name.
Parent School Children this School Highest Grade
Sally Jones County High 1 11
Sally Jones St. Elizabeth 2 3

--
KARL DEWEY
Build a little - Test a little

Karl -
I have been playing with this and I think the problem is, I don't need to
count how the number of students but I need to assign them a number
depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The
oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

:

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little


:

i fixed me other error. But this is not working. Do I need to have
2
tables, 1 ncm w/ parent, school, child, grade...what does the other
one have.

I created an identical table, 1) there is no count, 2) I get
duplicate
records.

:

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS
[Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School =
ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each
school.
Now what I need is to know who is the first, second, third, etc.
student in
each school. The ultimate goal here is to calculate the tuition
for each
student. There is a tuition break for each kid a family has in
the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student
discount
and so on. the grade level is what determines which student is
first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child
attends in a
table. I need to determine the number of students each
parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade
10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count
for Sally's
kids at the other school

 

[ncm]

I just discovered a glitch with having grade defined as a number and not
text. Can you please tell me what I need to do to make this ranking query
work with grade as text?

Also, is it possible to round a number to the nearest 100?

Take a look at RANKING queries

Select Parent, School, Grade, Child
, DCount("*","YourTable","Grade<=" & Grade & " AND Parent =""" & Parent &
""" AND School = """ & School & """") as Rank
FROM YourTable
Order By School, Parent, Grade

The above assumed that Grade was a number field. If Grade is not a number
field but is text and can contain values that are not numbers then we have
further work to do.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Maybe this is what you need --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS [Children
this School], Max(ncm.Grade) AS [Highest Grade]
FROM ncm INNER JOIN ncm_child_count ON (ncm.Parent =
ncm_child_count.Parent)
AND (ncm.School = ncm_child_count.School)
GROUP BY ncm.Parent, ncm.School, ncm_child_count.CountOfChild
ORDER BY ncm.Parent, ncm.School, Max(ncm.Grade) DESC;

It omits child's name.
Parent School Children this School Highest Grade
Sally Jones County High 1 11
Sally Jones St. Elizabeth 2 3

--
KARL DEWEY
Build a little - Test a little

Karl -
I have been playing with this and I think the problem is, I don't need to
count how the number of students but I need to assign them a number
depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The
oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

:

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little


:

i fixed me other error. But this is not working. Do I need to have
2
tables, 1 ncm w/ parent, school, child, grade...what does the other
one have.

I created an identical table, 1) there is no count, 2) I get
duplicate
records.

:

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS
[Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School =
ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in each
school.
Now what I need is to know who is the first, second, third, etc.
student in
each school. The ultimate goal here is to calculate the tuition
for each
student. There is a tuition break for each kid a family has in
the school.
So, Student 1 gets full tuition charged, student 2 gets 2 student
discount
and so on. the grade level is what determines which student is
first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the child
attends in a
table. I need to determine the number of students each
parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james- grade
10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another count
for Sally's
kids at the other school

 

[John Spencer]

Perhaps the simplest fix would be to add a table to your structure with the
following fields
GradeName: Text Field with whatever grade name you want
GradePositon: Number field that will allow you to sort the grade names in
order Pre-k = 1, K =11, 1st = 20, 2nd = 22, etc.

Then change the ranking expression to
DCount("*","YourNEWTable","GradePosition<=" & GradePostion & " AND Parent
=""" & Parent & """ AND School = """ & School & """") as Rank

Select Parent, School, Grade, Child
, DCount("*","YourNewTable","GradePosition<=" & GradePosition & " AND
Parent =""" & Parent &
""" AND School = """ & School & """") as Rank
FROM YourTable INNER JOIN YourNewTable
ON YourTable.Grade = YourNewTable.GradeName
Order By School, Parent, Grade

Round to the nearest hundred
Round((YourNumber + .000001)/100,0) *100

This always rounds 50 up to the next 100. If you use
Round(YourNumber/100,0) * 100 the rounding will use Banker's rounding.
50 would round to the nearest even one hundred ( 150 rounds to 200, and 250
rounds to 200)

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

I just discovered a glitch with having grade defined as a number and not
text. Can you please tell me what I need to do to make this ranking query
work with grade as text?

Also, is it possible to round a number to the nearest 100?

Take a look at RANKING queries

Select Parent, School, Grade, Child
, DCount("*","YourTable","Grade<=" & Grade & " AND Parent =""" & Parent &
""" AND School = """ & School & """") as Rank
FROM YourTable
Order By School, Parent, Grade

The above assumed that Grade was a number field. If Grade is not a
number
field but is text and can contain values that are not numbers then we
have
further work to do.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Maybe this is what you need --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS
[Children
this School], Max(ncm.Grade) AS [Highest Grade]
FROM ncm INNER JOIN ncm_child_count ON (ncm.Parent =
ncm_child_count.Parent)
AND (ncm.School = ncm_child_count.School)
GROUP BY ncm.Parent, ncm.School, ncm_child_count.CountOfChild
ORDER BY ncm.Parent, ncm.School, Max(ncm.Grade) DESC;

It omits child's name.
Parent School Children this School Highest Grade
Sally Jones County High 1 11
Sally Jones St. Elizabeth 2 3

--
KARL DEWEY
Build a little - Test a little


:

Karl -
I have been playing with this and I think the problem is, I don't need
to
count how the number of students but I need to assign them a number
depending
on their grade in the same school.

So, 3 kids, all in the same family, all go to the same school. The
oldest
kid would be 1, middle kid = 2 and youngest = 3.

Does that make sense?

:

You lost me with this post.
--
KARL DEWEY
Build a little - Test a little


:

i fixed me other error. But this is not working. Do I need to
have
2
tables, 1 ncm w/ parent, school, child, grade...what does the
other
one have.

I created an identical table, 1) there is no count, 2) I get
duplicate
records.

:

How is this --
SELECT ncm.Parent, ncm.School, ncm_child_count.CountOfChild AS
[Children
this School], ncm.Child, ncm.Grade
FROM ncm INNER JOIN ncm_child_count ON (ncm.School =
ncm_child_count.School)
AND (ncm.Parent = ncm_child_count.Parent)
ORDER BY ncm.Parent, ncm.School, ncm.Grade DESC;

--
KARL DEWEY
Build a little - Test a little


:

Very cool!
This will tell me the number of students each parent has in
each
school.
Now what I need is to know who is the first, second, third,
etc.
student in
each school. The ultimate goal here is to calculate the
tuition
for each
student. There is a tuition break for each kid a family has
in
the school.
So, Student 1 gets full tuition charged, student 2 gets 2
student
discount
and so on. the grade level is what determines which student
is
first, second
and so on.

:

Try this --
SELECT ncm.Parent, ncm.School, Count(ncm.Child) AS
CountOfChild
FROM ncm
GROUP BY ncm.Parent, ncm.School;

--
KARL DEWEY
Build a little - Test a little


:

I have a list of students, parents and the school the
child
attends in a
table. I need to determine the number of students each
parent has in a
school. I would like to get this from a query.

In excel, i sort by parent, then school.

column A = Parent Name
column B = School
Column E = Number of children at the school

=IF(A1242<>A1241,1,IF(B1242<>B1241,1,E1241+1))
so, for example,
parent = Sally Jones
School = St. Elizabeth
Sally's kids, sue = grade 12, justin - grade 11, james-
grade
10
sue 1
justin 2
james 3

If Sally has kids in another school we would do another
count
for Sally's
kids at the other school