MS Access Auto-populate Table

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
mdstallaAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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.
0
mdstallaAuthor 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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mdstallaAuthor Commented:
You are the man!  That worked.  Thanks so much for your help.
0
mdstallaAuthor 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
0
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.