Min & Max functions gives Nulls when they shouldn't

[PaulCoop]

I am using the following SQL Query:

INSERT INTO xx_catchment ( PC1, PC2, PC3, PC4, street, [first odd], [last odd], [first even], [last even] )
SELECT [Catchment List].PCA, [Catchment List].PCB, [Catchment List].PCC, [Catchment List].PCD, customer_potential.add1,

Min(IIf([customer_potential]![house_number] Mod 2=1,Val([customer_potential]![house_number]),Null)) AS Odds1,
Max(IIf([customer_potential]![house_number] Mod 2=1,Val([customer_potential]![house_number]),Null)) AS Odds2,
Min(IIf([customer_potential]![house_number] Mod 2=0,Val([customer_potential]![house_number]),Null)) AS Evens1,
Max(IIf([customer_potential]![house_number] Mod 2=0,Val([customer_potential]![house_number]),Null)) AS Evens2

FROM [Catchment List]
INNER JOIN customer_potential ON ([Catchment List].PCA = customer_potential.pc1) AND ([Catchment List].PCB = customer_potential.pc2) AND ([Catchment List].PCC = customer_potential.pc3) AND ([Catchment List].PCD = customer_potential.pc4)
WHERE ((([Catchment List].id)=[dummy]) AND ((IIf(IsNumeric([customer_potential]![house_number]),1,0))=1))
GROUP BY [Catchment List].PCA, [Catchment List].PCB, [Catchment List].PCC, [Catchment List].PCD, customer_potential.add1;

***** Please Note: [dummy] is a dummy variable as this is replaced in the SQL string in code by the correct value before the RunSQL command is called. *****



This takes a set of records of households which are grouped by postcode and street and finds the first odd house number, the last odd, the first even and the last even.

The problem arises is that I can often get a single even or odd house number. Examples:

example_num first_odd last_odd first_even last_even
1 1 17 2 20
2 1 1 Null Null
3 Null Null 14 38
4 3 9 4 Null
5 19 Null 16 26

Examples 1,2 and 3 are the sort of results I would expect and most of the time get. Unfortunately some records are like those shown in examples 4 and 5 which do not make any sense because if you have a first odd you must at least have a last odd which is the same number (likewise goes for evens).

Can someone explain why I get records such as 4 and 5, and how can I change my SQl statement to correct for the problem?

Paul Cooper

 

[lameid]

Can't explain it. But I have an idea. Try creating a query that just has even and odd values created by IIF statements. Use your Min and Max functions in a subsequent query based on the first. I suspect this will work. I don't think the query optimizer always makes sense of expressions when grouping is involved.