Joining data

[ttcorp]

Ok...

This is the table I need to create:

Company Representative Alternate
CompanyID FirstName LastName FirstName LastName

The data for this table is from two tables:
tblINDIVIDUAL and tblCorporate

The "CompanyName" field is from the tblCorporate

Tbe following field names are from tblINDIVIDUAL:

FirstName
LastName
NHA-Code

In the NHA-Code field there are several codes, the two codes I need is the "Rep" and "Alt" codes.

How do I set up a table to take those two codes from the "NHA-Code" field and make them column headings and show the company's Rep and Alt on one line? And, how do I show the FirstName and LastName in one cell?

I hope that made sense. Thanks in advance for any help you can give me!!!

 

[PHV]

How are tblINDIVIDUAL and tblCorporate related ?
I guess by a CompanyID field:
SELECT C.CompanyID, C.CompanyName, R.FirstName & ' ' & R.LastName AS Representative, A.FirstName & ' ' & A.LastName AS Alternate
FROM (tblCorporate AS C
LEFT JOIN tblINDIVIDUAL AS R ON C.CompanyID = R.CompanyID)
LEFT JOIN tblINDIVIDUAL AS A ON C.CompanyID = A.CompanyID
WHERE Nz(R.[NHA-Code],' ')='Rep' AND Nz(A.[NHA-Code],' ')='Alt';

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886