Select Query 1

[checkOut]

Hi to all,

I have problems with the following query:

SELECT DISTINCT PROJECT.leadnummer, IIf([offstatusRef] In (2,3,5),"B","A") AS LeadStatus
FROM PROJECT LEFT JOIN OFFERTE ON PROJECT.leadnummer = OFFERTE.projectnaam
WHERE (((PROJECT.blnDelete)=False));

This give a output of 2 columns; 1 with leadnummer and 1 with A or B. The following can happen
103 A
103 B
104 A
105 B
When A and B are to the same leadnummer I only want B to see (B rules over A), is that possible?

Thanx in advance,

Gerard

 

[PHV]

SELECT PROJECT.leadnummer, Max(IIf([offstatusRef] In (2,3,5),"B","A") AS LeadStatus
FROM PROJECT LEFT JOIN OFFERTE ON PROJECT.leadnummer = OFFERTE.projectnaam
WHERE (((PROJECT.blnDelete)=False))
GROUP BY PROJECT.leadnummer;

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

 

[checkOut]

Thanx PH,

exactly what I need.

Greetz,

Gerard