Welcome to SPN

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

Sign Up Now!

How can one delete records

Discussion in 'Information Technology' started by Wayne & Kerri Anne, Jul 28, 2006.

Tags:
  1. Wayne & Kerri Anne

    Wayne & Kerri Anne
    Expand Collapse
    Guest

    Hi All

    Can anyone give me a sample of deleting all but the first record in access97
    as I need to use a query to do this.


    Thanks
    Wayne
     
  2. Loading...


  3. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Wayne,

    Yes, you should be able to use a Delete Query for this purpose. The
    specifics will depend on how you identify "the first record" - can you
    give some more details about the data in this table, and an example of
    the single record you wish to retain?

    --
    Steve Schapel, Microsoft Access MVP

    Wayne & Kerri Anne wrote:
    > Can anyone give me a sample of deleting all but the first record in access97
    > as I need to use a query to do this.
    >
     
  4. Wayne & Kerri Anne

    Wayne & Kerri Anne
    Expand Collapse
    Guest

    Steve
    Bit of background on this database is. I wrote the first version in access97
    with all the forms reports. Now I have written this program in VB6 using the
    same db.
    I have a Autonumber field which is a random number. linking this to the
    second table. Fisrt table holds info of an Address Decription of work
    required time frames and so on. the second table holds info of items to do
    at the job location and so on.
    FIRST TABLE
    AutoNumber12354309,InspectionBy,InspectionDate,EllipseCode,Street,Suburb,Limit
    To,Limit From,Description and so on
    SECOND TABLE
    ID number copy of the
    autonmber,workordertype,UseStatus,ITSNumber,Qty,projectNumber,Schedule
    startdate,Schedule Endate and so on.

    I am would like to run a delete query to delete all but one record (dummie)
    in both tables.Why I am doing this is because I have an error in the code
    for vb6 that I haven't yet found a fix for the error is Run time '3426'
    adding a new record to a empty db.

    thanks for your help

    Wayne

    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:uZ7xcGYqGHA.3564@TK2MSFTNGP03.phx.gbl...
    > Wayne,
    >
    > Yes, you should be able to use a Delete Query for this purpose. The
    > specifics will depend on how you identify "the first record" - can you
    > give some more details about the data in this table, and an example of the
    > single record you wish to retain?
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Wayne & Kerri Anne wrote:
    >> Can anyone give me a sample of deleting all but the first record in
    >> access97 as I need to use a query to do this.
    >>
     
  5. Steve Schapel

    Steve Schapel
    Expand Collapse
    Guest

    Wayne,

    I think you will need to run two Delete Queries, one for each table.
    Assuming you can identify the "dummie" record by its ID, the SQL of the
    query will be something like this...
    DELETE * FROM [SECOND TABLE]
    WHERE [ID]<>999

    And then...
    DELETE * FROM [FIRST TABLE]
    WHERE [ID]<>999

    --
    Steve Schapel, Microsoft Access MVP

    Wayne & Kerri Anne wrote:
    > Steve
    > Bit of background on this database is. I wrote the first version in access97
    > with all the forms reports. Now I have written this program in VB6 using the
    > same db.
    > I have a Autonumber field which is a random number. linking this to the
    > second table. Fisrt table holds info of an Address Decription of work
    > required time frames and so on. the second table holds info of items to do
    > at the job location and so on.
    > FIRST TABLE
    > AutoNumber12354309,InspectionBy,InspectionDate,EllipseCode,Street,Suburb,Limit
    > To,Limit From,Description and so on
    > SECOND TABLE
    > ID number copy of the
    > autonmber,workordertype,UseStatus,ITSNumber,Qty,projectNumber,Schedule
    > startdate,Schedule Endate and so on.
    >
    > I am would like to run a delete query to delete all but one record (dummie)
    > in both tables.Why I am doing this is because I have an error in the code
    > for vb6 that I haven't yet found a fix for the error is Run time '3426'
    > adding a new record to a empty db.
     
  6. Wayne & Kerri Anne

    Wayne & Kerri Anne
    Expand Collapse
    Guest

    Thanks Steve
    your right needed two queries
    Wayne
    "Steve Schapel" <schapel@mvps.org.ns> wrote in message
    news:eErYItdqGHA.3248@TK2MSFTNGP04.phx.gbl...
    > Wayne,
    >
    > I think you will need to run two Delete Queries, one for each table.
    > Assuming you can identify the "dummie" record by its ID, the SQL of the
    > query will be something like this...
    > DELETE * FROM [SECOND TABLE]
    > WHERE [ID]<>999
    >
    > And then...
    > DELETE * FROM [FIRST TABLE]
    > WHERE [ID]<>999
    >
    > --
    > Steve Schapel, Microsoft Access MVP
    >
    > Wayne & Kerri Anne wrote:
    >> Steve
    >> Bit of background on this database is. I wrote the first version in
    >> access97 with all the forms reports. Now I have written this program in
    >> VB6 using the same db.
    >> I have a Autonumber field which is a random number. linking this to the
    >> second table. Fisrt table holds info of an Address Decription of work
    >> required time frames and so on. the second table holds info of items to
    >> do at the job location and so on.
    >> FIRST TABLE
    >> AutoNumber12354309,InspectionBy,InspectionDate,EllipseCode,Street,Suburb,Limit
    >> To,Limit From,Description and so on
    >> SECOND TABLE
    >> ID number copy of the
    >> autonmber,workordertype,UseStatus,ITSNumber,Qty,projectNumber,Schedule
    >> startdate,Schedule Endate and so on.
    >>
    >> I am would like to run a delete query to delete all but one record
    >> (dummie) in both tables.Why I am doing this is because I have an error in
    >> the code for vb6 that I haven't yet found a fix for the error is Run time
    >> '3426' adding a new record to a empty db.
     

Share This Page