Query prompt to change field retrieved

  • Thread starter Thread starter chilli_Novice
  • Start date Start date
C

chilli_Novice

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?
 
I'm working with a table with a field for each month of the year. (can't be
changed)
Click to expand...

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?
Click to expand...

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].
 
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 said:
I'm working with a table with a field for each month of the year. (can't be
changed)
Click to expand...

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?
Click to expand...

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].
Click to expand...
 
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.
Click to expand...

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)?
 
Back
Top