Access 2013 Populating a field

In my database I have four tables, table1, table2, table3, table4
In table1 three of the fields needs data from tables 2 - 4. Now, for example, in table1 data is needed from any of the other tables and the data has not been entered in a supporting table I would like to add data to the table missing the data so I can have it in table1.
Frank FreeseAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

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.

Jeffrey CoachmanMIS LiasonCommented:
Are you sure you have designed this system correctly?

In a true relational database, you create relationships between the tables.
In this way you can create a query that contains data form both tables.

In other words, there should be no real need to "Populate" or "add" data from one table to another...

Can you take a step back and provide an overview of your database here?
Frank FreeseAuthor Commented:
Here's the current Relationship diagram:
SQL DesignSome SQL statements can have multiple purposes, clauses and syntaxes
I'm not saying your wrong and indeed this design may be incorrect.
Jeffrey CoachmanMIS LiasonCommented:
Some SQL statements can have multiple purposes
By your design, you mean this to say:
Some SQL *Keywords* can have multiple purposes?

Yes, ...In this case tblPurpose could have multiple entries (Records) for each Keyword.
So this might be a better staring point:

kwID (Primary Key)

pID (Primary Key)
p_kwID (Foreign Key)

Data, Main/sub form, and relationships, might look like this:
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jeffrey CoachmanMIS LiasonCommented:
Now you could make a query like this:
SELECT tblKeywords.kwID, tblKeywords.Keyword, tblPurpose.pID, tblPurpose.pPurpose
FROM tblKeywords INNER JOIN tblPurpose ON tblKeywords.kwID = tblPurpose.p_kwID;

To display this:
Not here that there is no need to "populate" the Keywords table with the data form the Purpose table...


Jeffrey CoachmanMIS LiasonCommented:
Well, you might as well just have the sample DB I made..., I have attached it...

You could probably apply the same design principle to the other child tables...
(and note that my sample was quick and dirty, would probably want to make autonumber fields for your tables primary keys, ...and otherwise tidy up the basic design)

But to be on the safe  side, ...lets see what other experts might post...

Frank FreeseAuthor Commented:
Thanks Jeff
I don't think we're on the same page yet though. It's been a while since I worked with Access but I do recall that in using a form and there was not a value you were looking for in another table via a combo box one could automatically add the value to the lookup table without closing your form, going to the other table, adding the missing data then returning to the original form to add the data missing.
Jeffrey CoachmanMIS LiasonCommented:
Not sure I understand either...

Can you express this in terms of an actual user scenario...
Frank FreeseAuthor Commented:
I'll try
I have a form frmSQLKeyword that is used to capture information into tblSQLKeyword. All  the information (except Keyword) will come from the three supporting tables, tblSyntax, tblPurpose and tblClause. If, for example, I am entering in a new record into tblSQLKeyword. I come to find out that I need a new Clause because the Clause I need has not been entered into tblClause. I'd like to enter the new Clause (frmClause) and return back to my form and refresh the combo box on my frmSQLKeyword Hope this helps.
Jeffrey CoachmanMIS LiasonCommented:
Then here is the flow...
(I am not sure on what you are considering a "clause", so I will refer to Keywords and Purposes)
The relationship between Keyword and Purpose is One-To-Many (One keyword can have many Purposes)
You create a Main form of KeyWords (and also a subform of Purposes)
On this main form you have a combobox that lists the keywords
The combobox allows you to select a keyword from the combobox and display the info (and the child data) on the form.
On this main form you click on the "New Record" button and enter a new keyword.
When you click in the subform (to enter a Purpose for this new Keyword), ...the AfterInsert event of the main form triggers, and you requery the combobx:
The combobox is refreshed and the new Keyword appears in the combobox immediately
(You would also add the requery code to the After Delete event of the main form to avoid "Deleted" errors)
...Finally note that you should enforce referential Integrity when you create your relationships.

Hope this helps.
New sample is attached.

Frank FreeseAuthor Commented:
We're almost there. Now if the information I need is not in the "child" table how can I add the new data to the "child" from the "parent?
Jeffrey CoachmanMIS LiasonCommented:
Still confused...
You only add the Linking field value from the parent table to the child table, ...that is what the subform does...

In the one to many relationship data only flows from the parent to the child (via the linking field), ...not the other way around.

If we again, reference a standard One to Many relationship of Customers and Orders...
The CustID from the Customer table is inserted into the child table.
There are no references whatsoever to the child table in the Parent table.
For example: there is no reference to the OrderDate (child table) in the Customer table
...because we would not know what order the order date references...

When you say:
if the information I need is not in the "child" table how can I add the new data to the "child" from the "parent?
...You need to state this in terms of an actual user Scenario, ...using your actual object names...

Frank FreeseAuthor Commented:
In my form frmSQLKeyword I have four fields, SQLKeyword, Clauses, Syntax, and Purpose. The fields Clauses, Syntax and Purpose provide data to the table tblSQLKeyword in that I am using combo boxes.
Now lets say as I am entering a new record and when I come to Purpose, the Purpose I need has not been entered. I don't want to close the form and go to the Purpose table to enter a new Purpose then return to the form frmSQLKeyword in order to select the new Purpose for that will apply to that Keyword.
I hope this helps
Jeffrey CoachmanMIS LiasonCommented:
Then you could use the NotInList property of the combobox to enter new Purposes.

code similar to this:
Private Sub cboPurpose_NotInList(NewData As String, Response As Integer)
    If MsgBox("The Purpose '" & NewData & "' is not in the list." & vbLf & "Do you wish to add it?", vbYesNo) = vbYes Then
        CurrentDb().Execute "insert into tblPurpose (pName) values ('" & NewData & "')"
        Response = acDataErrAdded
        Response = acDataErrContinue
        Me.cboPurpose = ""
    End If
End Sub

Open in new window

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
Frank FreeseAuthor Commented:
Thank you for staying with me on this. Great job!
Jeffrey CoachmanMIS LiasonCommented:
Nothing that you asked for was particularly "Complicated"

I knew it was just a matter us "understanding" each other...

Glad I could help.

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.