I have data in two forms. Depending on suggestions I receive on how to handle the problem I will describe below I could go either way.
The common format between the two is I have GL Transaction detail. Two of these fields are [MonthName] (i.e., "Sept-2015") which was populated based on the link between the transaction date in the data and a [REF_Tbl_Date] mapping table. The other important field in common between the two formats is a [AmountUSD] which is the transaction amount in USD.
I created the second format in that I created fields based on [MonthName] and [AmountUSD]. In other words, instead of just having the MonthName field as part of the data I created fields with the names of each of the Month Names (i.e., "Sep-2015", "Oct-2015" reading left to right if you view in datasheet view.
What I want to do is use a criteria selection form (called [Frm_PARAMETER_Metrics] on which the user selects the current reporting month. From there I have three text fields for the prior three months. This all works no problem. I just can't figure out how to bring it all home.
My first thought was to have the criteria form drive the field names on the query. In other words, if the user selected "Sep-2015" then "Sep-2015" would be a field name on the query and that is what would show (along with the GL Account number formatting of the query. Is there a way of dictating the field listed in a query based on form selection?
I apologize, I should probably treat these as two separate threads but when my first approach did not work I decided to simply to use the [MonthName] field to drive the query. For example, If the user selected "Sep-2015" as the current month I tried having a "CurrentMonth" field name in the query that summed [AmountUSD] Where the [MonthName] in rows of data = "Sep-2015" and CMLess1 field that summed [AmountUSD] Where [MonthName] in the rows of data = "Aug-2015", etc....
The first approach, to me, seems would be very nice to be able to do but I am guessing the second approach is more likely a possibility? I couldn't get either to work and hope I can get some guidance.
Thank you.
The common format between the two is I have GL Transaction detail. Two of these fields are [MonthName] (i.e., "Sept-2015") which was populated based on the link between the transaction date in the data and a [REF_Tbl_Date] mapping table. The other important field in common between the two formats is a [AmountUSD] which is the transaction amount in USD.
I created the second format in that I created fields based on [MonthName] and [AmountUSD]. In other words, instead of just having the MonthName field as part of the data I created fields with the names of each of the Month Names (i.e., "Sep-2015", "Oct-2015" reading left to right if you view in datasheet view.
What I want to do is use a criteria selection form (called [Frm_PARAMETER_Metrics] on which the user selects the current reporting month. From there I have three text fields for the prior three months. This all works no problem. I just can't figure out how to bring it all home.
My first thought was to have the criteria form drive the field names on the query. In other words, if the user selected "Sep-2015" then "Sep-2015" would be a field name on the query and that is what would show (along with the GL Account number formatting of the query. Is there a way of dictating the field listed in a query based on form selection?
I apologize, I should probably treat these as two separate threads but when my first approach did not work I decided to simply to use the [MonthName] field to drive the query. For example, If the user selected "Sep-2015" as the current month I tried having a "CurrentMonth" field name in the query that summed [AmountUSD] Where the [MonthName] in rows of data = "Sep-2015" and CMLess1 field that summed [AmountUSD] Where [MonthName] in the rows of data = "Aug-2015", etc....
The first approach, to me, seems would be very nice to be able to do but I am guessing the second approach is more likely a possibility? I couldn't get either to work and hope I can get some guidance.
Thank you.
