[thelist] SQL Assistance

Michele Foster michele at wusc.ca
Tue Apr 1 10:52:05 CST 2003


I've dummed down this query greatly in order to try to find a solution to the problem. The real query is far more complex .. but for simplicity this shows the problem we are encountering.  This is from the sql query builder in Access 2K.

SELECT 
	Contacts.ContactID, 
	Module.ModuleContactID, 
	Module.ModuleID, 
	Module.Module, 
	Address.AddressType
FROM 
	(Contacts LEFT JOIN Address ON Contacts.ContactID = Address.AddressContactID) 
	INNER JOIN [Module] ON Contacts.ContactID = Module.ModuleContactID
	WHERE (((Module.Module)="CO" Or (Module.Module)="COWEB"));

The problem is that each contact can have multiple addresses.  For reporting purposes I only want 1 address.  (There is another column available that will allow me to sort and determine which address I want .. I left that column out for now.)  I've tried Top, Limit, Distinct and DistinctRow, but either I've been putting them in the wrong place or they are just the incorrect options. 

I want ALL Contacts where Module = CO or COWEB, but ONLY one address (if one exists, if one doest not exist, I still want the contact).

I'm at a loss what to try next. 

TIA,

Michele


Please cc me directly (michele at wusc.ca) on all replies .. as thelist has been taking anywhere from 1 to 4 hours to receive any messages (last week anyway :).



More information about the thelist mailing list