How can one delete records

#1 (**permalink**) 28-Jul-2006, 08:36 AM

Hi All

Can anyone give me a sample of deleting all but the first record in access97
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/13353-how-can-one-delete-records.html
as I need to use a query to do this.

Thanks
Wayne

*

#2 (**permalink**) 28-Jul-2006, 08:36 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
give some more details about the data in this table, and an example of
the single record you wish to retain?
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353

--
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.
>

#3 (**permalink**) 28-Jul-2006, 08:36 AM

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
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
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,Ell ipseCode,Street,Suburb,Limit
To,Limit From,Description and so on
SECOND TABLE
ID number copy of the
autonmber,workordertype,UseStatus,ITSNumber,Qty,pr ojectNumber,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" 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.
>>

#4 (**permalink**) 28-Jul-2006, 08:36 AM

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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
> FIRST TABLE
> AutoNumber12354309,InspectionBy,InspectionDate,Ell ipseCode,Street,Suburb,Limit
> To,Limit From,Description and so on
> SECOND TABLE
> ID number copy of the
> autonmber,workordertype,UseStatus,ITSNumber,Qty,pr ojectNumber,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.

#5 (**permalink**) 28-Jul-2006, 08:37 AM

Thanks Steve
your right needed two queries
Wayne
"Steve Schapel" 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.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
>> 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,Ell ipseCode,Street,Suburb,Limit
>> To,Limit From,Description and so on
>> SECOND TABLE
>> ID number copy of the
>> autonmber,workordertype,UseStatus,ITSNumber,Qty,pr ojectNumber,Schedule
>> startdate,Schedule Endate and so on.
>>
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=13353
>> 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.