Finding name from num

[bluedollar]

----------------------------------------------------------------------
I have the following tables

SITE_COMM

site_comm_num
quote_num
date_done
done_staff_num (linked to STAFF on staff_num)
type_num
completed_date
completed_staff_num (linked to STAFF on staff_num)
client
supplier_num

STAFF

staff_num
name

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

What I am trying to do?

I am trying to list all site_comm records that have the same quote num (this I can do fine). However I am also trying to list the name of the staff member as apposed to their number. eg.

staff.name instead of site_comm.done_staff_num
staff.name instead of site_comm.completed_staff_num

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

Problem

To do this I am using the staff table name field with the following criteria:

[site_comm]![done_staff_num] = [staff]![staff_num]

This works fine for one name, however I need to find the names for both done_staff_num and completed_staff_num, when I use the following:

[site_comm]![done_staff_num] = [staff]![staff_num]
[site_comm]![completed_staff_num] = [staff]![staff_num]

The query only lists site_comm records where both done_staff_num and completed_staff_num have the same staff number. However a lot of records in the table have different staff numbers for the done and completed_staff_num fields.

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

Any help would be greatly appreciated.

Thanks

Dan

 

[KenReay]

Hi

You need to make a query joining the SITE_COM table with teh staff table, in fact you need to make two joins, one for done_staff_num and one for completed_staff_num.

To do this proceed as follows:

In the query page of the datbase window, choose New Query

Add the SITE_COM Table to the query grid, by choosing it from the list of tables shown in the add tables dialog list, then add the STAFF in the same way, then add the Staff table again (Access will 'alias' it as Staff_1).

Now with the mouse click on done_Staff_num and drag to staff_num of the table Staff

Now with the mouse click on completed_staff_num and drag to the Staff_num of the table staff_1.

Delete any other join lines (there may well be no other lines).

Now right click in turn on each of your two join lines and from the three options shown in the dialog, choose "show all rows from table Site_Com".

Now drag into the query grid the columns you wish to have displayed in your query.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK