We help IT Professionals succeed at work.

MS Access Auto-populate Table

mdstalla
mdstalla asked
on
442 Views
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 = ""
Spreadsheet.xls
Comment
Watch Question

Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
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
Infotrakker Software
CERTIFIED EXPERT
Most Valuable Expert 2012
Top Expert 2014
Commented:
This problem has been solved!
(Unlock this solution with a 7-day Free Trial)
UNLOCK SOLUTION

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
Scott McDaniel (EE MVE )Infotrakker Software
CERTIFIED EXPERT
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