Date Convert - Totals for previous 12 Month From a Yr/mo

[Rx_]

Any suggestions, comments, or improvements on this approach or examples would be appreciated (tables are under 100,000 records)

After importing thousands of Excel templates into a table, the emission data date types may need to be converted for efficient SQL.
Will write a formula to convert the strings Jan, Feb, Mar, ... into a new Mo column with 1, 2, 3, ...

The SQL based report is intended to create a Total(Value) for each Site for the previous 12 months from a Year + Mo Point-in-time. Sometimes there will not be 12 months for example if it is a new location with only 3 months of data. Values are entered once a month.

Thinking of sorting by Year, Mo then taking a Top 12 query (then Totals)
Would a Parameter for a Where Date is < [Year & Mo + 3 days] be the solution?
Year is currently of type Text, going back 0 to 5 years.

Site Value Year Month| MO
S007 1.222 2012 Sep | 9
S007 1.444 2012 Aug | 8
S007 1.449 2012 Jul | 7

 

[Pat Hartman]

I would convert the data to a real date/time data type. Convert the text month to numeric and concatenate the fields, using 1 as the day and use CDate to convert the string to a date. Then you can use the field as a date and select a range.