Link to home
Start Free TrialLog in
Avatar of MedSouth
MedSouth

asked on

Help with building a select statement.

I have 3 tables (Requests, Payments and Notes) What I want to do is cleanup all outdated records ie: stuff older than a year from the tables.  The common field between then tables is Request_ID and is unique in the Requests table.  Now here's the rub the payments table has the information about how long ago it was paid (actualy it is the date of the payment but for my purposes this date will do).  So far my Select statement looks like this.

Select * from Requests Where REQ_Status = 'PRO' and PaymentStatus = 6

    (This selects "ALL" records that have been processed (PRO) and have been paid (6) reguardless of the date it was paid)

How do I add something like Payments.PaymentDate > a year ago to my select statement.  There may be only one payment for each or several but I only need the most recent payment.  If that date is over a year I want to delete the Record from Requests and all related records from the Payments and Notes tables.

Thanks in advance
Dave


   
ASKER CERTIFIED SOLUTION
Avatar of jamcos
jamcos

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
oops, error

should read:

Select RequestID, Max(PaymentDate) from Payments where PaymentDate < DateAdd("Y",-1,Now) Group By RequestID

Then to delete those requestIDs from the Requests table (and, with Cascade delete ON, to also delete all of the corresponding related records from Payments and Notes:

Delete * from Requests where RequestID IN
(Select RequestID from
     (Select RequestID, Max(PaymentDate) from Payments where PaymentDate <                DateAdd("Y",-1,Now) Group By RequestID)
)
Avatar of MedSouth
MedSouth

ASKER

Oops:
    I spoke to soon.  The tables are Requests, TRANSACTIONS and Notes.  Sorry about that!   I do not know if it case sensitive or not but those are my exact table names.  Also this is a MS Access database  and I'm using Visual Studio 2005 (vb.net) to do this through and I see no "Access Relationship window" I also tried to modify your select statement from the very top just to get a count of records that i might delete later and I get an exception in my try block "Syntax error in FROM clause."

Here is my new modified select statement:
Select * from Requests JOIN TRANSACTIONS ON Requests.REQ_ID = TRANSACTIONS.REQ_ID Where REQ_Status = 'PRO' and PaymentStatus = 6 AND TRANSACTIONS.Date < DateAdd("Y",1,Now)

Here is the whole project source code:
Public Class Form1
    Dim dbsource As New BindingSource
    Dim dbstart, dbend As Date
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        tbSelect.Text = "Select * from Requests JOIN TRANSACTIONS ON Requests.REQ_ID = TRANSACTIONS.REQ_ID Where REQ_Status = 'PRO' and PaymentStatus = 6 AND TRANSACTIONS.Date < DateAdd("Y",1,Now)"
        lblWorking.Visible = False
    End Sub
    Private Shared Function GetData(ByVal sqlCommand As String) As DataTable
        Dim connString As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=S:\msdrjdata2007.mdb"
        Dim dbConnection As OleDb.OleDbConnection = New OleDb.OleDbConnection(connString)
        Dim command As New OleDb.OleDbCommand(sqlCommand, dbConnection)
        Dim adapter As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter()
        adapter.SelectCommand = command
        Dim table As New DataTable
        table.Locale = System.Globalization.CultureInfo.InvariantCulture
        adapter.Fill(table)
        Return table
    End Function
    Private Sub cmdShowCounts_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdShowCounts.Click
        lblNumRows.Text = "Record Count: " & (DataGridView1.RowCount - 1)
    End Sub
    Private Sub cmdEXEQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdEXEQuery.Click
        querystart()
        Try
            ' Set up the DataGridView.
            With Me.DataGridView1
                ' Automatically generate the DataGridView columns.
                .AutoGenerateColumns = True
                ' Set up the data source.
                dbsource.DataSource = GetData(tbSelect.Text)
                .DataSource = dbsource
                ' Automatically resize the visible rows.
                .AutoSizeRowsMode = DataGridViewAutoSizeRowsMode.DisplayedCellsExceptHeaders
                ' Set the DataGridView control's border.
                .BorderStyle = BorderStyle.Fixed3D
                ' Put the cells in edit mode when user enters them.
                .EditMode = DataGridViewEditMode.EditOnEnter
            End With
        Catch ex As OleDb.OleDbException
            MessageBox.Show("Database to Grid View Fill Error", "Fill Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            MsgBox(ex.Message)
            System.Threading.Thread.CurrentThread.Abort()
        End Try
        queryend()
        DataGridView1.Refresh()
        lblNumRows.Text = "Record Count: " & (DataGridView1.RowCount - 1) & "   "
    End Sub
    Private Sub querystart()
        dbstart = Now()
        lblWorking.Visible = True
        lblWorking.Refresh()
        lblStart.Text = "Start Time: " & dbstart
        lblStart.Refresh()
        lblDuration.Text = "Duration: "
        lblEnd.Text = "End Time: "
    End Sub
    Private Sub queryend()
        dbend = Now()
        lblWorking.Visible = False
        lblWorking.Refresh()
        lblDuration.Text = "Duration: " & DateDiff(DateInterval.Second, dbstart, dbend)
    End Sub
End Class

Try this change:

        tbSelect.Text = "Select * from Requests JOIN TRANSACTIONS ON Requests.REQ_ID = TRANSACTIONS.REQ_ID Where REQ_Status = 'PRO' and PaymentStatus = 6 AND TRANSACTIONS.Date < DateAdd('Y',-1,Now)"

mis-matched "s

AW
I assumed that you were in Access itself (hence the Relationship window references).  If you can open the Access MDB is design mode, then you can set the Relationships there, and make the delete of the associated records that much easier.  Other wise, you will need to affect three delete statements, one to delete the necessary Req_IDs from wach of the three tables - they cannot all be deleted in a single SQL statement.

AW
Arthur:

    I swapped out the select statement above and I still get a "syntax error in from clause" exception.  I have dug a little further and it seems that in the Requests table there is no primary key the REQ_ID is indexed but allows duplicates.  I have made my backup though and I would like to test this on my backup instead of the live data.  I have also installed access on my machine now so I can use that (Which sounds easier for the deletion process).  THanks in advance.

Dave
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
How iis it that the Requests table allows multiple entries with the same Req_ID?  What is Req_ID supposed to be, if not the ID of a specific Request entry?

can you attach a representative copy of your database to your question, and I will take a look.
It seems you dont really need help building a select statement, rather just how to delete the correct records.

Is this a one-time operation? If so my comment ID: 22080135 might be quickest and easiest option for you, otherwise you will need to provide mroe schema information.