2 fields with same data problem

[mazon]

i am creating a dba that tracks phone inquiries. i want to
track what department the phone call came to and what
department it was referred to.

i have 3 tables
tbl1 is the caller information (Original department call
came to, call date, name, address, phone #)

tbl2 has caller's question, person referred to and
department referred to and follow-up date

tbl3 is the names of departments

the relationship is a one to many for tbl1 & tbl2
(inquiryID)

tbl3 has a one to many relationship with tbl1 and tbl2

i used the look-up wizard for departmentnames in tbl1
&tbl2 and it stores the DeptID, which is fine. I created
a query to show the inquiry information, and it shows the
department #'s when i add tbl3, to show the actual names,
i get no data, when i know there is some. Can i have 2
fields pulling from the same table and showing the name
not the ID

 

[Ted]

You can do this. I would guess that the problem is that
Access needs to know that the two department's can be
different. If your join just has both tables with the
department linked to the department table, Access may be
expecting that the two departments have to be the same.

To change this, in your relationships window, and in your
query builder, you can insert two instances of the
Department lookup table. Access will add a suffix after
the name of the table inserted the second time (I believe
it will add "_1"). Link one instance to table 1 to refer
to the called department, and link the second instance to
table 2 to represent the referred department. Then,
whenever you want to refer to either of the departments,
you can differentiate by using the applicable table name
(the one with or without the suffix) followed by the
field name. This tells Access that the table is being
used as a lookup for both tables, but that the values in
each table can be different from one another.

Hopefully this is clear, if not post back and I'll see if
I can clarify better.

-Ted

 

[mazon]

this makes sense, but being a novice, how do i do this.
if i had the tlkpdepartment table to the relationship
window 2, it will automatically do this?? If not can you
let me know how to do it. Thanks for your help

 

[Tim Ferguson]

Can i have 2
fields pulling from the same table and showing the name
not the ID

Yes: just use the Show Table dialog twice. The second instance will be
given an alias name (like Departments_2) but Access will take care of that
for you. The diagram will look a bit like this:

Calls
=====
CallNumber
etc
RefFromDeptID >------- Department
etc
ReferToDeptID >------- Department_2
etc


In the query grid, you can drag the Departments' names to new columns, but
you will want to give them sensible names like this:

ReferredFrom: Department.FullName | ReferredTo: Department_2.FullName


Hope that helps


Tim F

 

[Ted]

Yes, just add the table a second time the same way that
you added it the first time, and in the same window.
MSAccess will automatically take care of giving it the
alias as Tim mentioned.

 

[mazon]

thank you so much, IT WORKS!!!!!!!!

-----Original Message-----
Yes, just add the table a second time the same way that
you added it the first time, and in the same window.
MSAccess will automatically take care of giving it the
alias as Tim mentioned.

.