MS Access Auto-populate Table

mdstalla
mdstalla used Ask the Experts™
on
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 = ""
Spreadsheet.xls
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
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") & ")"
        rst.MoveNext
    Loop

    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.Edit
        rst("Condition1") = s1
        rst("Condition2") = s2
        rst("Condition3") = s3
        rst.Update

        s1 = ""
        s2 = ""
        s3 = ""
        
        rst.MoveNext
    Loop
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

Commented:
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).

Thanks.
Database381.accdb
Most Valuable Expert 2012
Top Expert 2014
Commented:
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
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

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

Author

Commented:
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
Most Valuable Expert 2012
Top Expert 2014

Commented:
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: http://support.experts-exchange.com/customer/portal/articles/608621-how-do-i-accept-a-comment-as-my-solution-?b_id=44

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial