asked on

Sql query conundrum

I have a query, and it retrieves data, but what I need is the OPPOSITE of what this query returns, but I can't get my head around it.

Please let me know if you need more info, and thanks for looking.

SELECT     tblChargeInfo.*
FROM         tblChargeInfo INNER JOIN
                      tblReportData ON tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND 
                      tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber
WHERE     (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1)

Open in new window

HugoHiasl

Hmm.. it would be easier if you describe what you want to get back from the database.

ralmada

Do you mean like this?
....
WHERE (tblChargeInfo.OpenAmount <> 0) AND (tblChargeInfo.Worked <> 1)

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

jcroson

ASKER

Yesterday I ran an UPDATE query on tblChargeInfo, setting anything that had an OpenAmount = 0 AND Worked = 0 to Worked = 1.

Unfortunately, I neglected to update the table tblReportData we use to track changes made to tblChargeInfo, and need to find these 10k records.

The only thing that makes these records unique is that they have no related data in tblReportData, namely the three INNER JOIN matches I used above to find records that meet the OpenAmount = 0 AND the now UPDATEd Worked = 1.

I thought of creating some VB code that would query tblChargeInfo using OpenAmount = 0 AND Worked = 1, step through the returned recordsets, pulling the three JOIN locations in as variables and querying tblReportData to see if a RecordSet is returned, and if not, update tblReportData.

Then I figured someone out there familiar with sql could probably perform this operation in sql.

Sharath S

Do you mean this? Better post sample data and what exactly you want.

SELECT tblChargeInfo.*
FROM tblChargeInfo INNER JOIN
tblReportData ON tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND
tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber
WHERE (tblChargeInfo.OpenAmount <> 0) OR (tblChargeInfo.Worked <> 1)

jcroson

ASKER

Actually, I think aneeshattingal has hit the nail on the head.

This query, as I see it, nests a join to negate my match before.

Right?

jcroson

ASKER

So how could I use this query to update tblReportData thusly:

tblReportData.Worked = tblChargeInfo.Worked
tblReportData.Notes = tblChargeInfo.Notes
tblReportData.ClaimNumber = tblChargeInfo.ClaimNumber
tblReportData.ProcCode = tblChargeInfo.ProcCode
tblReportData.DOS = tblChargeInfo.DOS
tblReportData.UpdateDate = Now()

TIA!

ralmada

So you want to find data that is in tblChargeInfo but is not in tblReportData?
You could do it like this as well:

SELECT     tblChargeInfo.* 
FROM         tblChargeInfo 
left JOIN tblReportData ON 	tblChargeInfo.ProcCode = tblReportData.ProcCode AND 
				tblChargeInfo.DOS = tblReportData.DOS AND  
                      		tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber 
WHERE     (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1) and
	tblReportDate.ProcCode is null

Open in new window

jcroson

ASKER

In addition to the need to find the data pointed out in the solution, I needed to update another table. I found it easier to use the attached VB code for this task.

Option Explicit

Private Sub Form_Load()
   Dim cnnCharge As ADODB.Connection
   Dim rsCharge As ADODB.Recordset
   Dim rsReport As ADODB.Recordset
   Dim i As Integer
   Dim strNotes As String, strClaimNumber As String, strProcCode As String, strDOS As String, strUpdateDate As String
   
   
   Set cnnCharge = New ADODB.Connection
   Set rsCharge = New ADODB.Recordset
   Set rsReport = New ADODB.Recordset
   
   Dim strChargeSql As String
   
    strChargeSql = "SELECT tblChargeInfo.Notes, tblChargeInfo.ClaimNumber, tblChargeInfo.ProcCode, tblChargeInfo.DOS " & _
              "FROM tblChargeInfo " & _
              "WHERE NOT EXISTS (SELECT 1 FROM tblReportData " & _
              "WHERE tblChargeInfo.ProcCode = tblReportData.ProcCode AND tblChargeInfo.DOS = tblReportData.DOS AND " & _
                      "tblChargeInfo.ClaimNumber = tblReportData.ClaimNumber) " & _
                      "AND (tblChargeInfo.OpenAmount = 0) AND (tblChargeInfo.Worked = 1)"

   ' Open a connection by referencing the ODBC driver.
   cnnCharge.ConnectionString = "driver={SQL Server};" & _
      "server=IBSFP;uid=sqluid;pwd=sqlpwd;database=db120ar"
   cnnCharge.Open
   
   rsCharge.Open strChargeSql, cnnCharge

Do While Not rsCharge.EOF

    strNotes = rsCharge.Fields.Item(0)
    strClaimNumber = rsCharge.Fields.Item(1)
    strProcCode = rsCharge.Fields.Item(2)
    strDOS = rsCharge.Fields.Item(3)
    strUpdateDate = Format(Now + 10, "m/d/yyyy")
    
    rsReport.Open "INSERT INTO tblReportData (Worked, Notes, ClaimNumber, ProcCode, DOS, UpdateDate) " & _
            "VALUES (1, '" & strNotes & "', '" & strClaimNumber & "', '" & strProcCode & "', '" & strDOS & "', '" & Format(Now + 20, "m/d/yyyy") & "')", cnnCharge
    rsCharge.MoveNext
Loop

   ' Close the connection.
   cnnCharge.Close
   
MsgBox "All done."
End Sub

Open in new window