Delete Qry

[Chimp8471]

i need to use a delete query to filter out some data not required,

the trouble is that when i import the data from excel it brings in the headings and on the excel spreadsheet it also totals each day it also brings in the total.

so i end up with for example

i get 33 lines of data that i want
i get one line of data that is the headings from the spreadsheet
i get one line of data that is blank except for the last column with has a total amount in it.

how do i set up a delete query

to delete the headings, and the total figure...but keep the rest of the data

i have attached the spreadsheet i am trying to read from and also the small database that i am trying to setup.

cheers


Andy

 

[bjackson]

when you import a spreadsheet you have the option of
importing the headings,so you can choose not to, which will sort your first problem. then you could run a delete query where Breaklvl=0 as it appears all other Breaklvl=1 on your spreadsheet,
or a delete query where isnull([dcode]) as it appears that field is
only null on the totals row

hope this helps

 

[Chimp8471]

this might sound stupid, but you said i have an option to import headers ???? where when and how would i go about this.

i will also have a look at you other suggestion regarding the delete issue.

cheers

Andy

 

[Jon K]

Try this.

In query SQL View add a Where Clause as follows:-

INSERT INTO [Cases _Equivilant] ( Daycode, Format, Line, CaseEQ )
SELECT ExcelImport.F3 AS Expr1, ExcelImport.F4 AS Expr2, ExcelImport.F5 AS Expr3, ExcelImport.F6 AS Expr4
FROM ExcelImport
WHERE isnumeric(F3);

 

[bjackson]

It looks like Jon K has a good way to do what you want.

for your future reference you can import excel spreadsheets using the docmd or a macro
DoCmd.TransferSpreadsheet acImport, 8, "the table name you want to put the data in", "Your excel file name"l, False, ""

the false part of the docmd is for headings,true if you want headings,false if you dont