Results 1 to 3 of 3
  1. #1
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    SQL query question - once again! (Access2K, Win2KPro)

    I thought I might get what I wanted done using LIKE in a SQL string but I am starting to get really confused about the whole thing. My original attack on the problem is coming back to me, enticing me with it's potential charms....

    Ok, well, enough of that -- basically, this is more of a Recordset manipulation problem & something I'd likely do in ASP but basically the whole thing operates off of Access, so I thought this would be a good place to find a solution.

    PROBLEM:
    I have a db with a table for initial staffings and logic that ensures that there will always be a unique staffing case number. I have another table that auto-increments a Monthly ID number. I have a third table where Follow-up records go. Unfortunately, I was a bit too clever and decided to take advantage of the existing MonthlyID and StaffingCaseNumber info to create a Follow-up number 'on the fly' that is composed of the Staffing Case Number and MonthlyID as well as some other stuff. All this is fine, but I forgot to provide for the ability to delete a Staffing Case AND all associated Follow-ups. Instead, I allowed the user to delete an Initial Staffing. I have since fixed the problem, but the existing data definately has some 'orphaned' Follow-Ups.

    What I need to do is, page thru the Follow Up table and find and delete all follow-ups that don't have a corresponding Initial Staffing. It's not hard to match Follow-ups and Initial Staffings, but the other way around is giving me fits.

    What I envision (not knowing if this is even possible) is to run a query which will inspect the Initial Staffing table for each Follow-up, mark the follow-up for deletion if there is no corresponding Initial Staffing (using InStr), keep going, etc. in one loop and then a subsequent delete of all the Follow-ups that were marked for deletion. Is this possible? Can you mark a row for deletion and later delete it? I like the idea of setting up the compare loop and then deleting rather than doing both at once -- early attempts just confused my computer, but perhaps there's a known method for doing that as well.

    I am just feel weird deleting records in the middle of a loop...it feels wrong, somehow -- crass, perhaps even borish. But if ya'll do this sort of thing all the time then I'll just have to put aside my misbegotten sense of db etiquette...

    Anyway, TIA for any help you can give. <img src=/S/boxedin.gif border=0 alt=boxedin width=25 height=20>

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: SQL query question - once again! (Access2K, Win2KPro)

    Why do this in a loop? I would design a series of queries:
    - a select query based on the follow-up table that calculates the Staffing Case Number from the Follow Up Number, using Left, Mid or whatever.
    - a select query based on the first one and the Initial Staffing table that selects all orphaned follow-ups, perhaps using the Find Unmatched Query Wizard.
    - a delete query based on the previous one that deletes the orphans.

  3. #3
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    austin, Texas, USA
    Posts
    1,029
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: SQL query question - once again! (Access2K, Win2KPro)

    I did do most of that already except I can't get the Find Unmatched query to work. But I put together a query that builds the expected string for locating Initial Staffing cases with Follow-ups and can run an additional query that maps Initial Staffings to Followups using InStr (in Access). You get an integer result from InStr; 0 means no match, >0 means a match. The Find Unmatched doesn't work because I am trying to find orphaned records based on a part of a string, not the whole thing -- that's why I use InStr.

    The Follow-up number is composed of the following data elements:

    "FU " & StaffingCaseNumber & " " & MonthlyID & " " & FollowUpPeriod <-- I can't predict the last part of the string. I can assemble a list of possible followups from the Initial Staffing Table by concatenating "FU " & StaffingCaseNumbe & " " & MonthlyID and store this as a query result. What I need to do next is compare each record in FollowUp table to find ones without a match in the concatenated list. To my little blonde mind, doing this in a loop is easy to understand. When I assemble a query, it just lists the data row-by-row from one table to another; I need to compare all the rows in one table with each row in another.

    Attached is a partial result of a query with comments on what it is showing and why it doesn't work. Also, since I'm doing all this in ASP on one end, I can easily identify the records outside of an Access query. I'd like to run thru the loop, mark the records and later delete them via ASP....

    After looking around some more, I found something called Batch Updates. I'll try that out in ASP and see if it works. Have you called DELETE using adLockBatchOptimistic and then executed the DELETE using UpdateBatch? If so, any 'gotchas'?
    Attached Files Attached Files

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •