How can one delete records

[Wayne & Kerri Anne]

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

 

[Steve Schapel]

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?

 

[Wayne & Kerri Anne]

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]

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

 

[Wayne & Kerri Anne]

Thanks Steve
your right needed two queries
Wayne

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

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.