Query to delete records with values of "--"

Dear all,

I have a table imported into Access the structure is below.  There are 7 consecutive dates.

ID  Latitude  Longitude  19/12/2014  20/12/2014  21/12/2014 etc.

I wrote the below query and it works for that dataset, i.e. the next dataset will have dates for the following week.  Is it possible to write a query to check and delete records where all fields except e.ID, e.Latitude, e.Longitude contain "--"  (without including field names)?

Thanks

SELECT e.ID, e.Latitude, e.Longitude, e.[19/12/2014] AS Expr1, e.[20/12/2014] AS Expr2, e.[21/12/2014] AS Expr3, e.[22/12/2014] AS Expr4, e.[23/12/2014] AS Expr5, e.[24/12/2014] AS Expr6, e.[25/12/2014] AS Expr7 INTO FormattedData
FROM Export_FFDI_Valid_Python AS e
WHERE ((([e].[19/12/2014])<>"--") AND (([e].[20/12/2014])<>"--") AND (([e].[21/12/2014])<>"--") AND (([e].[22/12/2014])<>"--") AND (([e].[23/12/2014])<>"--") AND (([e].[24/12/2014])<>"--") AND (([e].[25/12/2014])<>"--"));

Open in new window

AndyC1000Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
delete *
from Export_FFDI_Valid_Python
where instr([19/12/2014] & [20/12/2014] & [21/12/2014] & [22/12/2014] & [23/12/2014] & [24/12/2014] & [25/12/2014], "--") > 0
0
Dale FyeOwner, Developing Solutions LLCCommented:
because the dates (column headings) of this table are going to change from week to week, you are going to have to build this query at run time.  Personally, I would use a function to return the string, and would call it with something like:

