Re: SQL Statement for limiting the number of detail retrieved in Access 2000?

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

OK, I understand.

Can anyone give an code example of what Izzy suggests??

Thanks,

Hexman


On 25 Sep 2006 06:03:29 -0700, "Izzy" <israel.richner@xxxxxxxxx> wrote:

Martin is right, this cannot be handled in a query.

This will need to be handled in application code.

The only way this would work in a query is if the only 1 record was
returned by t1.

Izzy


Pritcham wrote:
Hi

I don't think you're going to be able to do this as the "Top x" clause
is going to limit your resultset to x number of records, not x number
of records for each 'grouping' (i.e. the link between your two tables)
so no matter how you try to link the results of this subquery to your
master table, you're only ever (at best) going to get x records from
the subquery.

Cheers
Martin

Hexman wrote:
Izzy,

Thanks for the response. I still can't get it to work, either with Access or SS EE. It generates no error during execution, but doesn't return any
rows. If I take out the "(SELECT TOP 3 t2.* FROM t2)" and change all "t3" references to "t2", it retrieves the rows (except too many of them).

This has me stumped. Any further ideas?

All I want to do is limit the number of rows returned from t2 to be joined with t1.

Thanks,

Hexman


On 23 Sep 2006 16:17:22 -0700, "Izzy" <israel.richner@xxxxxxxxx> wrote:

Try this: (Or something of the like)

SELECT t1.*, t3.*
FROM t1
INNER JOIN
(SELECT TOP 3 t2.*
FROM t2) t3 ON t1.Item = t3.Item
WHERE (t1.TrnDate = #09/23/06#)

You may even want too use a LEFT OUTER JOIN for this, depends on your
data.

Izzy

Hexman wrote:
Hello All,

How do I limit the number of detail records selected in a Master-Detail set using SQL?

I want to select all master records for a date, but only the first 3 records for the details (based on the primary key of the detail record). I've
been trying with "TOP 3", but can't get anywhere. Using Access 2000.

Something like:

SELECT t1.*, TOP 3 t2.*
FROM t1, t2
WHERE (t1.item = t2.item) AND (t1.TrnDate = #09/23/06#)

Any help appreciated,

Hexman
.

Relevant Pages

  • Re: SQL Statement for limiting the number of detail retrieved in Access 2000?
    ... Does this solve the problem Hexman? ... WHERE OrderDetails.LineItem IN (Select Top 3 LineItem FROM OrderDetails ... How do I limit the number of detail records selected in a Master-Detail set using SQL? ... I want to select all master records for a date, but only the first 3 records for the details. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL Statement for limiting the number of detail retrieved in Access 2000?
    ... Hexman wrote: ... INNER JOIN ... How do I limit the number of detail records selected in a Master-Detail set using SQL? ... I want to select all master records for a date, but only the first 3 records for the details. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: SQL Statement for limiting the number of detail retrieved in Access 2000?
    ... this cannot be handled in a query. ... INNER JOIN ... How do I limit the number of detail records selected in a Master-Detail set using SQL? ... I want to select all master records for a date, but only the first 3 records for the details. ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Print Form/Report with Master and one Detail record per page?
    ... For the purpose the forms were designed for, the subform ... worked correctly showing all detail records for any master ... I did not have a query ...
    (microsoft.public.access.formscoding)
  • Re: General Query question
    ... FROM dbo_tblSaaDocument RIGHT JOIN (dbo_tblObligationType INNER JOIN ... I would like to have the master table updated. ... redundancy, which thereby avoids redundancy. ... You store data ONCE, and once only, and then use Queries linking the ...
    (microsoft.public.access.gettingstarted)