Results 1 to 7 of 7
  1. #1
    Join Date
    Apr 2004
    Location
    Stratford, CT
    Posts
    5

    Red face Unanswered: Help on Comparing 2 tables

    Here's what i need to do, in a nutshell. Im not versed in Acess so I could really use some help.

    I have two tables...one is IMPORT and one is STUDENTS. Each table has a common field of STUDENT ID. I need to compare the STUDENT ID field in IMPORT to the STUDENT ID Table in STUDENTS. For any records in IMPORT that are not found in STUDENTS i need to place that in a NEW_STUDENTS table. If a matching record is found i need to call a procedure called "PROCESS_STUDENT". At the end of the whole process, if any records exist in the NEW_STUDENTS table, i need to call a procedure titled "LIST NEW STUDENTS".

    This doesnt seem complicated, but im not very good at access. Can someone point me in the right direction?

    Thanks,

    Adam

  2. #2
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    Well you should be able to do what you need using a query. Don't know how familiar you are with them, but what you need to do is create a new query (in Design View). In the Show Table window that appears add the table IMPORT. In the Query menu at the top choose Append - this will cause the query to insert records into the table of your choice. So you will need to select NEW_STUDENTS as the table to append to.

    In the first column of the query, choose table IMPORT and field StudentID. Append To should contain StudentID (this is the field in NEW_STUDENTS it is appended to).

    In Criteria enter:

    NOT IN (Select StudentID from STUDENTS)

    When you run the query you should find the new records in NEW_STUDENTS.

    If you want to do multiple operations you can either use VBA or a macro, as you'll need to create a report and open it afterwards, and you may want to delete the records you appended from the import table.

    Hope this helps.

  3. #3
    Join Date
    Apr 2004
    Location
    Stratford, CT
    Posts
    5
    Ok thats sounds great, Ill try that out, its a good start. Now the only thing I have to figure out is how to add the extra steps to call two other procedures. Ill probably have to do this in VB? Would it be hard to do the entire process in VB?

    Thanks

    Adam

  4. #4
    Join Date
    Apr 2004
    Location
    Stratford, CT
    Posts
    5
    Ok thats sounds great, Ill try that out, its a good start. Now the only thing I have to figure out is how to add the extra steps to call two other procedures. Ill probably have to do this in VB? Would it be hard to do the entire process in VB?

    Thanks

    Adam

  5. #5
    Join Date
    Apr 2004
    Location
    Stratford, CT
    Posts
    5

    Post


  6. #6
    Join Date
    Mar 2004
    Location
    Brighton, UK
    Posts
    25
    No it shouldn't be hard at all. Create a command button on a form, then go to the properties of the button, and to the onclick event. Choose Event Procedure then click the ... button. In the code window that pops up you need to add the lines:

    docmd.OpenQuery "MyQueryname"
    docmd.openreport "MyReportNane"

    If you wanted to open a new form showing the added records, then

    docmd.openform "MyFormName"

    If you want to get rid of some records from the import table then create a delete query and open it in the same way as above. If you want to hide the message warnings you that you are appending records/deleting records from tables then you need the line:

    DoCmd.SetWarnings False

    and to turn on again:

    DoCmd.SetWarnings True

    Then click your button and something will happen, hopefully!

    Good luck

  7. #7
    Join Date
    Apr 2004
    Location
    Stratford, CT
    Posts
    5
    OK got it. The last think I think I need to do is this: if a match is found i need to call a procedure. Also, at the end, I need to check the NEW STUDENTS Table for data. If it contains any, i need to call one more procedure then clear the data out. If I follow the above steps you gave me, how can i insert the calls ?

    Adam

Posting Permissions

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