strSQL = fnDeletePython(#19/12/2014#)

then the function would look like:
Public Function fnDeletePython(StartDate as Date) as String

    Dim dtLoop as Date
    Dim strSQL as variant
    
    For dtLoop = StartDate to DateAdd("d", 6, StartDate)
        strSQL = strSQL & "& [" & Format(StartDate, "dd/mm/yyyy") & "] "
    Next
    strSQL = Mid(strSQL, 3)
    strSQL = "DELETE FROM Export_FFDI_Valid_Python " _
                & "WHERE instr(" & strSQL & ", " & chr$(34) & "--" & chr$(34) & ") > 0"
    fnDeletePython = strSQL

End Function

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
Actually, upon re-reading your post, I believe what you want is a string that looks like:

DELETE FROM Export_FFDI_Valid_Python
WHERE [19/12/2014] LIKE "*--*"
AND [20/12/2014] LIKE "*--*"
AND ...

The function for that would look like:
Public Function fnDeletePython(StartDate as Date) as String

    Dim dtLoop as Date
    Dim strSQL as variant
    
    For dtLoop = StartDate to DateAdd("d", 6, StartDate)
        strSQL = strSQL & "AND ([" & Format(StartDate, "dd/mm/yyyy") & "] " _
                     & "LIKE " & chr$(34) & "*--*" & chr$(34) & ") "
    Next

    strSQL = Mid(strSQL, 5)
    strSQL = "DELETE FROM Export_FFDI_Valid_Python WHERE " & strSQL
    debug.print strSQL
    fnDeletePython = strSQL

End Function

Open in new window

0
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Gustav BrockCIOCommented:
The simplest thing you could do, is to export the table without the field names, then attach this:

DoCmd.TransferSpreadsheet  acExport, ,"Export_FFDI_Valid_Python", "c:/test/FFDI.xlsx", False
DoCmd.TransferSpreadsheet acLink, ,"xlsFDDI", "c:/test/FDDI.xlsx", False

This attached table has generic field names: F1, F2, F3, F4, etc.

Now create a query:

Select
    F1 As ID,
    F2 As Latitude,
    F3 As Longitude,
    F4 As Date1,
    F5 AS Date2,
    F6 AS Date3,
    F7 AS Date4,
    F8 AS Date5,
    F9 AS Date6,
    F10 AS Date7
From
    xlsFDDI
Where
    IsDate(F4) And IsDate(F5) And IsDate(F6) And IsDate(F7) And IsDate(F8) And IsDate(F9) And IsDate(F10)

Then use this query as source for your further processing.

Of course, you might be able to attach your original file without the field names, thus skipping the two first steps.

/gustav
0
AndyC1000Author Commented:
In reference to the duplicate question I was looking at two possible solutions. I started with this question whether it was possible to write a generic query to check if any of the fields contain "--", if so delete the entire record.  The original data comes from a netcdf file -  I know that if one of the dates contain "--" it means there are no records for it.  

A netcdf file is downloaded from a server weekly and the python script is used to create the csv which is imported into Access.    

The solutions provided on this question will work, however I'm trying to avoid users altering the date fields.  If I'm wrong please let me know.

The python question was posted after I decided it might be easier not to write those rows of data to the file in the first place.  I'm new to python and haven't been able to get the code to enter the if block where the check is , I.e.

if "masked" not in content: # also tried "--"
   outputwriter.writerow(content)
   print content

Open in new window

Please advise if I should delete the python question as I'm unable to add a python tag to this one.
0
AndyC1000Author Commented:
To clarify this question the below code block writes a csv file.   My issue is when there is no data in the content string for a lat/lon, the row is outputted to the csv file as

 [-33.939999, 151.03918, --, --, --, --, --, --, --]

 or to the console as

 [-33.939999, 151.03918, masked, masked, masked, masked, masked, masked, masked]

 I tried wrapping the outputwriter around an if statement, it doesn't enter the code block. i.e.

 if "masked" not in content: # also tried if "--" not in content
    outputwriter.writerow(content)
    print content

with open(r'C:/output.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')
    for date_val in date_strings:
        header.append(date_val)
    outputwriter.writerow(header)
    for lat_index, lat in enumerate(lats):
        for lon_index, lon in enumerate(lons):
            content = [lat,lon]
            for time_index, time in enumerate(times[:]): 
                data = value[time_index,lat_index,lon_index]
                content.append(data)
            outputwriter.writerow(content) 

Open in new window

0
aikimarkCommented:
This routine will construct and execute the delete statement, based on your criteria.
Sub Q_28627100()
    Dim fld As Field
    Dim strSQL As String
    Dim strPattern As String
    For Each fld In DBEngine(0)(0).TableDefs("Export_FFDI_Valid_Python").Fields
        If IsDate(fld.Name) Then
            strSQL = strSQL & " & " & fld.Name
            strPattern = strPattern & "--"
        End If
    Next
    strSQL = Mid(strSQL, 4)
    strSQL = "Delete * From Export_FFDI_Valid_Python Where " & strSQL & " = " & strPattern
    DBEngine(0)(0).Execute strSQL
End Sub

Open in new window

0
aikimarkCommented:
Correction.  I forgot to square bracket the field names.
Sub Q_28627100()
    Dim fld As Field
    Dim strSQL As String
    Dim strPattern As String
    For Each fld In DBEngine(0)(0).TableDefs("Export_FFDI_Valid_Python").Fields
        If IsDate(fld.Name) Then
            strSQL = strSQL & " & [" & fld.Name & "]"
            strPattern = strPattern & "--"
        End If
    Next
    strSQL = Mid(strSQL, 4)
    strSQL = "Delete * From Export_FFDI_Valid_Python Where " & strSQL & " = " & strPattern
    DBEngine(0)(0).Execute strSQL
End Sub

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
clockwatcherCommented:
Looks like you're probably dealing with a numpy masked array. If you are, you should be able to test for a mask and if it has one, not include it in your csv.  Give the following a try:
import numpy as np
from numpy.ma import getmask
with open(r'C:/output.csv', 'wb') as csvFile:
    outputwriter = csv.writer(csvFile, delimiter=',')
    for date_val in date_strings:
        header.append(date_val)
    outputwriter.writerow(header)
    for lat_index, lat in enumerate(lats):
        for lon_index, lon in enumerate(lons):
            content = [lat,lon]
            skip_row = False
            for time_index, time in enumerate(times[:]): 
                data = value[time_index,lat_index,lon_index]
                if np.any(getmask(data)):
                    skip_row = True
                else:
                    content.append(data)
            if not skip_row:
                outputwriter.writerow(content) 

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.