Re: Limit Report to the 1st 30 records

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Thanks for your help, I to have a busy day and won't get a chance to check
it out till to night,
thanks again Blair
"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:%23y0g12WXGHA.128@xxxxxxxxxxxxxxxxxxxxxxx
You can try the query below. I have no way to test it. I suggest you
paste
it into a new query. If you get an error, please post the error message
back to the group. I'll try to check back later today, but it's a busy
day
today and I may not get back. If not, perhaps Tom Ellison will look over
our shoulders and post something more elegant (or at least point out my
error).

Assumptions:
You have a control on the form with the name
[Forms]![FFirstMating]![txtGetNumber]
You don't need to show the ranking.

SELECT
[98MatingRecords].[SHED #]
, [98MatingRecords].[NEST #]
, [98MatingRecords].Section
, [98MatingRecords].[FEMALE #]
, [98MatingRecords].[Mating Year]
, [98MatingRecords].[1st MATING]
, [98FemaleHistory].Dead
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
[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 #]
[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]![txtGetNumber] )
ORDER BY [98MatingRecords].[NEST #];


"Blair" <bemullen@@@ns.sympatico.ca> wrote in message
news:OXrRFkWXGHA.3760@xxxxxxxxxxxxxxxxxxxxxxx
Here is the SQL
Primary key in [98MatingRecords] is [Female #] and [Mating Year]
together,
In [98FemaleHistory] it is [Female #] only

SELECT TOP 30 [98MatingRecords].[SHED #], [98MatingRecords].[NEST #],
[98MatingRecords].Section, [98MatingRecords].[FEMALE #],
[98MatingRecords].[Mating Year], [98MatingRecords].[1st MATING],
[98FemaleHistory].Dead
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
(([98MatingRecords].[1st
MATING]) Is Null) AND (([98FemaleHistory].Dead)=No))
ORDER BY [98MatingRecords].[NEST #];

Thanks Blair

"John Spencer" <spencer@xxxxxxxxx> wrote in message
news:Ohxe6WWXGHA.3440@xxxxxxxxxxxxxxxxxxxxxxx
No, you can't use a parameter to change the TOP nn. You would have to
use
VBA to rewrite the query.

An alternative would be to use a ranking query. That could use the
parameter (a reference to a control) but it would be slower. If you
are
interested in that please post the SQL of your current query. Perhaps
I
or
someone else, can then modify it. Also, tell us the name of the form
and
control that will contain the value you want to use to limit the record
and
identify the primary key field in your table(s).

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message


"Blair" <bemullen@@@ns.sympatico.ca> wrote in message
news:e2gCnOWXGHA.3848@xxxxxxxxxxxxxxxxxxxxxxx
I used The top property and it works great, Thanks

Now I tried to put a form text box control but I can't, I can only
use
a
number greater than zero. Is there an easy way to be able to change
the
number using a text box?

Thanks Blair










.

Relevant Pages

  • Re: Count to 8 and start over
    ... So, here is my SQL for my query, but the RunningTtl field does ... FROM tblQualityInspectors INNER JOIN (tblProductLine INNER JOIN ... > then make visible the control to the left, else, make visible the> control to ... Extra work, maybe, but the end result is obtained,> and ...
    (microsoft.public.access.queries)
  • Re: Parameter Query Errors
    ... ControlNum] or or some other variant. ... You query would look something like: ... FROM (Dealer INNER JOIN (Customers INNER JOIN Orders ON ... criteria against the Control Number. ...
    (microsoft.public.access.queries)
  • RE: DLookup in CrossTabQuery form
    ... You cannot update a query that is grouping. ... I looked at the "Add Existing Fields" Control they did not appear. ... FROM WStuContacts INNER JOIN WCredits ON WStuContacts.SNum = WCredits.SNum ... The dLookup is contained in an unbound text control in my form. ...
    (microsoft.public.access.forms)
  • Re: Limit Report to the 1st 30 records
    ... FROM 98FemaleHistory As F INNER JOIN 98MatingRecords as M ... VBA to rewrite the query. ... An alternative would be to use a ranking query. ... parameter (a reference to a control) but it would be slower. ...
    (microsoft.public.access.reports)
  • Re: repost: Registering Provider
    ... Allright, I just did. ... I don't control how and when they reply - they are ... busy just like you and me, but historically their responses have always been ...
    (microsoft.public.dotnet.framework.adonet)