none
SQL Help RRS feed

  • Question

  • I'm still on Access 2003. I got a table, Offenses, to which I want to append some records from CFA and then do 2 updates. For the purposes of this question, the fields in Offenses are Name, Offense1, Offense2, Offense3.

    I use an append query to fill Name and Offense1 from CFA (Name, Charge) based on some criteria. Multiple charges may exist in CFA for the same name but name is unique to Offenses.

    Here is where I need help:  I now need to update Offense2 to a second instance of Charge if it exists. And as if that isn't cumbersome enough, I also need to update Offense3 to yet another charge that isn't = to Offense1 or Offense2 if exist.

    I'd rather not use DLookup if it can be avoided. I assume there is some way of using a join or EXISTS

    Thanks for any reply


    50% of programming is coding. The other 90% is debugging

    Sunday, August 30, 2015 7:10 PM

Answers

  • Thank you The DB Guy. I expected the updates to be separate and normalization is not an option. I am working with existing system which doesn't utilize of primary keys in many of the tables and frankly break some of the technical rules of databases. However, I came up with a query that I tested on the first update and it runs efficiently and satisfactorily. It is basically an update query using a subquery and the not-equal '<>' operator. I haven't looked into it yet but I should be able to easily modify this for the second update.

    docmd.RunSQL "UPDATE [Offenses] AS O " _
        & "INNER JOIN (SELECT NCR.Name, NCR.Address, NCR.County, CFA.[Statute_#] & ' - ' & CFA.[Statute_Description] AS [Offense] " _
        & "     FROM [Offenses] AS NCR " _
        & "     INNER JOIN [County FL - All] AS CFA " _
        & "     ON CFA.Address_1 = NCR.Address And Trim(Nz(CFA.First_Name) & ' ' & Nz(CFA.Last_Name)) = NCR.Name " _
        & "     WHERE CFA.[Statute_#] & ' - ' & CFA.[Statute_Description] <> NCR.Offense1) AS T2 " _
        & "ON T2.Name = Cust.Name And T2.Address = Cust.Address And T2.County = Cust.County " _
        & "SET Cust.[Offense2] = T2.[Offense]"
    


    50% of programming is coding. The other 90% is debugging

    Sunday, August 30, 2015 9:18 PM

All replies

  • Hi. I think the safest way is to update each column, one at a time. However, the best answer is to "normalize" the data in the table.

    Assuming normalization is not an option, you could start with the Find Unmatched Query Wizard. You can then create a Totals query based on that to only return the first unmatched offense for each name. Then, you should be able to use that query to add the records to the table. Repeat the process for the next column.

    Hope that helps...

    Sunday, August 30, 2015 7:33 PM
  • Thank you The DB Guy. I expected the updates to be separate and normalization is not an option. I am working with existing system which doesn't utilize of primary keys in many of the tables and frankly break some of the technical rules of databases. However, I came up with a query that I tested on the first update and it runs efficiently and satisfactorily. It is basically an update query using a subquery and the not-equal '<>' operator. I haven't looked into it yet but I should be able to easily modify this for the second update.

    docmd.RunSQL "UPDATE [Offenses] AS O " _
        & "INNER JOIN (SELECT NCR.Name, NCR.Address, NCR.County, CFA.[Statute_#] & ' - ' & CFA.[Statute_Description] AS [Offense] " _
        & "     FROM [Offenses] AS NCR " _
        & "     INNER JOIN [County FL - All] AS CFA " _
        & "     ON CFA.Address_1 = NCR.Address And Trim(Nz(CFA.First_Name) & ' ' & Nz(CFA.Last_Name)) = NCR.Name " _
        & "     WHERE CFA.[Statute_#] & ' - ' & CFA.[Statute_Description] <> NCR.Offense1) AS T2 " _
        & "ON T2.Name = Cust.Name And T2.Address = Cust.Address And T2.County = Cust.County " _
        & "SET Cust.[Offense2] = T2.[Offense]"
    


    50% of programming is coding. The other 90% is debugging

    Sunday, August 30, 2015 9:18 PM
  • Hi. Glad to hear you're making good progress. Good luck with your project. Let us know if you get stuck...
    Sunday, August 30, 2015 9:26 PM