Noob question:Inserting data into tables that have a relationship

[jediderek]

I'm confused on how to go about this situation. Lets say I have 2 tables that have a realationship with eachother:

table 1

ID	Company
1	apple
2	verizon

table 2

ID	Company_ID	customer
1	2	john

And also a form
1. CompanyName:_________


2.CustomerName:_________

<button>

The question is when I enter the form, I want it to insert the data into table 2. But it has to somehow check table 1 if that company is in that table, if not then it adds that new company with an ID. Then it grabs the ID from table 1 and put it in the field "company_ID". I assume this must be a common thing to do if you have good database design. I'm new to access so I can't find the simple solution(if there is one) other than creating lots of vba code with loops and if statments. Any help in pointing me to the right direction or an example would be very appreciated. thanks!!

[orange]

Write a description 4-5 lines in plain English (as you would tell an 8 yr old) of the "business involved". No jargon and no database quasi-terms. Bottom line -- get a clear statement of WHAT you are trying to automate. Once that is clear, then consider the options that may exist for HOW to do that automation using Access.
Don't be too quick to jump into physical Access (or any other DBMS).
Good luck.

[jediderek]

I just did it in VBA my own way and it works fine. I was just hoping if access did it a better way or maybe cleaner code. Here is my solution for those wondering how to do the same:

dim rs as recordset
dim idHolder as integer
dim strSQL as String
dim strSQL2 as String
Set rs = CurrentDb.OpenRecordset("table1", dbOpenSnapshot)
rs.FindFirst "Company='" & Me.txtCompanyName & "'"

if rs.NoMatch Then
strSQL2 = "INSERT INTO table1(CompanyName) VALUES('" & txtCompanyName & "');"
DoCmd.RunSQL strSQL2

Me.Requery
idHolder = DMax("ID", "table1")

Else
idHolder = rs!ID
End If

strSQL = "INSERT INTO table2(CompanyID,Customer) VALUES ('" & idHolder & "', '" & txtCustomerName & "');"
DoCmd.RunSQL strSQL
Me.Requery

[Bulzie]

Usually you have a mainform Company and a subform Customer, and put ID (or the field that links the 2) in the LinkMaster/LinkChild property on the subform. So when you add data to the subform, it automatically puts that ID in. Other way is to have Form for Customer table and have a combobox for Company where they select that ID to go on the Customer record.

[jediderek]

Usually you have a mainform Company and a subform Customer, and put ID (or the field that links the 2) in the LinkMaster/LinkChild property on the subform. So when you add data to the subform, it automatically puts that ID in. Other way is to have Form for Customer table and have a combobox for Company where they select that ID to go on the Customer record.

Do you mind going in more detail with this? I think I'm following you but an example or more explaining will help me understand better. thanks!

[Bulzie]

You would have something like this:
tblCompany:
CompanyID(Autonumber)
CompanyName

tblCustomer:
CustomerID(Autonumber)
CompanyID(numeric, Long Integer) This links to the CompanyID above in a 1 to many (1 company to many customers)
CustomerName

So you create a Mainform using tblCompany and displays company name. Then create another form for tblCustomer and put that as a subform into the Mainform and in the LinkMaster and Linkchild enter CompanyID(that is what links the 2). So you select a Company and it will show all the Customers for that company in the subform. If you have a new customer, you just add that into the subform and it will add the CompanyID for you.

[orange]

Rarely do tables with names like table1 and table2 have any resemblance to a business situation.
It appears you ae dealing with 1 or more Companies, and each of those Companies may have 0,1 or more Customers.
It will help you and readers if you talk about what you are trying to do in simple business terms.
Relationship has a specific meaning in relational database - usually based on a business rule/fact.

Good luck with your project.

[jediderek]

You would have something like this:
tblCompany:
CompanyID(Autonumber)
CompanyName

tblCustomer:
CustomerID(Autonumber)
CompanyID(numeric, Long Integer) This links to the CompanyID above in a 1 to many (1 company to many customers)
CustomerName

So you create a Mainform using tblCompany and displays company name. Then create another form for tblCustomer and put that as a subform into the Mainform and in the LinkMaster and Linkchild enter CompanyID(that is what links the 2). So you select a Company and it will show all the Customers for that company in the subform. If you have a new customer, you just add that into the subform and it will add the CompanyID for you.

The part I was confused on was the fact that I didn't know what subforms were or those property names like LinkMaster, thank you for taking the time to explain it for me.

Rarely do tables with names like table1 and table2 have any resemblance to a business situation.
It appears you ae dealing with 1 or more Companies, and each of those Companies may have 0,1 or more Customers.
It will help you and readers if you talk about what you are trying to do in simple business terms.
Relationship has a specific meaning in relational database - usually based on a business rule/fact.

Good luck with your project.

I was able to fix the problem, I just needed to understand queries better and different ways to implement my forms, thanks