Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

selecting only the 10th item from a list of 10000

Discussion in 'Information Technology' started by ally123, Jul 28, 2006.

  1. ally123

    ally123
    Expand Collapse
    Guest

    Hello All,
    I am pretty new to the SQL world. I would really appreciate someones
    help here. The logic i have is that if the count of items in a group
    are less than 1000 then i need that entire group
    if the the count is greater than 1000 in a group then i need to select
    every 10th item in that group and multiply one of its attributes by 10.

    Can pleeeeease someone help me on that?
     
  2. Loading...

    Similar Threads Forum Date
    Gurus Original Hukamnamas From 10th King History of Sikhism Feb 18, 2015
    USA Akal Takhat Jathedar Attends 10th Death Anniversary Yogi Bhajan Breaking News Oct 9, 2014
    Sikh Philosophy Network - Celebrating 10th Amazing Year! Sikh Sikhi Sikhism May 31, 2013
    Best Video for 10th Anniversary SPN Celebration Thread - Vote Now! Opinion Polls May 31, 2013
    Sukhmani Sahib:10th Ashtapadee:8th Pauri:My understanding Sukhmani Sahib Apr 6, 2012

  3. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    You will find an example here:

    http://www.mvps.org/access/queries/qry0006.htm

    As far as which record is the 1st, 10th, etc, it will depend on the sorting
    order in the query. You would need to get a record count also. You would
    probably do this before you open the query so that you will know whether or
    not you need to perform the calculation. To get the record count, run the
    DCount() function on the entire recordset then run the query with the update
    if the count is >1000.

    Example:
    If DCount("*", "tblMyTable") > 1000 Then
    strSQL = "UPDATE tblMyTable SET tblMyTable.Field1 = tblMyTable.Field1 *
    10 WHERE PlusOne([Field1]) Mod 10 = 0;"
    'If using a global variable
    'i = 0
    CurrentDb.Execute strSQL, dbFailOnError
    End If

    Add an ORDER BY clause to the above SQL to sort the table in the order
    desired.

    The reason the example states that it will affect every nth record, but not
    necessarily the desired record is because of the Static variable (Static i
    As Integer). It will remember its value from the last time it was called. If
    you change it to a global variable (Public i As Integer) then set its value
    to zero before executing the query, you will always know where it is
    starting from, it that matters. If you make it a global variable, you may
    want to use a more distinctive name than "i" so that you don't accidentally
    try to use the name for a local variable in another routine.

    --
    Wayne Morgan
    MS Access MVP


    "ally123" <aliasgark@gmail.com> wrote in message
    news:1150487534.976563.95730@i40g2000cwc.googlegroups.com...
    > Hello All,
    > I am pretty new to the SQL world. I would really appreciate someones
    > help here. The logic i have is that if the count of items in a group
    > are less than 1000 then i need that entire group
    > if the the count is greater than 1000 in a group then i need to select
    > every 10th item in that group and multiply one of its attributes by 10.
    >
    > Can pleeeeease someone help me on that?
    >
     
  4. KARL DEWEY

    KARL DEWEY
    Expand Collapse
    Guest

    Your data records needs to be numbered - an Autonumber field will do nicely.

    Use a macro with condition to check >1000 records and run select query.
    The next line in the macro have condition to check >=1000 records and run an
    update query. Add a field in the design view like this --
    X: [YouAutonumberField] MOD 10
    Set criteria to a number like zero or your choosing between 0 and 9. If you
    use 0 then every record ending in zero is selected.

    "ally123" wrote:

    > Hello All,
    > I am pretty new to the SQL world. I would really appreciate someones
    > help here. The logic i have is that if the count of items in a group
    > are less than 1000 then i need that entire group
    > if the the count is greater than 1000 in a group then i need to select
    > every 10th item in that group and multiply one of its attributes by 10.
    >
    > Can pleeeeease someone help me on that?
    >
    >
     
  5. ally123

    ally123
    Expand Collapse
    Guest

    hey thank you guys for replying so quickly. I am using RAPID SQL can
    you help me out in those terms . I am sorry i did not mention that
    earlier
    KARL DEWEY wrote:
    > Your data records needs to be numbered - an Autonumber field will do nicely.
    >
    > Use a macro with condition to check >1000 records and run select query.
    > The next line in the macro have condition to check >=1000 records and run an
    > update query. Add a field in the design view like this --
    > X: [YouAutonumberField] MOD 10
    > Set criteria to a number like zero or your choosing between 0 and 9. If you
    > use 0 then every record ending in zero is selected.
    >
    > "ally123" wrote:
    >
    > > Hello All,
    > > I am pretty new to the SQL world. I would really appreciate someones
    > > help here. The logic i have is that if the count of items in a group
    > > are less than 1000 then i need that entire group
    > > if the the count is greater than 1000 in a group then i need to select
    > > every 10th item in that group and multiply one of its attributes by 10.
    > >
    > > Can pleeeeease someone help me on that?
    > >
    > >
     
  6. Wayne Morgan

    Wayne Morgan
    Expand Collapse
    Guest

    Are you connecting to Rapid SQL using linked tables in Access? If so, the
    previous suggestion should work.

    --
    Wayne Morgan
    MS Access MVP


    "ally123" <aliasgark@gmail.com> wrote in message
    news:1150491503.597817.126740@r2g2000cwb.googlegroups.com...
    > hey thank you guys for replying so quickly. I am using RAPID SQL can
    > you help me out in those terms . I am sorry i did not mention that
    > earlier
     

Share This Page