Subquery

[JimP]

In the example below, can a single query using subqueries be created to
extract the lowest MthNbr for the lowest year and the highest MthNbr for the
highest year. i.e.

Table1
[Year] [MthNbr]
2004 7
2004 9
2006 3
2006 5

Query results 2004, 7, 2006, 5 (MinYear, MinMthNbr for MinYear, MaxYear,
MaxMthNbr forMaxYear)

 

[Stefan Hoffmann]

hi Jim,

Query results 2004, 7, 2006, 5 (MinYear, MinMthNbr for MinYear, MaxYear,
MaxMthNbr forMaxYear)

Try

SELECT Min([Year]),
DMin("MthNbr", "Table1", "[Year]=" & DMin("[Year]", "Table1")),
DMax("[Year]", "Table1"),
DMax("MthNbr", "Table1", "[Year]=" & DMax("[Year]", "Table1"))
FROM Table1


mfG
--> stefan <--

 

[JimP]

Thank you, I kind of figured that out later - that "D" functions were the
answer.

hi Jim,

Query results 2004, 7, 2006, 5 (MinYear, MinMthNbr for MinYear, MaxYear,
MaxMthNbr forMaxYear)

Try

SELECT Min([Year]),
DMin("MthNbr", "Table1", "[Year]=" & DMin("[Year]", "Table1")),
DMax("[Year]", "Table1"),
DMax("MthNbr", "Table1", "[Year]=" & DMax("[Year]", "Table1"))
FROM Table1


mfG
--> stefan <--