Limit Report to the 1st 30 records

The point of the last query was to see what values were returned in the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED #])=[Forms]![FFirstMating]![Text20]) AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

OK! Gotchya
They are in numerical order, but depending what section # you put in depends
what group and range comes up. So for section 1 the range is from 1-53 and
215-263 but any nests that the animal has died and is marked dead wont be
there or if the nest is just empty, if I put in section 2 then the range
would be 54-103 and 264-312. So in section 1 there are 76 nests being used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP VALUES
field worked and that is what I'll use if we can't make this work. I would
like to be able to control that # because not all our sheds have the same
number of nest's in each section. Some I might want the first 20 the next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method in other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED #])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

So the ranking isn't working as expected. It should be generating numbers
from 1 to N. Where the maximum of N would be the number of records returned
in the query.

(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

At this point, I am stuck. Would it be possible for you to strip your
database down to the two tables and send that to me so I can test solutions?
IF you can ZIP the two tables to under 2 megabytes (or at least a sampling
of the two tables) and send it to me I will take a look at developing a
solution. My email server automatically kills .mdb attachments. If your
database is small enough, you can send it without zipping just change the
extension on the file to .bdm

Insert .UMBC before the .EDU in my email address.



"Blair" wrote in message
...
OK! Gotchya
They are in numerical order, but depending what section # you put in
depends
what group and range comes up. So for section 1 the range is from 1-53 and
215-263 but any nests that the animal has died and is marked dead wont be
there or if the nest is just empty, if I put in section 2 then the range
would be 54-103 and 264-312. So in section 1 there are 76 nests being used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP VALUES
field worked and that is what I'll use if we can't make this work. I would
like to be able to control that # because not all our sheds have the same
number of nest's in each section. Some I might want the first 20 the next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method in
other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records
instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED #])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

Thanks a bunch But I will have to do in the next couple of days cause
daughter has 16th birthday party this afternoon and tonight, but I will do
that
Thanks Blair
"John Spencer" wrote in message
...
So the ranking isn't working as expected. It should be generating numbers
from 1 to N. Where the maximum of N would be the number of records
returned
in the query.

(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

At this point, I am stuck. Would it be possible for you to strip your
database down to the two tables and send that to me so I can test
solutions?
IF you can ZIP the two tables to under 2 megabytes (or at least a
sampling
of the two tables) and send it to me I will take a look at developing a
solution. My email server automatically kills .mdb attachments. If your
database is small enough, you can send it without zipping just change the
extension on the file to .bdm

Insert .UMBC before the .EDU in my email address.



"Blair" wrote in message
...
OK! Gotchya
They are in numerical order, but depending what section # you put in
depends
what group and range comes up. So for section 1 the range is from 1-53
and
215-263 but any nests that the animal has died and is marked dead wont
be
there or if the nest is just empty, if I put in section 2 then the range
would be 54-103 and 264-312. So in section 1 there are 76 nests being
used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP VALUES
field worked and that is what I'll use if we can't make this work. I
would
like to be able to control that # because not all our sheds have the
same
number of nest's in each section. Some I might want the first 20 the
next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method in
other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records
instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED
#])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

Sent the db on the weekend wondering if you got it ok
Blair
"John Spencer" wrote in message
...
So the ranking isn't working as expected. It should be generating numbers
from 1 to N. Where the maximum of N would be the number of records
returned
in the query.

(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

At this point, I am stuck. Would it be possible for you to strip your
database down to the two tables and send that to me so I can test
solutions?
IF you can ZIP the two tables to under 2 megabytes (or at least a
sampling
of the two tables) and send it to me I will take a look at developing a
solution. My email server automatically kills .mdb attachments. If your
database is small enough, you can send it without zipping just change the
extension on the file to .bdm

Insert .UMBC before the .EDU in my email address.



"Blair" wrote in message
...
OK! Gotchya
They are in numerical order, but depending what section # you put in
depends
what group and range comes up. So for section 1 the range is from 1-53
and
215-263 but any nests that the animal has died and is marked dead wont
be
there or if the nest is just empty, if I put in section 2 then the range
would be 54-103 and 264-312. So in section 1 there are 76 nests being
used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP VALUES
field worked and that is what I'll use if we can't make this work. I
would
like to be able to control that # because not all our sheds have the
same
number of nest's in each section. Some I might want the first 20 the
next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method in
other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records
instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED
#])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

Just got into the office. I've been offsite with customers.


"Blair" wrote in message
...
Sent the db on the weekend wondering if you got it ok
Blair
"John Spencer" wrote in message
...
So the ranking isn't working as expected. It should be generating
numbers
from 1 to N. Where the maximum of N would be the number of records
returned
in the query.

(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

At this point, I am stuck. Would it be possible for you to strip your
database down to the two tables and send that to me so I can test
solutions?
IF you can ZIP the two tables to under 2 megabytes (or at least a
sampling
of the two tables) and send it to me I will take a look at developing a
solution. My email server automatically kills .mdb attachments. If your
database is small enough, you can send it without zipping just change the
extension on the file to .bdm

Insert .UMBC before the .EDU in my email address.



"Blair" wrote in message
...
OK! Gotchya
They are in numerical order, but depending what section # you put in
depends
what group and range comes up. So for section 1 the range is from 1-53
and
215-263 but any nests that the animal has died and is marked dead wont
be
there or if the nest is just empty, if I put in section 2 then the
range
would be 54-103 and 264-312. So in section 1 there are 76 nests being
used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP
VALUES
field worked and that is what I'll use if we can't make this work. I
would
like to be able to control that # because not all our sheds have the
same
number of nest's in each section. Some I might want the first 20 the
next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method in
other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in
the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records
instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED
#])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

All;

Examined the data and sample query I was sent and built a query based on the
information Blair sent. The final query is shown below. At this point, I
don't know if this is giving Blair what he wanted or not.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead, 1+(SELECT Count(*) FROM 98FemaleHistory As F
INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) AS Ranking
FROM 98FemaleHistory INNER JOIN 98MatingRecords ON [98FemaleHistory].[FEMALE
#] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED #])=[Forms]![FFirstMating]![Text20]) AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=2006) AND (([98MatingRecords].[1st
MATING]) Is Null) AND (([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))=CLng(Nz([Num of records to
return (blank=30)],30))))
ORDER BY [98MatingRecords].[NEST #];

"John Spencer" wrote in message
...
Just got into the office. I've been offsite with customers.


"Blair" wrote in message
...
Sent the db on the weekend wondering if you got it ok
Blair
"John Spencer" wrote in message
...
So the ranking isn't working as expected. It should be generating
numbers
from 1 to N. Where the maximum of N would be the number of records
returned
in the query.

(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

At this point, I am stuck. Would it be possible for you to strip your
database down to the two tables and send that to me so I can test
solutions?
IF you can ZIP the two tables to under 2 megabytes (or at least a
sampling
of the two tables) and send it to me I will take a look at developing a
solution. My email server automatically kills .mdb attachments. If
your
database is small enough, you can send it without zipping just change
the
extension on the file to .bdm

Insert .UMBC before the .EDU in my email address.



"Blair" wrote in message
...
OK! Gotchya
They are in numerical order, but depending what section # you put in
depends
what group and range comes up. So for section 1 the range is from 1-53
and
215-263 but any nests that the animal has died and is marked dead wont
be
there or if the nest is just empty, if I put in section 2 then the
range
would be 54-103 and 264-312. So in section 1 there are 76 nests being
used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP
VALUES
field worked and that is what I'll use if we can't make this work. I
would
like to be able to control that # because not all our sheds have the
same
number of nest's in each section. Some I might want the first 20 the
next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method in
other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in
the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records
instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED
#])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];

Yes John this is working Great I didn't use (Blank =30) part but it is what
I wanted to accomplish
Thanks a Bunch
Blair
"John Spencer" wrote in message
...
All;

Examined the data and sample query I was sent and built a query based on
the
information Blair sent. The final query is shown below. At this point, I
don't know if this is giving Blair what he wanted or not.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead, 1+(SELECT Count(*) FROM 98FemaleHistory As F
INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) AS Ranking
FROM 98FemaleHistory INNER JOIN 98MatingRecords ON
[98FemaleHistory].[FEMALE
#] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED #])=[Forms]![FFirstMating]![Text20]) AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22]) AND
(([98MatingRecords].[Mating Year])=2006) AND (([98MatingRecords].[1st
MATING]) Is Null) AND (([98FemaleHistory].Dead)=No) AND ((1+(SELECT
Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))=CLng(Nz([Num of records
to
return (blank=30)],30))))
ORDER BY [98MatingRecords].[NEST #];

"John Spencer" wrote in message
...
Just got into the office. I've been offsite with customers.


"Blair" wrote in message
...
Sent the db on the weekend wondering if you got it ok
Blair
"John Spencer" wrote in message
...
So the ranking isn't working as expected. It should be generating
numbers
from 1 to N. Where the maximum of N would be the number of records
returned
in the query.

(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

At this point, I am stuck. Would it be possible for you to strip your
database down to the two tables and send that to me so I can test
solutions?
IF you can ZIP the two tables to under 2 megabytes (or at least a
sampling
of the two tables) and send it to me I will take a look at developing
a
solution. My email server automatically kills .mdb attachments. If
your
database is small enough, you can send it without zipping just change
the
extension on the file to .bdm

Insert .UMBC before the .EDU in my email address.



"Blair" wrote in message
...
OK! Gotchya
They are in numerical order, but depending what section # you put in
depends
what group and range comes up. So for section 1 the range is from
1-53
and
215-263 but any nests that the animal has died and is marked dead
wont
be
there or if the nest is just empty, if I put in section 2 then the
range
would be 54-103 and 264-312. So in section 1 there are 76 nests
being
used
at this time out of the 101

The solution you gave me inputting a number in the properties TOP
VALUES
field worked and that is what I'll use if we can't make this work. I
would
like to be able to control that # because not all our sheds have the
same
number of nest's in each section. Some I might want the first 20 the
next
shed I might want the first 30 records
So that is what we are trying to do.

I really appreciate your time and help, Also I can use this method
in
other
queries if we get it to work.
Thanks again Blair
"John Spencer" wrote in message
...
The point of the last query was to see what values were returned in
the
ranking field.
Are they sequential numbers?
Are they right aligned?
Do the numbers go from 1 to 76? Or do they show some other range?


"Blair" wrote in message
...
I pasted the code you sent and it still is showing all 76 records
instead
of
the 20 in the text75 control
thanks for your help
Blair

"John Spencer" wrote in message
...
Let's see if we are correctly calculating the ranking.

SELECT [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
, 1+ (SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as
M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]) as Ranking

FROM 98FemaleHistory INNER JOIN 98MatingRecords
ON [98FemaleHistory].[FEMALE #] = [98MatingRecords].[FEMALE #]
WHERE ((([98MatingRecords].[SHED
#])=[Forms]![FFirstMating]![Text20])
AND
(([98MatingRecords].Section)=[Forms]![FFirstMating]![Text22])
AND
(([98MatingRecords].[Mating Year])=Year(Now())) AND
(([98FemaleHistory].Dead)=No) AND ((1+(SELECT Count(*)
FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M
ON [F].[FEMALE #] = [M].[FEMALE #]
WHERE
[M].[SHED #]=[Forms]![FFirstMating]![Text20] AND
[M].Section=[Forms]![FFirstMating]![Text22] AND
[M].[Mating Year]=Year(Now()) AND
[M].[1st MATING] Is Null AND
[F].Dead=No AND
[M].[Nest #] [98MatingRecords].[NEST #]))
=CLng([Forms]![FFirstMating]![Text75])))
ORDER BY [98MatingRecords].[NEST #];