asked on

display a prior month value in a query for a field

display a prior month value in a query for a field.
In this Table: dbo_QCS_CRS_SCORE
Example data:
JANUARY = 33.4
FEBRUARY = 98.2
MARCH = 96.3

I have the fields JANUARY TO DECEMBER AS FIELD NAMES. IN "dbo_QCS_CRS_SCORE"
i WOULD LIKE TO ADD THIS TO THE QUERY BELOW AND SHOW THE PRIOR MONTH SCORE.

so say this is March I need to display February field.
When April 1st is here. I need to display March field ?

SELECT [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME] AS NAME, QCS_CRS_EMPLOYEE_DIRECTORY.user_id, QCS_CRS_EMPLOYEE_DIRECTORY.EMPLOYEE_STATUS, dbo_QCS_CRS_SCORE.YTD_AVG
FROM QCS_CRS_EMPLOYEE_DIRECTORY INNER JOIN dbo_QCS_CRS_SCORE ON QCS_CRS_EMPLOYEE_DIRECTORY.user_id = dbo_QCS_CRS_SCORE.RACFID
WHERE (((QCS_CRS_EMPLOYEE_DIRECTORY.TITLE_DESCRIPTION) In ('On-Site Specialist','Desktop Specialist','Reference Analyst')))
ORDER BY [EMPLOYEE_FIRST_NAME] & " " & [EMPLOYEE_LAST_NAME];

Open in new window

Thanks
fordraiders

ASKER CERTIFIED SOLUTION

therealmongoose

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

Field and table names cannot be dynamic, so you will have to use VBA to modify the SQL to select the month field from the twelve possible.
That said, you need to normalise your table - the month's value should be read from a child table.

Fordraiders

ASKER

used this also:
Dim currentmonth As String
Dim mname As String
Dim mnumber As Long

mnumber = Month(Date)

mname = MonthName(mnumber, False)
currentmonth = mname

Me.EMPLOYEE_RACFID = Me.cboEMPLOYEE.Column(1)
Me.ASSOC_STATUS = Me.cboEMPLOYEE.Column(2)

Select Case currentmonth
Case "January"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(15)
Case "February"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(4)
Case "March"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(5)
Case "April"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(6)
Case "May"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(7)
Case "June"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(8)
Case "July"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(9)
Case "August"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(10)
Case "September"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(11)
Case "October"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(12)
Case "November"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(13)
Case "December"
Me.SCORE_INPUT = Me.cboEMPLOYEE.Column(14)
End Select

Gustav Brock

If that is all you need, it could be:

PreviousMonth: MonthName(DateAdd("m", -1, Date()), False)

Open in new window

Fordraiders

ASKER

Thanks