Date Query

I have created a table with the following fields

ID (autonumber)
RID (Lookup)
Weight (number,Single, 1 decimal place)
Date of (Date)

I need to create a query with:

1. requested input value for year
2. an expression that will tell me the FIRST Weight in January (of the year
provided by user input)

I can use the january expression as a template to create the rest of the
months of the year


I know you guys can help me...you always do....so whatcha think? Thank you
very much in advance.

Thanks,
Scottie

Looks like I forgot to mention that the query should be generated for each
person in the Resident Table(RID)...

Query one to get FirstDate in Month for each RID
SELECT RID
, Month([DateOf]) as MonthNum
, Min([DateOf]) as FirstInMonth
FROM YourTable
WHERE [DateOf] Between DateSerial([What Year?],1,1) and DateSerial([What
Year?],12,31)
GROUP BY RID, Month([DateOf])

Using that query along with the original table to get the results

SELECT YourTable.*
FROM YourTable INNER JOIN QueryOne
ON YourTable.RID = QueryOne.RID
AND YourTable.[DateOf] = QueryOne.FirstInMonth

You can do this all in one correlated query. It will probably be a lot
slower than the two query solution
SELECT YourTable.*
FROM YourTable
WHERE [DateOf] =
(SELECT Min([DateOf])
FROM YourTable as YT2
WHERE YT2.RID = YourTable.RID
AND Month(YT2.[DateOf]) = Month(YourTable.[DateOf]) )
WHERE YourTable.[DateOf] Between DateSerial([What Year?],1,1) and
DateSerial([What Year?],12,31)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

"Scottie" wrote in message
...
I have created a table with the following fields

ID (autonumber)
RID (Lookup)
Weight (number,Single, 1 decimal place)
Date of (Date)

I need to create a query with:

1. requested input value for year
2. an expression that will tell me the FIRST Weight in January (of the
year
provided by user input)

I can use the january expression as a template to create the rest of the
months of the year


I know you guys can help me...you always do....so whatcha think? Thank
you
very much in advance.

Thanks,
Scottie

John,

I used your sql code to create the following

weightqry1

SELECT RID, Month([DateOf]) AS MonthNum, Min([DateOf]) AS FirstInMonth
FROM WeightTbl
WHERE [DateOf] Between DateSerial([What Year?],1,1) and DateSerial([What
Year?],12,31)
GROUP BY RID, Month([DateOf]);

weightqry2

SELECT WeightTbl.*
FROM WeightTbl INNER JOIN weightqry1 ON (WeightTbl.Dateof =
weightqry1.FirstInMonth) AND (WeightTbl.RID = weightqry1.RID);


I am still having an issue. right now the data is returning the following
in weightqry2

RID Weight Dateof
King, H 138.8 1/1/2007
King, H 134.4 2/1/2007
King, H 131.6 3/1/2007
King, H 136.8 4/1/2007
King, H 138.4 5/1/2007
King, H 139.6 6/1/2007
King, H 144 7/1/2007
King, H 145.2 8/1/2007
King, H 148.2 9/1/2007
King, H 142.4 10/1/2007

I would like it to return as:

RID January February March .....ect...
King, H 138.8 134.4 131.6

Thanks a lot for the help....looking forward to your solution

Scottie

Use weightqry2 as the source for a crosstab query.

TRANSFORM First(Weight)
SELECT RID
FROM WeightQry2
PIVOT Format(DateOf,"mmmm")
IN ("January","February",...,"November","December" )

'================================================= ===
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'================================================= ===


Scottie wrote:
John,

I used your sql code to create the following

weightqry1

SELECT RID, Month([DateOf]) AS MonthNum, Min([DateOf]) AS FirstInMonth
FROM WeightTbl
WHERE [DateOf] Between DateSerial([What Year?],1,1) and DateSerial([What
Year?],12,31)
GROUP BY RID, Month([DateOf]);

weightqry2

SELECT WeightTbl.*
FROM WeightTbl INNER JOIN weightqry1 ON (WeightTbl.Dateof =
weightqry1.FirstInMonth) AND (WeightTbl.RID = weightqry1.RID);


I am still having an issue. right now the data is returning the following
in weightqry2

RID Weight Dateof
King, H 138.8 1/1/2007
King, H 134.4 2/1/2007
King, H 131.6 3/1/2007
King, H 136.8 4/1/2007
King, H 138.4 5/1/2007
King, H 139.6 6/1/2007
King, H 144 7/1/2007
King, H 145.2 8/1/2007
King, H 148.2 9/1/2007
King, H 142.4 10/1/2007

I would like it to return as:

RID January February March .....ect...
King, H 138.8 134.4 131.6

Thanks a lot for the help....looking forward to your solution

Scottie