check if fields exist in a table then add new field

[Zandra]

Hi,
I have a table and i would like to check always 2 fields if they exist. If the 2 fields exist then i want to add a new field. How can i do this? here is the pseudocode:

check if field1 and field2 exist in MyTable
if field1 and field2 exist then
add new field
else
exit

 

[MSAccessRookie]

Hi,
I have a table and i would like to check always 2 fields if they exist. If the 2 fields exist then i want to add a new field. How can i do this? here is the pseudocode:

check if field1 and field2 exist in MyTable
if field1 and field2 exist then
add new field
else
exit

What will be the purpose of adding a new field?

 

[Zandra]

thanks for the quick response. The new field will have the result of calculation. For instance, if the 2 fields found then i want to substract field1 minus field2. That is all what i need. I hope you can help me with this as i was trying to figure out the last 3 days. many thanks.

 

[boblarson]

You don't add a field for calculations. You use a QUERY. You can use a query in 99.99% of the places where you would use a table.

 

[MSAccessRookie]

You don't add a field for calculations. You use a QUERY. You can use a query in 99.99% of the places where you would use a table.

This advice was the reason for my question in the first place. I agree that there is no reason to store the calculations.

 

[Zandra]

the fields are dynamic and they are based on user selection. The user may select field1 and field2 today but tomorow he/she may select field9 and field10 so that is why i am not using a query. I would not know which fields will be in the table because different users select different fields. thanks

 

[boblarson]

I would not know which fields will be in the table because different users select different fields. thanks

You still would use a query to do this. How does your entire process work? What is building the table? Where is the data coming from? How does it get there? How do users "select different fields?"

 

[Zandra]

The user selects the following from a drop down box
store name
store location
Revenue Year (the years are dynamic and they could be either be 2004, 2005, 2006, 2007.... and so on)

The user always selects start date and end date for the revenue. Once the user makes his/her own selection, then the user clicks the "Generate" button then it will run a query that generates a Temp_Table. Pls. see attached for how it look like the Temp_Table once the user runs the query. I want to be able to add a new field that calculates the difference between the 2 revenues

 

[Zandra]

I have answered all your question, so can someone help me here? Or please tell me if what i am trying to accomplish is not possible. thanks

 

[Scooterbug]

It's highly unusual for the need to add a field to an already exsisting table. It has been suggested that if you want to preform a calculation, you should use a query. Have you explored the possibility of using a query to get the calculation that you need?

Edit:
Looking at the test db you showed, a simple equation (like this: Difference: [2005]-[2006]) will get you the calculation you require.) If the field names change, then you will have to use code to generate the sql for the query.

Alternatively, you can use queries to get all the data for a set year if your tables have been properly normalized.

 

[boblarson]

Seeing only the temp table doesn't help a bit. Where does this data come from, how are you getting it, and how are you putting it in the table?

 

[Zandra]

You are just keep asking the same thing over and over. i came this forum to get help and clearly i am not an expert of coding or the techniques you are talking about

 

[boblarson]

You are just keep asking the same thing over and over. i came this forum to get help and clearly i am not an expert of coding or the techniques you are talking about

Yes, I keep asking the same thing over and over. Think of it this way - You come to your doctor for help and you tell him that you have a problem. He asks you what your problem is and you tell him, "I have a problem." He asks you again, "what is your problem?" And you answer back, "I have a problem."

Do you think he would be able to help you in any way? Well, that is basically what you keep doing to us. We are trying to give you guidance and help to get what you need (not necessarily what you WANT, but what you NEED) in order to do it RIGHT.

So, again, I will ask -

1. Where does your data come from?

2. How are you getting it to your "temp Table?"

3. What do you mean when you say - "users select different fields?" HOW do they do this? From where do they do this?

We need INFORMATION which you are not providing. I keep telling you that you do NOT need another field in your table for calculations. Well, if I know how your process works and what is ALL involved, we MIGHT just be able to do this the correct way WITHOUT adding a calculated field to the table which is a violation of normalization rules, and bad practice.

Do you understand?

 

[Banana]

Zandra-

A thought.

When several posters give the same answer (e.g. use a query to do a calculation), then it should alert you to the fact that something is amiss (namely the idea of adding field to a table).

It should be a simple matter of doing something like this:

SELECT Field1, Field2, IIf(Field1>0, Field2-Field1, Null) As Calc1
FROM ...

and you don't even need VBA code to create new field on the table which would be far more complicated.

 

[Zandra]

thanks Banana,
at least you are giving a hint. i appreciate that.

 

[gemma-the-husky]

but to take this a stage further, it sounds like you have columns in your table for each potential year/period which you might need to analyse in your final result. (ie like a spreadsheet) - but this isnt really the way to structure it in access. In access you should have a long thin column with a separate row for each year/period - so that THEN when you say you want information for 2007-2008, you just have a query that effectively filter out the relevant rows, and access automatically subtotals them for you.

so it becomes easy to just say - i want 2006 or 2007 (or both) or whatever, because the data is structured in a standard format.


-----------
alternatively you can perhaps find a way to process the data yorr way - but rather than adding a column, just have an extra column available to store your calcluation results - not ideal, but easier than adding columns in code, which is something you would hardly ever need to do.