Pathetic... I cannot figure out a seemingly simply query

[mjseim]

Alright people... I'm ashamed to even ask but here goes.

I have two tables:
1) Employees
2) Neighborhoods

Table Employees has a comprehensive list of all employees and their information (including phone numbers).

Table Neighborhoods has lots of information about each neighborhood we're working in and it also specifies what people play certain roles in each neighborhood. For instance, there is a field for "Field Manager" and "Sales Consultant". These two fields are exact matches of "Full Name" from Table Employees.

All I want is for my Neighborhoods Table to be updated or a new table be created that imports the "Field Manger Phone Number" into the Neighborhoods Table and the same for the "Sales Consultant".

-----------------------------

To be explain one other way, this is what I want:

Table: Employees
Field: Full Name
Field: Phone Number

Table Neighborhoods
Field: Field Manager (already equals Full Name)
Field: Sales Consultant (already equals Full Name)
Field: Field Manager Phone Number (This is the field I want pulled from Table Employees)
Field: Sales Consultant Phone Number (This is the field I want pulled from Table Employees)

-------------------------------

I've tried my best to create an Update Query or a Make Table query but I have not gotten any good results. Can someone please help me out with this being very descriptive as I am certain I'm just goofing one simple step.

Thanks in advance.

 

[pbaldy]

Actually, you should not store the numbers in the neighborhood table, since they're already stored in the employee table. It's simple to join the 2 tables together in a query and get the number for the employee when you need it. If you try to store it again, you have maintenance problems (you're seeing the first problem, the second would occur when someone changed their number).

 

[mjseim]

Ok, I can buy that.

But, I want to report the employees numbers in my Neighborhood report. How then would I do this?

 

[mjseim]

I think I may have figured this out... I just use subreports. However, something seems to be wrong as I will have to create about a dozen subreports (I only mentioned Field Manager and Sales Consultant but there are plenty more).

Is that ok that I have so many subreports? Or am I approaching this all wrong?

 

[pbaldy]

Like I said, you would join the 2 tables together in the source query of your report. I doubt you need subreports. Here's an example of a query, with what might be your names:

SELECT Neighborhoods.[Field Manager], Neighborhoods.Whatever, Employees.[Phone Number]
FROM Neighborhoods INNER JOIN Employees ON Neighborhoods.[Field Manager] = Employees.[Full Name]

If there are multiple fields in this table for different employees, it's possible there's a design problem. Are they ALL ALWAYS filled out? If not, and even possibly if so, there should be a separate table for the employees assigned to neighborhoods. There would be fields for neighborhood (your key field from that table), employee (your key field from the employee table) and probably task (sales consultant, field manager, etc). With this design, there would be a subreport, but it would be more correct design IMHO.