Random Monthly Averages for 12 months -dependent on user input 1

[gwoman]

I am trying to create a query that will produce monthly averages based on a date that is input by the user. Meaning if a user selects Aug 1990 the results would be averages from Aug 1990 thru Aug 1991 .... if the user selects Dec 1991 the result would be Dec 1991 thru Dec 1992 ... etc.

Here is my query that shows 1 month with the date hard coded (the date field will ultimately point to my form's date input box)


SELECT DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE, DDB_ALT_LAND_LSE_TB.ALT_LAND_LSE_NME, Sum(DDB_FINL_WELL_ALOC_TB.WELL_OIL_ALOC_QTY) AS SumOfWELL_OIL_ALOC_QTY, Count(DDB_FINL_WELL_ALOC_TB.WZ_CTRL_NBR) AS CountOfWZ_CTRL_NBR, Sum(DDB_FINL_WELL_ALOC_TB.WELL_MNLY_OPDY_QTY) AS SumOfWELL_MNLY_OPDY_QTY, ([SumOfWELL_OIL_ALOC_QTY]/[SumOfWELL_MNLY_OPDY_QTY]) AS [Average BPD]
FROM DDB_FINL_WELL_ALOC_TB INNER JOIN DDB_ALT_LAND_LSE_TB ON DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE = DDB_ALT_LAND_LSE_TB.ALT_LAND_LSE_CDE
WHERE (((DDB_FINL_WELL_ALOC_TB.WELL_MNLY_ALOC_DTE)=#8/1/1990#))
GROUP BY DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE, DDB_ALT_LAND_LSE_TB.ALT_LAND_LSE_NME
HAVING (((DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE)="43117") AND ((Sum(DDB_FINL_WELL_ALOC_TB.WELL_OIL_ALOC_QTY))>0))
ORDER BY DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE;

Thanks
gwoman

 

[PHV]

SELECT Format(DDB_FINL_WELL_ALOC_TB.WELL_MNLY_ALOC_DTE,'yyyy-mm') As [Month], DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE,
...
WHERE (DDB_FINL_WELL_ALOC_TB.WELL_MNLY_ALOC_DTE Between #8/1/1990# And DateAdd('yyyy',1,#8/1/1990#)-1)
AND DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE='43117'
GROUP BY Format(DDB_FINL_WELL_ALOC_TB.WELL_MNLY_ALOC_DTE,'yyyy-mm'), DDB_FINL_WELL_ALOC_TB.ALT_LAND_LSE_CDE, DDB_ALT_LAND_LSE_TB.ALT_LAND_LSE_NME
HAVING Sum(DDB_FINL_WELL_ALOC_TB.WELL_OIL_ALOC_QTY)>0

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

 

[gwoman]

Excellent ... thanks PHV!!!