query or vba script to Delete 4th column from a access 2013 table

[nmshivu]

I have below column structure in access 2013 table.
I have to maintain only 6 Data columns (Data followed by a number) in access 2013 db. Starting every month I need to remove first "Data" column, for instance, this month if I run the query it should delete "Data1" column next month "Data2" so on and so forth.
I will run another query to insert the next set of Data Columns, for example, Data7 from another table once the first data column is deleted.
Urgent help on how to do this is appreciated.


Thanks in advance.




ServerName
Description
Data1
Data2
Data3
Data4
Data5
Data6

[ranman256]

run a query:
update table set [field] = null

[Minty]

This sounds like a 101 on how NOT to use a database.
The data you are importing shouldn't be put into a new field (Spreadsheets have columns, Databases have fields), but stored vertically with a identifier for the month, something like

Server name
Description
Data
DataDate

Then you can add as many months data as you like and query it easily, without having to do anything else.

[nmshivu]

run a query:
update table set [field] = null

Can I know the query to delete the 4th column everytime i run it.

[nmshivu]

Thanks for the suggestion. The requirement would still be to delete the 4th column. Appreciate your assistance in getting the solution for same.

[Minty]

You wouldn't have a fourth data column. You would only have four columns and keep adding data to them.

ServerName	Description	March_Data	April_Data	May_Data	etc	etc
ABC1	SQL Server	3456	4545	4311
ABC2	Exchange	45567	7667	32187
	wrong
	right
ServerName	Description	Data	DataDate
ABC1	SQL Server	3456	01/03/2017
ABC1	SQL Server	4545	01/04/2017
ABC1	SQL Server	4311	01/05/2017
ABC2	Exchange	45567	01/03/2017
ABC2	Exchange	7667	01/04/2017
ABC2	Exchange	32187	01/05/2017

[orange]

As Minty has advised, your current table structure/approach is suspect.
In my view you have determined How you will do something and have chosen "spreadsheet".
I doubt that anyone said "The requirement would still be to delete the 4th column."

Requirements are derived from some business need --which can be described in plain English. Once you say the requirement is to do something with a table or column --you have jumped to a physical solution. You need to do more analysis to identify the real business requirement. Once found/described, there may be several options for database design.

Good luck with your project.

[ssanfu]

Thanks for the suggestion. The requirement would still be to delete the 4th column. Appreciate your assistance in getting the solution for same.

Sorry to be blunt, but it sounds like you are bound and determined to do something stupid.

Constantly adding and deleting fields greatly increases the chances of corrupting your dB. You need to create a normalized design........ (see Post #6)



You have been warned!!



You will need to use Data Definition Language (DDL).
Data definition language may sound like it's another programming language, but it's actually a method of categorizing the different types of SQL commands.

Data definition language (DDL) refers to the set of SQL commands that can create and manipulate the structures of a database. DDL statements are used to create, change, and remove objects including indexes, triggers, tables, and views.

See https://www.w3schools.com/SQl/sql_alter.asp

You must go into SQL View of a query and type in the SQL commands. The syntax is:

ALTER TABLE table_name
DROP COLUMN column_name;

Here is an example:

ALTER TABLE tblPersons
DROP COLUMN DateOfBirth;

When you execute the query, THERE IS NO WARNING!! The field and data will be gone. Be very sure you know what you are doing!!

Do LOTS of testing on a trash dB...........


Good luck... you are going to need it..........

[nmshivu]

Below is the sample DB similar to the DB I am working on. As you can see there are 6 months data. my contractual obligation is to maintain only 6 months data. Now if I am supposed to add July 17 month data I have to remove the Jan-17 column. Since I cannot do it based on column name as it changes every month hence trying to find a way to delete by using the column number like Jan-17 is 3rd column in the table.
Please do let me know if you need more information.
https://msdn.microsoft.com/en-us/vba...roperty-access

[June7]

I looked at using TableDefs to remove field and still required the actual field name, not a field index.

CurrentDb.TableDefs("tablename").Fields.Delete("fi eldname")

So, assuming you always want the field with index 3 (that's the 4th field because index begins with 0 and Jan-17 is the 4th field, not 3rd), consider:

Sub CreateCalculatedField()
    Dim rs As DAO.Recordset, strField As String
    Set rs = CurrentDb.OpenRecordset("SELECT * FROM Table1")
    strField = rs.Fields(3).Name
    rs.Close
    CurrentDb.TableDefs("Table1").Fields.Delete (strField) 
    'or use SQL action statement
    CurrentDb.Execute "ALTER TABLE Table1 DROP COLUMN " & strField
End Sub

However, since you are repeatedly adding and deleting fields, can't be certain field order in table is the same as displayed in the image. So need more code that cycles through the recordset field names, converts those month abbreviations to a month number to determine which is the earliest month and delete that field by name.

I also recommend you normalize data structure and retain records only for 6-month period. Delete records that are dated prior to specified date. Or just apply filter criteria to view only the desired 6-month period. I really cringe at the thought of deleting data. I guarantee you will regret it someday.

[ssanfu]

Here is an example of a normalized table with your data:



This is what a form would look like (record source is based on a query)



A query to delete data before a specified date (if you have a text box control on a form):

DELETE *
FROM Table1
WHERE Table1.DataDate < [Forms]![Form1].[DelDate];

In the table, I enter the date as the last day of the month.
The control "DelDate" could be the first date of the earliest month you want to keep.

Now if I am supposed to add July 17 month data I have to remove the Jan-17 column.

In a normalized data structure, you would delete RECORDS (NOT fields). The query to delete Jan-17 (and earlier) data would be (if you were manually entering the date)

DELETE *
FROM Table1
WHERE Table1.DataDate < #2/1/2017#;

If, in the future, your requirements changed so that you needed to keep 12 months data, you just change the delete date.
If the requirement changed so you needed to only keep 4 months data, all you have to do is change the delete date, not change the table structure and code to delete fields.