Can I add a column to a table and give it a name using VBA?

[Phonik]

Hi all

I am currently building a holiday planner for my employers and I would like to try and display the days of the year as rows and the staff as columns (Fields). When someone adds a new member of staff, I would like it to also add them to the calendar table as a new column and name the column the same as the individuals name. Is this possible using VBA or SQL string?

If anyone has a better idea as to how I can display the forthcoming holidays for a team I would really appreciate your comments.

Thanks

Gareth

 

[tehNellie]

It sounds like you've got your table structure a bit back to front somewhere if you're trying to use this approach but without more information regarding how you've got your setup currently planned it's a bit tricky to offer any more detailed advice.

 

[Moniker]

You have the design backward. As soon as you have more than 255 employees, your table breaks. Put days of the week in columns and employees in rows. Remember, thin and tall (a few columns and lots of rows) is better than fat and skinny (lots of columns and a few rows) in DB design.

As for adding a new field, look into this:

CurrentDb.TableDefs("YourTableName").CreateField

You'll end up with this:

CurrentDb.TableDefs("YourTableName").Append CurrentDb.TableDefs("YourTableName").CreateField <field attributes here>

To do this in SQL, you use Data Definition Language (DDL):

CurrentDb.Execute "ALTER TABLE YourTableName ADD COLUMN NewFieldName NewFieldDataType;"

Search Google for MS Access DDL Reference for help on using DDL in Access (and for a list of the available datatypes and their parameters).

 

[gemma-the-husky]

presentation and data storage are different things

the most important thing is to make sure that your data is properly analysed and normalised. You can then arrange to display it in forms/reports etc to suti your requirements.

dont think of your data in terms of the "spreadsheet" you want to see


eg if you have a staffholidays table with fields staffno, datefrom, dateto etc

you can presenting this as columns of dates, and names of staff in rows (which i would have thought was more normal). or you can present it as rows of dates and columns of staff names. It just needs a bit of creative design.

I do something like this to through a vehicle availability chart tick chart, with columns showing weekly availability. It actually presents as a display of check boxes. If you click a check box it sets/clears the availability of the vehicle (this isnt exactly what it does, but effectively thats what happens)