Results 1 to 9 of 9
  1. #1
    Join Date
    Dec 2005
    Posts
    5

    Unanswered: limiting a table rows

    Hi all.

    I have a table that may be grown according to a user actions on it.
    I want to limit the rows in the table. For instance, I want that the table will contain only the final 32 rows from 140 rows that currently exist in it.
    The table has 'ID' column as primary key in it, but I should take account that this ID is not consequential number.

    What should I do?

  2. #2
    Join Date
    Dec 2005
    Posts
    5

    limiting a table rows

    Hi all.

    I have a table that may be grown according to a user actions on it.
    I want to limit the rows in the table. For instance, I want that the table will contain only the final 32 rows from 140 rows that currently exist in it.
    The table has 'ID' column as primary key in it, but I should take account that this ID is not consequential number.

    What should I do?

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    My guess is that you don't care about the table itself, only the results that the user sees. The exact mechanics of how you do that are database engine specific, but not difficult to implement in most cases.

    What database engine are you using?

    -PatP

  4. #4
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by bezaleli
    I want that the table will contain only the final 32 rows from 140 rows that currently exist in it.
    Hi

    Firstly you need to define what the final 32 rows are. Although there is an aparent meaning in the order of rows in a table this is not true - there is no such meaning in a relational database. So you need to define how you deriving the last 32.

    Also - would a query be enough to do this rather than delete? And if you do need to delete, does this need to be rolling so that as a new row is added the "earliest" row is deleted?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Concur with the "Audacious Flump Cuddler", providing you have an easy means of identifying the last 32 rows in the table then it should be a breeze.

    The only effective way to do this is either with an autonumber field or a date / time field.

    If you do not have such an easy method then you have a problem. You will need to either revisit the table design to include such a parameter or find an alternative way of doing it.

    You cannot rely on SQL returning results in a order unless you specify. It is highly likely that SQL will return them in the same unspecified sequence as the last time, but you don't know for certain. As a consequence its always a good idea to specify an order to a SQL select statement.

    In the face of it the "top n" sub clause should do the trick

    eg
    select top 32 from tblOrders where ManuID=blah order by OrderID DESC; 'for a seqential orderid
    select top 32 from tblOrders where ManuID=blah order by OrderDate DESC; 'for an order date

    you may need to also look at "group by" and "having" - depends on your requirement. But even if you don't need them for this exercise, they are usefull to read up on, as they will be usefull at some stage. The help has some reasonable examples.
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    atgofia erioed ad 'r bstards chrensia 'ch i lawr
    only just twigged it is Welsh - I'll have it translated soon You trying to fox those island hoppers across t'waters?

    So - some Welsh blood in them there lancy viens?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Dec 2005
    Posts
    5
    I am using MS-Access :Microsoft.Jet.OLEDB.4.0;Jet OLEDB:Engine Type=5;

    Thanks

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Thees reet tha'nose, on both counts. Its enough of a struggle for the little darlings, whom I consider SEFL's (speaking English as a Foreign language), to understand English without cottoning on to Cymraig

    Now as to identifing the language: thats one for being slow.......
    Last edited by healdem; 01-26-06 at 12:07.
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    I'm moving this thread to the Microsoft Access Forum. There are a lot of folks that can give you better insight into the Jet database engine, and the "care and feeding" of its files than I can there.

    -PatP

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •