DMin, Dmax

[JimP]

How can I structure code or a query to find the minimum and maximum [Year]
and [MthNbr] for each [CustID], one record per [CustID], e.g.

Table1
[CustID] [Year] [MthNbr]
1 2004 7
1 2005 9
1 2006 3
2 2005 2
2 2006 8
2 2007 4

Query Results
[CustID] [MinYear] [MinMthNbrForMinYear] [MaxYear]
[MaxMthNbrForMaxYear]
1 2004 7
2006 3
2 2005 2
2007 4

 

[Dale Fye]

Jim,

How about starting with something like:

SELECT CustID, Min(Dateserial([Year], [MthNbr], 1), Max(DateSerial([Year],
[MthNbr] + 1, 0)
FROM yourTable
GROUP BY CustID

Then take that and parse out the year and month
SELECT CutsID,
Year(Min(Dateserial([Year], [MthNbr], 1)) as MinYr,
Month(Min(Dateserial([Year], [MthNbr], 1)) as MinMthNbrForMinYr,
Year(Max(DateSerial([Year], [MthNbr] + 1, 0)) as MaxYr,
Month(Max(DateSerial([Year], [MthNbr] + 1, 0)) as
MaxMthNbrForMaxYr
FROM yourTable
GROUP BY CustID

HTH
Dale

 

[Stefan Hoffmann]

hi Jim,

How can I structure code or a query to find the minimum and maximum [Year]
and [MthNbr] for each [CustID], one record per [CustID], e.g.

SELECT CustID,
DMin("[Year]","", "CustID=" & CustID),
..
FROM Table1
GROUP BY CustID



mfG
--> stefan <--

 

[Stefan Hoffmann]

hi Jim,

I'm not sure this is going to work.I'm trying to get the minimum and maximum
values in a single record. e.g. the following code,

SELECT
CustID,
DMin("[Year]","Table1","CustID=" & [CustID]) AS MinYear,
DMin("[Nbr]","Table1","[Year]=" & DMin("[Year]","Table1") & "
And CustID=" & [CustID]) AS MinYearNbr,
DMax("[Year]","Table1","CustID=" & [CustID]) AS MaxYear,
DMax("[Nbr]","Table1","[Year]=" & DMax("[Year]","Table1") & "
And CustID=" & [CustID]) AS MaxYearNbr
FROM Table1
GROUP BY CustID;

yields the following incomplete results

The inner DMin/DMax must also be filtered on CustID.


mfG
--> stefan <--