Query prompt to change field retrieved chilli_Novice 12/29/2008 9:46:04 PM I'm working with a table with a field for each month of the year. (can't be changed) I need to query data for last month and I would like to be able to prompt for a field. Does anyone have some code that would allow me to do this?

Re: Query prompt to change field retrieved John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 12/30/2008 12:10:19 AM On Mon, 29 Dec 2008 13:46:04 -0800, chilli_Novice <chilliNovice[ at ]discussions.microsoft.com> wrote: [Quoted Text] >I'm working with a table with a field for each month of the year. (can't be >changed) Why can't it be changed? It's WRONG. It simply is. This is a good spreadsheet but it is *not* correct design for a table! Is there a Year field? How can you tell whether [January] refers to January 2008, or 2009, or 1737? > I need to query data for last month and I would like to be able to >prompt for a field. Does anyone have some code that would allow me to do >this? > You can't use parameters to select a field. You'll need to either build a SQL string based on the value of a form control, or restucture the data. You can do so temporarily and dynamically using a UNION query. This needs to be built in the SQL window (not the query grid). You can "unravel" the data with a query like SELECT this, that, "January" AS TheMonth, [January] AS TheValue FROM yourtable UNION ALL SELECT this, that, "February" AS TheMonth, [February] AS TheValue FROM yourtable UNION ALL SELECT this, that, "March" AS TheMonth, [March] AS TheValue FROM yourtable UNION ALL < etc > You can then base a parameter query with a criterion of "February" on the field TheMonth and look for the value for that month in [TheValue]. -- John W. Vinson [MVP]

Re: Query prompt to change field retrieved chilli_Novice 12/30/2008 2:32:01 PM I guess I should have said that I can't change since I'm only a user of this application and not on the IT staff. I understand that it's not good practice but it's what I have to deal with. there is a field for year and for financial line. So I would query for 2008 and Financial line = to Total revenue but each month I need to bring up only the last completed month. I would love to automate this so I don't have to modify each query before I run them each month. any help would be appreciated. "John W. Vinson" wrote: [Quoted Text] > On Mon, 29 Dec 2008 13:46:04 -0800, chilli_Novice > <chilliNovice[ at ]discussions.microsoft.com> wrote: > > >I'm working with a table with a field for each month of the year. (can't be > >changed) > > Why can't it be changed? It's WRONG. It simply is. This is a good spreadsheet > but it is *not* correct design for a table! > > Is there a Year field? How can you tell whether [January] refers to January > 2008, or 2009, or 1737? > > > I need to query data for last month and I would like to be able to > >prompt for a field. Does anyone have some code that would allow me to do > >this? > > > > You can't use parameters to select a field. You'll need to either build a SQL > string based on the value of a form control, or restucture the data. You can > do so temporarily and dynamically using a UNION query. This needs to be built > in the SQL window (not the query grid). You can "unravel" the data with a > query like > > SELECT this, that, "January" AS TheMonth, [January] AS TheValue > FROM yourtable > UNION ALL > SELECT this, that, "February" AS TheMonth, [February] AS TheValue > FROM yourtable > UNION ALL > SELECT this, that, "March" AS TheMonth, [March] AS TheValue > FROM yourtable > UNION ALL > < etc > > > You can then base a parameter query with a criterion of "February" on the > field TheMonth and look for the value for that month in [TheValue]. > -- > > John W. Vinson [MVP] >

Re: Query prompt to change field retrieved John W. Vinson <jvinson[ at ]STOP_SPAM.WysardOfInfo.com> 12/30/2008 5:33:44 PM On Tue, 30 Dec 2008 06:32:01 -0800, chilli_Novice <chilliNovice[ at ]discussions.microsoft.com> wrote: [Quoted Text] >I guess I should have said that I can't change since I'm only a user of this >application and not on the IT staff. I understand that it's not good >practice but it's what I have to deal with. > >there is a field for year and for financial line. So I would query for 2008 >and Financial line = to Total revenue but each month I need to bring up only >the last completed month. I would love to automate this so I don't have to >modify each query before I run them each month. > >any help would be appreciated. If you'll post the relevant fieldnames and datatypes I'll rewrite my suggested UNION query so you can create your query. By "last completed month" do you mean the prior calendar month (i.e. November 2008 if run today, December 2008 if run next Monday, January 2009 if run on 2/5/2009)? -- John W. Vinson [MVP]