Check if specific column name exist in a table

[Zandra]

Hi There,
I have a table that table contain few columns but i am looking for specific columns and if found those specific columns; i want to perform simple calculation. Here is the pseudocode for what i am trying to do:

Check if column names exist first (in this case the spefic column are Profit and Spend)
if column names found then
insert new columns and give it any name
then perform calculation which is (Profit - Spend)
Else if specific column names not found then
exit the code

thanks

 

[Zandra]

Can someone help me here? thanks

 

[Banana]

I'm kind of fuzzy on 'why'.

Normally, we would already know where the columns are and write a simple SQL statement like this:

SELECT Profit - Spend
FROM myTable;

But the fact that you are asking this question suggests to me two things:

1) You have several tables with same column
2) You have one table but with several columns that contains the keyword in the name.

and either are not properly normalized and the direct answer to your question would be of necessity overcomplicated and time-consuming along several other problems.

It's possible that I'm not understanding 'why' and would appreciate any corrections/clarifications.

 

[boblarson]

and either are not properly normalized

Not normalized IS the answer. But, I believe this may be a data warehouse type situation, unless I'm mistaken (see Zandra's other posts).

You could check to see if a field is there by using:

Function DoesFieldExist(strFieldName As String) As Boolean
Dim fld As DAO.Field
Dim tdf As DAO.TableDef

For Each tdf in CurrentDb.TableDefs
   For Each fld in tdf.Fields
        If fld.Name = strFieldName Then
           DoesFieldExist = True
           Exit Function
        End If
    Next fld
Next tdf
End Function

(air code - untested)

 

[Zandra]

Banana,
Thanks for your response. Just to clarify, i have a table that has several columns and in this table i could have the following columns : 2006, 2007, 2008, 2009 and etc. So the table is generated per user's selection meaning the user can select to include in the table the revenues for 2006 and 2007 only or another user may select to include the revenue for 2009 and 2010. So as you can see these are dynamic and i want to perform some calculation. For instance, if user selects revenue for 2006 and 2007 then i want to insert a new column and name it like "2006 - 2007 Delta" and calculate the difference between 2006 and 2007.

 

[Banana]

Okay, it's not normalized.

I'd really, really, really encourage you to read up on normalization and redesign the table so all 200X-200X goes into a single table, then it's now a simple matter of writing a query using appropriate WHERE clause.

 

[Zandra]

I am not the expert but i normalized now but i am not sure how to calculate it. For instance, 2006-2007 and 2007-2008 but please keep in mind the years are dynamic. Pls. see attached how i normalized. I really appreciate your help. thanks.