Delete Query to delete first two rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello,
i am importing a excel table to my access database and i want to delete the
first two rows of the table. these two rows are above the field headings. is
this possible, or it's there a better way to do it. help will be appreciated.
thanks
 
it's there a way to bring the table in through a import or something and at
the same time have a delete query that deletes those first two rows..or
something......this table is being exported from a different program and it's
time consuming exporting it and then go back to the exported table and delete
those rows.....thanks
 
Will said:
i am importing a excel table to my access database and i want to delete the
first two rows of the table. these two rows are above the field headings. is
this possible, or it's there a better way to do it. help will be appreciated.
Click to expand...


I suggest that you define a named range for the data you do
want and import the range.

Lacking that, you can delete rows in the Access table, but
you must have a way to uniquely identify the rows to delete.
Database tables do not have any information about "first
row" so that concept just doesn't hold water.
 
Will said:
I understand that, what i dont understand is how to by doing that?
Click to expand...


What data in the in the first two records that can be used
to uniquely identify them?

When you have determined one or more fields with a value
that can be used to identify the records, then use that in a
Delete query:

CurrentDb.Execute "DELETE * FROM table WHERE field = value"
 
there is actually no data in the first two rows......only in one of the
cellss the date and time it was imported.....the following records that have
data are the ones that have fields headings....
 
Will said:
there is actually no data in the first two rows......only in one of the
cellss the date and time it was imported.....the following records that have
data are the ones that have fields headings....
Click to expand...


Sorry, but that isn't definitive enough for me to work with.
If you will provide an example of the first 4 or 5 rows,
maybe I could propose something.

Did you consider using the named range approach?
 
example of the xl table is....

row1..............|.......|.......................|............|....empty
row2..............|.....|........date/time|..........|.........
row3 OrderNo | ID | RequiredDate | Desc | Comments
0001 fm 4/4/07 any any

i only want to import starting at row3, so i would get rid of row1 and
row2......you say name range approach, how can i go about doing
that.....thanks again
 
Will said:
example of the xl table is....

row1..............|.......|.......................|............|....empty
row2..............|.....|........date/time|..........|.........
row3 OrderNo | ID | RequiredDate | Desc | Comments
0001 fm 4/4/07 any any

i only want to import starting at row3, so i would get rid of row1 and
row2......you say name range approach, how can i go about doing
that.....thanks again
Click to expand...


You could try checking for a Null (or "") in the OrderNo
field. To test that, create a query based on your imported
table:
SELECT * FROM thetable WHERE OrderNo Is Null

Play around with the criteria until the query only returns
the two records you want to delete. Then switch the query
to a DELETE query.

Create a named range in Excel that only covers the desired
data instead of the entire sheet. Then import the range.
This is by far the preferred approach, unless you have no
control of the spreadsheet.
 
well, i have not control of the xl file...it come with those two lines from a
different program and i tried to import it to an existing table that has the
right fields and the wizard doesnt let me do that. I guess, i will have to go
into that xl file to delete the two lines everytime i export that file from
this other program. i thought there was a way of going around that...thanks
for your help though
 
Back
Top