Results 1 to 3 of 3
  1. #1
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    update/insert multiple records (A2k3 SP2)

    <P ID="edit" class=small>(Edited by gdrezek on 12-Apr-06 14:27. clicked too soon....added text file)</P>Hi All,
    I"ve worked myself into a circle.
    In dealing with a Scholarship database, the student's program data (i.e. college major course of study) is updated yearly. Certain scholarships require certain majors. I've got no problems with this update/insert procedure until students with multiple majors appear.
    The 2 rules are:
    <UL><LI>If the latest download data includes a change in majors, the latest data is added as new
    <LI>If there is no change in majors, then the old data is updated; i.e. their advisor, start date, college name, department, but not the Program code number[/list]My problem:
    When a student has a double (or more) major, then they appear twice in the "old data" and most likely will appear twice in the "new data". So for each loop iteration I will get one record updated and the other added new........there will be 4 records now, when there should only be 2. Even if I do a "SELECT DISTINCT" on just the Student's ID number and use that to grab records from the 2 data sources mentioned, I still end up with double the records I should. I can see how this is happening, I just can't get a grip on how to do this the way I need to.

    Attached please find a text file with the following 3 subs included:
    <UL><LI>cmdImportProgramData_Click() - the OnClick event of the button on the Import Data form
    <LI>TempTableProgramInfo(strTableName) - the create table code for the new data temp table (included mostly for information)
    <LI>ImportProgramData() - the import program data routine[/list]Thank you.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

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

    Re: update/insert multiple records (A2k3 SP2)

    By using a double loop, you have insufficient control over the matching. I would create some queries instead:

    1) An update query based on on TempTableProgramInfo and tblProgramInformation with an inner join on CLGID vs ColleagueID and Program vs JMCJG03SPProgram that updates the appropriate fields in tblProgramInformation with the values of the corresponding fields in TempTableProgramInfo.

    2) A query based on TempTableProgramInfo and tblProgramInformation with a left join on CLGID vs ColleagueID and Program vs JMCJG03SPProgram. Add TempTableProgramInfo.* and tblProgramInformation.ColleagueID to the query grid, with Is Null in the Criteria line for the latter. Change the query to an append query with target tblProgramInformation. Clear the Append To box for ColleagueID.

    You can execute these queries in code, using DoCmd.OpenQuery or CurrentDb.Execute.

  3. #3
    4 Star Lounger
    Join Date
    Jan 2002
    Location
    Brookings, South Dakota, USA
    Posts
    449
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: update/insert multiple records (A2k3 SP2)

    Thank you Hans......... <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23> need I say it works just the way it should? <img src=/S/fanfare.gif border=0 alt=fanfare width=31 height=23>
    As an added bonus, this method also runs about 20 times faster as well.
    I gotta learn a whole lot more about queries. This is like the umpteenth time you've shown me their power.
    Thanks again.
    <IMG SRC=http://www.wopr.com/w3tuserpics/gdrezek_sig.jpg>
    "Those who dance are considered insane by those who can't hear the music" - George Carlin

Posting Permissions

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