Joining a table with a query that have common values

[luckydougy]

I have a database with a table being a list of multiple employers and corresponding contract start dates. Ex. company1, 1/1/2012 company2, 3/1/2012, etc.
Then I have a query of employees from multiple employers.
Ex. John smith, company1, bill miller, company2, etc.
I want to a query that gives me the employees name, the employer, and the contract start date combined. Ex. John smith, company1, 1/1/2012, bill miller, company2, 3/1/2012, etc.
I am new to access and am not sure how to do this.
Any help is appreciated!

 

[MarkK]

Hey there welcome to the forum. I recommend you read up on "database normalization." One record in a table should provide the definition for no more that one object, so to have fields like Company1 and Company2 in the same record is suspect. Also, having the names of two different people in the same record is a data structure you will find difficult to work with in a database.
HTH
Mark

 

[luckydougy]

They are not in the same record, they are in seperate records and that is just how I put them in my post. I am just wondering how to check to see if their is a value that is common in both tables.

 

[jdraw]

Tell us the structure of each table - the fields in each.

 

[luckydougy]

Table 1: Company(field 1), Employee Name(field 2)
Table 2: Company(field 1), Contract Start Date(field 2)

I want to creat a new table(table 3) that has the employees name(field 1) and the contract start date(field 2). The table is made by finding where the company name matches and then adding thier corresponding attributes(Name, Contract Start Date) to the new table.

 

[MarkK]

Looking at your data I expect to see three tables minimum, which are ...

tblCompany
CompanyID (Primary Key)
CompanyName

tblEmployee
EmployeeID (PK)
FirstName
LastName

tblContract
ContractID (PK)
CompanyID (Foreign Key)
StartDate

One record in a table represents one discrete thing. Each record should contain a unique Long Integer ID field, and it's handy in Access to use an AutoNumber field for this purpose.

Is that making sense to you?
Mark

 

[luckydougy]

No, right now I have 2 tables. And no I do not have PKs because I do not need them.

Table 1
Column 1 Title - Company Column 2 Title - Employee Last Name
Column 1 Sample Data: Walmart Column 2 Sample Data: Smith

Table 2
Column 1 Title - Company Column 2 Title - Contract Start Date
Column 1 Sample Data: Walmart Column 2 Sample Data: 1/1/2011

I want to make this:

Table 3
Column 1 Title - Employee Last Name Column 2 Title - Contract Start Date
Column 1 Sample Data: Smith Column 2 Sample Data: 1/1/2011

This third table needs to be made because the Company(Walmart) is in common in both tables. I think I need to run a join somhow but am not sure on quite how to do it.

Thanks Again

 

[MarkK]

You do joins using keys. But you say you don't need keys. Best of luck,

 

[luckydougy]

you cant join them based on a common value of a column? walmart matches with walmart so now take the attributes that were associated with walmart in the previous 2 tables and then join those attributes into a new table. thought it would be simple.