Prompt user for Field

  • Thread starter Thread starter DtTall
  • Start date Start date
D

DtTall

Hello,

I have a problem that I think should be easy but does not seem to be.
I have an update query that updates CYF (current year forecast) to
CYFAPR (for a snapshot of the forecast as of April or whatever month I
am taking the snapshot for). I have other queries that update CYF and
therefore to preserve other data I do not want to have those queries
write directly to CYFAPR.

Anyway, my issue is that when I run the query I would like to have
Access prompt for the field that I want CYF to update to (i.e. when I
run it for May I can enter CYFMAY and CYF copies to CYFMAY). I cannot
seem to make a prompt work for a field. Here is my SQL so far:

UPDATE MASTER SET MASTER.CYFAPR = [MASTER]!CYF;


Any help would be great. Thanks in advance,

Dttall
 
Hello,

I have a problem that I think should be easy but does not seem to be.
I have an update query that updates CYF (current year forecast) to
CYFAPR (for a snapshot of the forecast as of April or whatever month I
am taking the snapshot for). I have other queries that update CYF and
therefore to preserve other data I do not want to have those queries
write directly to CYFAPR.

Anyway, my issue is that when I run the query I would like to have
Access prompt for the field that I want CYF to update to (i.e. when I
run it for May I can enter CYFMAY and CYF copies to CYFMAY). I cannot
seem to make a prompt work for a field. Here is my SQL so far:

UPDATE MASTER SET MASTER.CYFAPR = [MASTER]!CYF;


Any help would be great. Thanks in advance,

Dttall
Click to expand...

If you have fields for CYFAPR, CYFMAY, CYFJUN and so on... your database
structure IS WRONG.

"Fields are expensive. Records are cheap." If you have a one to many
relationship, model it as a one to many relationship, with two tables! Each
month's data should be *in a new record*, not as another field in your record.

You can use a Crosstab query if desired to *present* the normalized data in a
wide-flat form; but storing it that way is simply incorrect design.
 
While I appreciate the advice on how to set up the database my
original question is unanswered. How do you prompt for a field name?
 
You can't do this with a parameter prompt.

IF you are a bit experienced in VBA you can build the SQL qiery on the fly
and execute the built query string.

--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
While I appreciate the advice on how to set up the database my
original question is unanswered. How do you prompt for a field name?
Click to expand...

You can't, in a parameter query. One of many reasons why storing data in
fieldnames is bad design!

You will need to write VBA code to construct the entire SQL string of the
query, inserting the fieldname in its proper place.
 
John S. and John V. have suggested an approach.

Now, you can choose to "appreciate the advice" and ignore it, but you risk
"paining yourself into a corner" that will only become more and more
difficult to get out of.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
John, Jeff

Thanks both for your help. There is a reason that I am storing the
data this way. I might point out that this is the same format that
SRC BusinessObjects uses to store time series. There are other
reasons that I am doing it this way (such as having to have an Excel
front end because Access is not supported across the company and
explaining to users why I am having them enter data in a ugly way that
doesn't make sense is not a fight I want to fight.), but it doesn't
matter.

All that aside, thanks for your answers and helping out users with
questions.

David
 
John, Jeff

Thanks both for your help. There is a reason that I am storing the
data this way. I might point out that this is the same format that
SRC BusinessObjects uses to store time series. There are other
reasons that I am doing it this way (such as having to have an Excel
front end because Access is not supported across the company and
explaining to users why I am having them enter data in a ugly way that
doesn't make sense is not a fight I want to fight.), but it doesn't
matter.

All that aside, thanks for your answers and helping out users with
questions.

David
Click to expand...

What you might want to do is to have two tables - the wide-flat
BusinessObjects/Excel model, and a normalized table; you could have two
queries set up to migrate data from one to the other. It sounds like you'll
want to have the wide-flat as the master; if so you could create a normalizing
UNION query to migrate the data into the tall-thin normalized table for Access
calculations, reports, etc.
 
Back
Top