MS Access Auto-populate Table

Posted on 2014-08-12
Last Modified: 2014-08-18
I have what should be a relatively simple question.  I apologize for not providing an Access attachment; but I am having problems opening any updated Access attachments that are sent back to me.  I have attached an Excel Spreadsheet that should effectively illustrate what I would like to accomplish:

Essentially, I have 2 tables (Table1 and Table2) which are already pre-populated with 3 record-sets of data.  

1.       I would like to create a 3rd table (Table3) that auto-populates based on and if certain conditions are met from the other two tables.  If the condition is met, the designated field should be populated with “Delete—“, if the condition is not met, the designated should simply remain blank.

2.      I would also like to ensure that record-sets in my new Table (Table3) are in sync with the record-sets in Table 1 and Table 2 (a friend already linked the records sets of Table1 and Table2 together—although I’m not sure how he did it; so I’m only concerned with syncing up Table3).

I know the basics of Access, but I don’t have a lot of experience in some of these more customized/complicated tasks… so if someone could hold my hand and give me step-by-step instructions on how to make this happen, it would be most appreciated.

Please review the attached Spreadsheet.  You will notice that there are 3 conditions I’d like to program into this Table:

If Question1 = 1; then Condition3 = "Delete--" Otherwise, Condition3 = ""
If Question1 = 2, Question4 = 4 AND Question6 = 2; then Condition1 = "Delete--" Otherwise, Condition1 = ""
If Question4 = 2, Question5 = 1 OR Question8 = 2; then Condition2 = --"Delete--" Otherwise, Condition2 = ""
Question by:mdstalla
    LVL 84
    I'm not sure you can do this with straight SQL. In cases like this you're often better off creating a Temporary table, and filling that table with the data, then using the Temp table as needed.

    Create a table named Table3, with 4 fields: ID, Condition1, Condition2 and Condition3.

    Now populate that table:

    Private Sub cmFillTemp_Click()
        CurrentDb.Execute "DELETE FROM Table3"
        Dim rst    As DAO.Recordset
        Set rst = CurrentDb.OpenRecordset("SELECT DISTINCT ID FROM Table1 UNION SELECT DISTINCT ID FROM Table2")
        Do Until rst.EOF
            CurrentDb.Execute "INSERT INTO Table3(ID) VALUES(" & rst("ID") & ")"
        Set rst = CurrentDb.OpenRecordset("SELECT * FROM Table3")
        Do Until rst.EOF
            Dim s1 As String
            Dim s2 As String
            Dim s3 As String
            Dim rst2_1 As DAO.Recordset
            Dim rst2_2 As DAO.Recordset
            Set rst2_1 = CurrentDb.OpenRecordset("SELECT * FROM Table1 WHERE ID=" & rst("ID"))
            Set rst2_2 = CurrentDb.OpenRecordset("SELECT * FROM Table2 WHERE ID=" & rst("ID"))
            If rst2_1("Question1") = "1" Then
                s3 = "Delete--"
            End If
            If rst2_1("Question1") = "2" And rst2_1("Question4") = "4" And rst2_2("Question6") = "2" Then
                s1 = "Delete--"
            End If
            If rst2_1("Question4") = "2" Or rst2_2("Question5") = "1" Or rst2_2("Question8") = "2" Then
                s2 = "Delete--"
            End If
            rst("Condition1") = s1
            rst("Condition2") = s2
            rst("Condition3") = s3
            s1 = ""
            s2 = ""
            s3 = ""
    End Sub

    Open in new window

    Note I used "ID" as the name of the "InterviewID" field. You'd need to change that to match your own data structure.

    Author Comment

    Hi Scott:

    It doesn't seem to be working on my end.  I've attached my database.  If you can take a look at it and let me know where I'm going wrong... it would be a big help.

    Keep in mind that, unfortunately, you will not be able to send the database back to me because I can't see Access attachments on this website (I have the tech support team looking into it).

    LVL 84

    Accepted Solution

    You haven't "wired up" the command button on Form1.

    To do that, open the form in Design view, and open the Property sheet - Event tab. In the "On Click" event, select "[Event Procedure]" and then click the build button to the right of that box. You'll be taken to the VBA Editor. From there, move the code from the "cmFill_Temp_Click" event to the new event procedure for your button.

    It looks like your tables are setup exactly as the ones in my test database, so you shouldn't have to change anything. However, if the name of your ID field changes to InterviewID (as it is in your examples), you'd have to make modifications to the code to reflect that change.

    See this image:VBA Editor

    Author Comment

    You are the man!  That worked.  Thanks so much for your help.

    Author Comment

    I've requested that this question be closed as follows:

    Accepted answer: 0 points for mdstalla's comment #a40259638

    for the following reason:

    Expert solved the problem
    LVL 84
    Glad I was able to help.

    I think you meant to award points to me, but instead awarded them to yourself. The new EE interface is somewhat confusing in this regard, so I've objected to let you try the closure again.

    Here's a Help Article that may be useful:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
    I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    8 Experts available now in Live!

    Get 1:1 Help Now