[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

MS Access Auto-populate Table

Posted on 2014-08-12
6
Medium Priority
?
360 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
0
Comment
Question by:mdstalla
  • 3
  • 3
6 Comments
 
LVL 85
ID: 40257216
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
 

Author Comment

by:mdstalla
ID: 40257336
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
 
LVL 85

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 total points
ID: 40257926
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
Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

 

Author Comment

by:mdstalla
ID: 40259638
You are the man!  That worked.  Thanks so much for your help.
0
 

Author Comment

by:mdstalla
ID: 40260187
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
 
LVL 85
ID: 40260188
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

Featured Post

New feature and membership benefit!

New feature! Upgrade and increase expert visibility of your issues with Priority Questions.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses

834 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