Hello. I have a MS Access Database with two tables, and, I need to
know if I need to set up a relationship to get the kind of results I
want from a query, or if I just need to phrase my query correctly.

I have a Contacts table, "Con", and a Companies table, "Com". The
Primary Key for the Contacts table "Con.ContactID" is the way of
identifying each contact (There is also a name field
"Con.ContactName").

Each Company has a current contact "Com.Curr" and a previous contact
"Com.Prev" that correspond to the Contact ID number from the Contacts
table.

Basically, one person can be a contact to multiple companies.
Similarly, one person can be a previous contact to multiple companies.
So, what I need is a query that will show me both the current and the
previous Names, not the ID Number for each company.

When I do a simple query, I can get this:
(fields: Com.CompanyName, Com.Curr, Com.Prev)
ABC Company    2    1
DEF Company    3    1

When I do a Join query, I can get one of the names, but not the other
(fields: Com.CompanyName, Com.Curr(joined with Con.ContactID), Com.Prev)
ABC Company   John Smith   1
DEF Company   Jane Smith   1

What I want, is a query that will get me the names of both
(fields: Com.CompanyName, Com.Curr(joined with Con.ContactID),
Com.Prev(joined with Con.ContactID))
ABC Company   John Smith   -Unassigned-
DEF Company   Jane Smith   -Unassigned-

Is it necessary to set up a relationship for this to work? Or, do I
just need a well written SQL query? I am not at my laptop so I don't
have the exact SQL query written for the top 2 examples.

---

Clarification of Question by jrwelch-ga on 05 Jun 2006 08:34 PDT

I realized I didn't phrase part of this well. 

On example 2 and 3 I have the Value for Com.Curr and Com.Prev as the
Con.ContactID. What I want to display in the query is the
Con.ContactName. Hopefully that's a bit more precise.

In fact I have a deceptively simple solution to your problem. This
Out-of-the-box solution uses some design time changes with some simple
SQL without any join stuff. Just use the lookup feature of MS-Access &
get the kind of results that you are looking for by using the
following method.

1. Open Companies table, "Com" in design view.
2. The following shows the property-value pair to be set in the Lookup
tab of the Curr Field properties

   Display Control  Combo Box
   Row Source Type  Table/Query
   Row Source       SELECT Con.ContactID, Con.ContactName FROM Con;
   Bound Column     1
   Column Count     2
   Column Widths    0";10"

3. Similarly the following shows the property-value pair to be set in
the Lookup tab of the Prev Field properties. Incidentally it's exactly
same as the previous one.

   Display Control  Combo Box
   Row Source Type  Table/Query
   Row Source       SELECT Con.ContactID, Con.ContactName FROM Con;
   Bound Column     1
   Column Count     2
   Column Widths    0";10"

4. Save the changes.
5. Open the Com table in Datasheet view & viola! You have what you are looking for.

Hope that is what you are lookin for.

Thanks.

> What I want, is a query that will get me the names of both
> (fields: Com.CompanyName, Com.Curr(joined with Con.ContactID),
> Com.Prev(joined with Con.ContactID))
> ABC Company   John Smith   -Unassigned-
> DEF Company   Jane Smith   -Unassigned-

You need a SQL statement like this:

SELECT     Com.CompanyName AS CompanyName, Con1.ContactName AS CurrentContact, 
           Con2.ContactName AS PreviousContact
FROM         Com LEFT OUTER JOIN
             Con Con1 ON dbo.Com.Curr = Con1.ContactId LEFT OUTER JOIN
             Con Con2 ON dbo.Com.Prev = Con2.ContactId

Hope it helps
Nejla

I had a little mistake in my previous comment. please consider this one.

> What I want, is a query that will get me the names of both
> (fields: Com.CompanyName, Com.Curr(joined with Con.ContactID),
> Com.Prev(joined with Con.ContactID))
> ABC Company   John Smith   -Unassigned-
> DEF Company   Jane Smith   -Unassigned-

You need a SQL statement like this:

SELECT     Com.CompanyName AS CompanyName, Con1.ContactName AS CurrentContact, 
           Con2.ContactName AS PreviousContact
FROM         Com LEFT OUTER JOIN
             Con Con1 ON Com.Curr = Con1.ContactId LEFT OUTER JOIN
             Con Con2 ON Com.Prev = Con2.ContactId

Hope it helps
Nejla

Here is a simple query:
SELECT comp.compid, comp.company, comp.currcon,
DLookUp("contact","cont","contid=" & [currcon]) AS currContact,
comp.Prevcon, DLookUp("contact","cont","contid=" & [prevcon]) AS
Prevcontact
FROM [comp];

That shows following results:
compid	company	currcon	currContact	Prevcon	Prevcontact
1	abc	1	john	5	david
2	def	3	abraham	2	bill
3	ghi	4	charles	1	john
4	jkl	3	abraham	4	charles
5	mno	4	charles	1	john
6	pqr	4	charles	2	bill
7	stu	2	bill	3	abraham
8	vwx	7	fred	4	charles
9	yzz	8	graham	5	david
where
*******************************
Comp: company table
having columns : Compid,Company,CurrCon,PrevCon 
compid	company	currcon	Prevcon
1	abc	1	5
2	def	3	2
3	ghi	4	1
4	jkl	3	4
5	mno	4	1
6	pqr	4	2
7	stu	2	3
8	vwx	7	4
9	yzz	8	5


**************************************
Cont: Contacts Table
having columns: ContID,Contact
contid	contact
1	john
2	bill
3	abraham
4	charles
5	david
6	emma
7	fred
8	graham