Trying to get my name and contact info from one table and location from another

[CDub]

Sorry for the noob level of this question. I spent a lot of time in Filemaker back in the day, but am having to design a new database in Access. I have been watching videos, reading, and researching for the past several days and just cannot seem to figure out how to do what I need to do. I feel like I am on the cusp, but am banging my head against a wall.

Here is the scenario:



Table 1 - Contact information, all the usual suspects
Table 2 - Location information
Table 3 - Junction table to create a many-to-many relationship between T1 and T2

I have set up the relationship correctly in T3 using a composite key consisting of ContactID from T1 (linked to same in T1) and LocationID from T2 (linked to same in T2). I need to use many-to-many because there are situations where one contact will be associated with multiple locations and others where there will be multiple contacts associated with a single location.

I want to be able to attach a ContactID(s) to a LocationID and display the contact information from T1 along with the location information from T2. I cannot for the life of me figure out how to do this.

Thanks in advance.

[June7]

Build a query that includes all three tables.

[CDub]

Build a query that includes all three tables.

Thanks for the response. Is there any way that you can be more specific? What would I include in the query from each table?

T1 includes:
contactID
name
company
phone
email

T2 includes:
locationID
address
contact name
company
phone
email

T3 includes:
contactID
locationID


Up until this point, I have been using two excel spreadsheets for all this data, one for the contact information and one for the location information. The location spreadsheet also included the contact information. The whole point of going to Access was to create a better interface for inputting data on a daily basis, as well as preventing having to input the contact info on each location record when the contact information has already bee input into the contact spreadsheet. I am fine going back through the data and matching the contactID and locationID together, but I am unsure as to how to set up the query so that it works properly. I would like to be able to have the contact information auto-populate the location form when I input the contactID, which in Filemaker was a piece of cake, but in Access seems like some kind of heavy programming situation.

Thanks for your help. Any additional would be appreciated. C

[June7]

A report could include all 3 tables and all fields.

Options for data entry/edit form:

1. single form bound to T3 with comboboxes for selecting contact and location - the form RecordSource can include all three tables but do not use INNER JOIN and don't allow edit to fields of T1 and T2

2. main form bound to T1 and subform bound to T3 with combobox to select location - the subform RecordSource can include T3 and T2 but don't use INNER JOIN and don't allow edit of T2 fields

3. main form bound to T2 and subform bound to T3 with combobox to select contact - the subform RecordSource can include T3 and T1 but don't use INNER JOIN and don't allow edit of T1 fields

[CDub]

I will get my search on and see if I can figure out comboboxes, inner join and limiting editing per field. Thanks for your help. If I can have this figured out before Monday, I will feel like a rockstar.

[June7]

Comboboxes:
http://www.datapigtechnologies.com/f...combobox1.html
http://www.datapigtechnologies.com/f...combobox3.html
http://www.datapigtechnologies.com/f...combobox2.html

JOIN: In query designer, click on the link line between tables, change the join type

EDITING: On form, set properties of textboxes bound to lookup table fields as Locked Yes and TabStop No

[CDub]

I'm on it. June7, you are a boss. Thanks again. I will advise how it all works out. I should have found this place sooner.

[CDub]

This is the dialog I am getting for the query links. I don't see any option for inner join.



Any thoughts?

[June7]

INNER JOIN is the Option 1. The other options will be LEFT or RIGHT outer join.

You can observe this by selecting option then go to SQL View of query builder and look at the SQL statement.