Remove Empty Data Fields

I have a table with 40 fields and import data into it from another source. 
Sometimes there could be 50000 records and at other times there is 500 
records.

Is there a way to programatically remove fields from the table that do not 
not have a data in it.

Like :

if myTable.Season1 is null then Delete myTable.Season1

is this possible in a Loop checking to see if any fields are null.

Thanks
Craig 


0
Craig
8/9/2007 4:20:56 PM
access.queries 6343 articles. 1 followers. Follow

5 Replies
951 Views

Similar Articles

[PageSpeed] 57

Craig

Why?!  If you don't have data for a field, you don't put anything in there. 
Why would you want to delete the field itself?  And what will you do when 
what you are importing has MORE fields ... add new ones?

That way lies (maintenance) madness!  Every query, form, report, macro and 
code snippet that depends on the table being structured one way will break 
when you start deleting fields!

If you'll describe what you would be able to accomplish as a result of 
deleting those fields, the newsgroup readers may be able to offer an 
alternate approach..., one that doesn't impose such a severe maintenance 
burden.

Regards

Jeff Boyce
Microsoft Office/Access MVP

"Craig" <beaky1211@hotmail.com> wrote in message 
news:eC4xUEq2HHA.5756@TK2MSFTNGP04.phx.gbl...
>I have a table with 40 fields and import data into it from another source. 
>Sometimes there could be 50000 records and at other times there is 500 
>records.
>
> Is there a way to programatically remove fields from the table that do not 
> not have a data in it.
>
> Like :
>
> if myTable.Season1 is null then Delete myTable.Season1
>
> is this possible in a Loop checking to see if any fields are null.
>
> Thanks
> Craig
> 


0
Jeff
8/9/2007 5:25:56 PM
Try adding the following procedure to a standard module in the database.  It 
is fairly crude in that it makes the naïve assumption that any index on the 
column in question will be a single column index of the same name as the 
column.  To cater for all possibilities as regards indexes as constraints 
would require quite a lot more code:

Public Sub DropNullColumns(strTable As String)

    Const NO_INDEX = 3372
    Dim dbs As DAO.Database
    Dim tdf As DAO.TableDef
    Dim fld As DAO.Field
    Dim strSQL As String
    
    Set dbs = CurrentDb
    Set tdf = dbs.TableDefs(strTable)
    
    For Each fld In tdf.Fields
        If DCount(fld.Name, strTable) = 0 Then

            ' drop index if exists
             strSQL = "DROP INDEX " & _
                fld.Name & " ON " & strTable
            On Error Resume Next
            dbs.Execute strSQL
            If Err.Number = NO_INDEX Then
                ' ignore error
            Else
                ' unknown error
                If Err.Number <> 0 Then
                    MsgBox Err.Description, vbExclamation, "Error"
                End If
            End If

            ' restore default error handling
            On Error Goto 0
                    
            ' drop column
            strSQL = "ALTER TABLE " & strTable & _
                " DROP COLUMN " & fld.Name
            dbs.Execute strSQL
        End If
    Next fld
    
End Sub

Call the procedure, passing the name of the table into it, like so:

    DropNullColumns "YourTable"

Ken Sheridan
Stafford, England

"Craig" wrote:

> I have a table with 40 fields and import data into it from another source. 
> Sometimes there could be 50000 records and at other times there is 500 
> records.
> 
> Is there a way to programatically remove fields from the table that do not 
> not have a data in it.
> 
> Like :
> 
> if myTable.Season1 is null then Delete myTable.Season1
> 
> is this possible in a Loop checking to see if any fields are null.
> 
> Thanks
> Craig 
> 
> 
> 

0
Utf
8/9/2007 5:48:01 PM
I know this seems a little unusual, but there is a good reason.

I have to take data from a client system on a regular basis, clean up the 
data and format it, then import it into another system.

If I can remove the empty fields either from the table or exporting the data 
using a query where there fields are not null, it will save a heap of time 
by not having to go through all of the field mapping on the import (which 
the import preselects to the best match - badly).

Thanks
Craig



"Jeff Boyce" <nonsense@nonsense.com> wrote in message 
news:%23TJUZpq2HHA.1208@TK2MSFTNGP03.phx.gbl...
> Craig
>
> Why?!  If you don't have data for a field, you don't put anything in 
> there. Why would you want to delete the field itself?  And what will you 
> do when what you are importing has MORE fields ... add new ones?
>
> That way lies (maintenance) madness!  Every query, form, report, macro and 
> code snippet that depends on the table being structured one way will break 
> when you start deleting fields!
>
> If you'll describe what you would be able to accomplish as a result of 
> deleting those fields, the newsgroup readers may be able to offer an 
> alternate approach..., one that doesn't impose such a severe maintenance 
> burden.
>
> Regards
>
> Jeff Boyce
> Microsoft Office/Access MVP
>
> "Craig" <beaky1211@hotmail.com> wrote in message 
> news:eC4xUEq2HHA.5756@TK2MSFTNGP04.phx.gbl...
>>I have a table with 40 fields and import data into it from another source. 
>>Sometimes there could be 50000 records and at other times there is 500 
>>records.
>>
>> Is there a way to programatically remove fields from the table that do 
>> not not have a data in it.
>>
>> Like :
>>
>> if myTable.Season1 is null then Delete myTable.Season1
>>
>> is this possible in a Loop checking to see if any fields are null.
>>
>> Thanks
>> Craig
>>
>
> 


0
Craig
8/13/2007 4:30:44 PM
Brilliant  !!!

This is just perfect.  Thanks

Craig


"Ken Sheridan" <KenSheridan@discussions.microsoft.com> wrote in message
news:4EC38D71-1994-4DA6-A107-7847D027C45B@microsoft.com...
> Try adding the following procedure to a standard module in the database.
> It
> is fairly crude in that it makes the na�ve assumption that any index on
> the
> column in question will be a single column index of the same name as the
> column.  To cater for all possibilities as regards indexes as constraints
> would require quite a lot more code:
>
> Public Sub DropNullColumns(strTable As String)
>
>    Const NO_INDEX = 3372
>    Dim dbs As DAO.Database
>    Dim tdf As DAO.TableDef
>    Dim fld As DAO.Field
>    Dim strSQL As String
>
>    Set dbs = CurrentDb
>    Set tdf = dbs.TableDefs(strTable)
>
>    For Each fld In tdf.Fields
>        If DCount(fld.Name, strTable) = 0 Then
>
>            ' drop index if exists
>             strSQL = "DROP INDEX " & _
>                fld.Name & " ON " & strTable
>            On Error Resume Next
>            dbs.Execute strSQL
>            If Err.Number = NO_INDEX Then
>                ' ignore error
>            Else
>                ' unknown error
>                If Err.Number <> 0 Then
>                    MsgBox Err.Description, vbExclamation, "Error"
>                End If
>            End If
>
>            ' restore default error handling
>            On Error Goto 0
>
>            ' drop column
>            strSQL = "ALTER TABLE " & strTable & _
>                " DROP COLUMN " & fld.Name
>            dbs.Execute strSQL
>        End If
>    Next fld
>
> End Sub
>
> Call the procedure, passing the name of the table into it, like so:
>
>    DropNullColumns "YourTable"
>
> Ken Sheridan
> Stafford, England
>
> "Craig" wrote:
>
>> I have a table with 40 fields and import data into it from another
>> source.
>> Sometimes there could be 50000 records and at other times there is 500
>> records.
>>
>> Is there a way to programatically remove fields from the table that do
>> not
>> not have a data in it.
>>
>> Like :
>>
>> if myTable.Season1 is null then Delete myTable.Season1
>>
>> is this possible in a Loop checking to see if any fields are null.
>>
>> Thanks
>> Craig
>>
>>
>>
>



0
Craig
8/13/2007 4:35:29 PM
I think you might have come up with a very viable solution there.  Use a 
query, not a table.

When you don't have data in particular fields, don't include them in the 
query (but leave all the fields in the table).

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Craig" <beaky1211@hotmail.com> wrote in message 
news:%23aLDGdc3HHA.5796@TK2MSFTNGP05.phx.gbl...
>I know this seems a little unusual, but there is a good reason.
>
> I have to take data from a client system on a regular basis, clean up the 
> data and format it, then import it into another system.
>
> If I can remove the empty fields either from the table or exporting the 
> data using a query where there fields are not null, it will save a heap of 
> time by not having to go through all of the field mapping on the import 
> (which the import preselects to the best match - badly).
>
> Thanks
> Craig
>
>
>
> "Jeff Boyce" <nonsense@nonsense.com> wrote in message 
> news:%23TJUZpq2HHA.1208@TK2MSFTNGP03.phx.gbl...
>> Craig
>>
>> Why?!  If you don't have data for a field, you don't put anything in 
>> there. Why would you want to delete the field itself?  And what will you 
>> do when what you are importing has MORE fields ... add new ones?
>>
>> That way lies (maintenance) madness!  Every query, form, report, macro 
>> and code snippet that depends on the table being structured one way will 
>> break when you start deleting fields!
>>
>> If you'll describe what you would be able to accomplish as a result of 
>> deleting those fields, the newsgroup readers may be able to offer an 
>> alternate approach..., one that doesn't impose such a severe maintenance 
>> burden.
>>
>> Regards
>>
>> Jeff Boyce
>> Microsoft Office/Access MVP
>>
>> "Craig" <beaky1211@hotmail.com> wrote in message 
>> news:eC4xUEq2HHA.5756@TK2MSFTNGP04.phx.gbl...
>>>I have a table with 40 fields and import data into it from another 
>>>source. Sometimes there could be 50000 records and at other times there 
>>>is 500 records.
>>>
>>> Is there a way to programatically remove fields from the table that do 
>>> not not have a data in it.
>>>
>>> Like :
>>>
>>> if myTable.Season1 is null then Delete myTable.Season1
>>>
>>> is this possible in a Loop checking to see if any fields are null.
>>>
>>> Thanks
>>> Craig
>>>
>>
>>
>
> 


0
Jeff
8/13/2007 8:01:51 PM
Reply:

Similar Artilces:

vlookup w/ multiple data in a single cell
Is it possible to have a cell contain two pieces of informaton (e.g., A1 ="Me, You"), a vlookup statement to look for each pece of informaton in that cell (e.g., first "Me", then "You") in another sheet, and display the match in B1? Sheet 1 A1 = "Me, You" B1 = (after vlookup) "Art, Joe" Sheet 2 A1 = "Me" and B1 = "Art" A2 = "You" and B2 = "Joe" I tried all different kinds of =VLOOKUP with different functions, and I can't figure t out, of course I assume its possble to even use VLOOKUP ...

merging and formating data
I have data in 2 columns that i need to merge. IE: A B 234 245 needs to be: 234245 Now i know i can concantenate the data but then i'm stuck. The data can be of any number of rows, so i need to select only the cells with data in them. I then have to take the data and transpose it. IE: A 234 456 689 needs to be: A B C 234 456 689. This way i can save it as comma delimited and import it into another source. Basically i need my ending data to be 123,345,677,8984. Any ideas on how to get this all done? I would like to make it a macro so i dont have to manually do i...

csv-files with '+' in field -> treated as formula
When I make up a CSV-file (from our oracle-database) we have to fill in a line like this: 9603144;01;TEST;+BE;01/01/1000;31/12/9999 When opening the file in Excel, the 4th field (+BE) is treated as a formula, but that is not an option. Anyone have an idea how to write it in the CSV-file so that it is treated as a text-field but without showing any quotes (so I would like to see +BE and not '+BE) Tnx already in advance! Dave found something myself: just add a space before the + and it's ok if anyone has another idea: post it >-----Original Message----- >When I make up ...

change worksheet data without changing chart
I am attempting to create a scatter plot chart in Excel that will not get modified each time I sort my Excel worksheet. Does anybody know how to "lock" the chart or export it so that it is no longer linked to the worksheet? Thanks!!! Here's how to unlink a chart from its data: http://peltiertech.com/Excel/ChartsHowTo/DelinkChartData.html - Jon ------- Jon Peltier, Microsoft Excel MVP Peltier Technical Services Tutorials and Custom Solutions http://PeltierTech.com/ _______ shelly wrote: > I am attempting to create a scatter plot chart in Excel > that will not ge...

Lost Subject Field
I don't know how I did it but somehow I managed to delete the subject field in outlook so now when I go to create a new email to send to someone I only have the TO: line showing! I am unable to enter a subject! How do I get the subject back???? Go up to VIEW | MESSAGE HEADER. Kenny "Rockets" <wcrockets@hotmail.com> wrote in message news:00b601c3dc66$d8456fb0$a501280a@phx.gbl... > I don't know how I did it but somehow I managed to delete > the subject field in outlook so now when I go to create a > new email to send to someone I only have the TO: line &g...

will formulas be lost when moving data in a worksheet?
I have a P & L that contains 3 years of info. I have to delete the oldest year (2007) and put in our actual numbers for year ending 2010. Will I lose all of the formulas when I delete all of the 2007 numbers? Is there an easier way to do this? Edit/ Goto/ Special/ Constants Delete those. -- David Biddulph "Renae" <Renae@discussions.microsoft.com> wrote in message news:456322CE-EB1E-4616-8716-881CF545DC33@microsoft.com... >I have a P & L that contains 3 years of info. I have to delete the oldest > year (2007) and put in our actual numbers for y...

2 Subforms, can't change link fields for one
I have two subforms on my main form. The second is a copy of the first, but I want to link it to separate fields on the main form. When I open the main form in design view, the first subform shows in design view, but the second is just a blank control. The controlsource field is set to the same as the first. When I try to change the link child/master fields, it says: "The expression you entered refers to an object that is closed or doesn't exist." and blanks it out. What am I doing wrong? Thanks, =Ray= ...

remove "Protected Storage System Provider" do not help #2
After updating from Outlook2000 to Outlook XP(2002) OutlookXP keeps asking for passwords for every account :-((( I have tryed the trix "remove Protected Storage System Provider" in the registery (done by the letter as described in two Q-articles) but it do not help or change anything :-((( Please help -- erik Denmark ...

Importing data #2
Importing data into customer, item, suppliers, and GL master files. This is a brand new company and I need to import the master files from another system. I am able to easily dump each file to excel, but how do I import into GP? -- Gino S. GPK Computers Gino, Usually you would use an import tool like Integration Manager or eConnect. If this is a one time import, you're probably better off with Integration Manager. -- Victoria Yudin Dynamics GP MVP "Gino@GPK" <gino@gpk.net.au> wrote in message news:5B6CAB1C-FA16-408A-8242-8B1B9E80B011@microsoft.com... > Impo...

Autofilling data on a form
I get myself into the darndest things. I have a requirement that calls for creating a form in Excel that automatically inserts dates which change by calendar quarter and state. The user would like to be able to make one entry and have the form autofill the correct dates throughout the form. Ideally, a single form would be usable for several states, but individual state forms are the "fall back". Any help anyone could offer would be greatly appreciated. -- Al Aplin Crestview, Florida Hi Al this should be possible but i'll need more info (and the real fun thing is tha...

Copy & Paste Filtered Data
When I filter a column, then copy that column and paste into another worksheet (using F5/Special/Visible cells only), the pasted data is not the same as the data I wanted to copy. The pasted data omits certain rows, and I have no idea why. Does anyone know how I can do that correctly Similary, if I use the Advanced Filter command to get unique records only, I tried both ways and compared the data (results were not the same). 1st way is to filter the data in place, and the 2nd way is to copy the results to another location. When I tried both methods, the copied results were different (some...

Removing Item Highlight from CListCtrl
Hi I use MFC VC++ 4.2. I have a CListCtrl, a few CEdit, a Clear Button and a few buttons. Clear button function is to clear all CEdit and also clear the highlight from the selected item. My question is: 1. How to remove the highlight from the selected item? 2. I also need to disable a few buttons if there is no item selected. How to do those things? Thank you very much. >I have a CListCtrl, a few CEdit, a Clear Button and a few buttons. > >Clear button function is to clear all CEdit and also clear the highlight >from the selected item. > >My question is: >1. Ho...

removing duplicate records in excel, how to do it?
How to remove duplicart entries within excel? Data>advanced filter, unique records only Regards, Peo Sjoblom "Don" wrote: > How to remove duplicart entries within excel? Hi see: http://www.cpearson.com/excel/deleting.htm#DeleteDuplicateRows -- Regards Frank Kabel Frankfurt, Germany Don wrote: > How to remove duplicart entries within excel? Have you notice that the first entry is duplicated in both cases: when header line is included or exclude "Peo Sjoblom" wrote: > Data>advanced filter, unique records only > > Regards, > > Peo S...

Formulas for birth year and age in user defined fields
I'm using outlook 2002. I want to create 2 user-defined fields in my contacts, but am having trouble figuring out how to do it: 1. Year of birth field, based on the value entered in the birthday field - I created a new user defined field, with the Type set as Formula, and the formula set as: Year ([Birthday]). The year is displayed, but formatted as a number (1,957) rather than a year (1957). How can I get the number to be displayed without the comma? 2. Age, based on difference between birthday and present date - Is there a simple formula to accomplish this? I haven't been ab...

add data labels in pivotchart
Hi everyone, i want to add data labels in pivotchart (of course it is easy) but when i change some of indexes in the category fields or series fiels - all data labels disappear. And then i must add data labels manually again, it is very annoying. I have no idea what to do, probably i should ask someone who is experienced in VBA to write a code or something else...... IF you don't know how to do this, i will appreciated any kind of help, advices, whatever.... ...

removing Exchange 5.5 when the server has been gone for over a yea
I have taken over the administration of a Exchange 2003 server. The old 5.5 has been gone for over a year now. I was having some trouble with the public folders still waiting to rep. to the 5.5 server. I start to remove the rep. to the old 5.5 server and now I am getting alot more files trying to rep. over. Everything that had to do with the 5.5 server is grey out. How can I get ALL of this cleaned up and stop the 2003 server from trying to rep to the 5.5 server that is not there. Please help, this thing is eating my disk space alive. Hi, Are you trying to view the 5.5 server th...

Outlook From Field Disappears
The from field for the inbox disappeared. When I try to re-add it, Outlook crashes. Office XP Service Pack 3 on a Windows XP Pro workstation. Error Signature: AppName: outlook.exe AppVersion: 10.0.6626.0 ModName: outllib.dll ModVersion: 10.0.6515.0 Offset: 0028e199 I have uninstalled Office, ran regclean and reinstalled to no avail. I also tried replacing the dll from another install of Office. Any ideas? Please email me if possible. Thanks, Rob Coombe ...

Fields... what's going on
Hi Sorry, I expect this is really basic. I'm trying to write fields in Word 2000 with the IF command. The problem is that, suddenly, when I try to write them (CTRL+F9), I get the braces fine, but can only input the letter "I" - nothing else. 1 character, and it can only be a capital "i". Can someone tell me why this is, and how to stop it? It worked fine until 1/2 hour ago. *sob* Thanks in advance Steve This newsgroup is for discussions about Microsoft Access, a relational database program. A Word newsgroup would be a better choice for your question. "Cr...

Exporting data from a table to blogs
Hi, i have seen the sharepoint function offered by MS Excel. Would like to check with you whether this could be performed and exported into a blog? I've used sharepoint, but am not sure what you're talking about. Can you give some specific info on what you want to do? "owen.cxy" wrote: > Hi, i have seen the sharepoint function offered by MS Excel. Would like to > check with you whether this could be performed and exported into a blog? As in i have some data in MS Excel which i would like to export it and publish into a blog. Is it possible and is sharepoint ...

Getting external data problem
Hi In the External Data Range Properties, there are 3 options for 'If the number of rows in the data range changes upon refresh:' I want it to stay on option 3 - Overwrite existing cells with new data, clear unused cells. but it keeps defaulting to option2 - Insert entire rows for new data, clear unused cells. I am using Excel 2000 and my data is from Access 97. Any ideas?? Steve ...

Transfering Outlook data to a new computer
I just exported the data in my Outlook 2000 folders in an old computer and then imported it to Outlook 07 in my new computer. The newly imported data does not have the original date attached as to when the message was received. Is there a way to keep the receiving date attached to the message? Thanks Paul R Paul R <prenfree@hotmail.com> wrote: > I just exported the data in my Outlook 2000 folders in an old > computer and then imported it to Outlook 07 in my new computer. The > newly imported data does not have the original date attached as to > when the message was r...

Need help with changing multiple page fields. #2
I have found some code on the web that will allow me to link pivot table menus together. This way if I have three tables all based on the same info and say change the date in one, the date changes in all of them. I have tested out the code and it works well but... when I pull data from an outside source the code fails because it can not find the data fields. Is it possible to link related pivot table fields using an outside data source like SQL? The code I am using is listed below and this is the link from where I got the code from. It's a great site. Change Multiple Page Fields -- C...

I need a macro to find cut and paste data to new cell
I have data that I have exported to excel that I need to reformat to be able to create a pivot table to check for duplicate entries. On importing the data which is in the form of journal entries the memo line is stting above the journal numbers in column D. I need to find all of the comments and cut and paste them to column C. The comments are not all the same but do contain the same word "To" in the comment. The journals are not all the same size and so the comment line does not appear at regular intervals. Any help appreciated as I am very new to VBA ...

Copy and paste data from Auto Filter / Subtotal (or apply a formula to filtered data)
I have Excel 2003 at work and Excel 2000 on one of my computers at home. I am not sure how much this feature differs between the two programs, but I am defintitely experiencing this problem on my work computer: When I apply autofilter and then copy the data and try to paste it onto another worksheet, it pastes all the data, not just the visible data (i.e the data that is still visible when autofilter is applied). I have just tried this on my home computer (2000) and the results are a little more unpredictable. Sometimes it will paste only the visible data, but sometimes it pastes all the dat...

Prompt if data is not entered after so many times
Is it possible to highlight a cell if that data has not been entered after so many times? For instance, if I have a column where I have entries of A,B,C, & D and say B hasn't been entered for 7 times since the last entry of B, is there a way to highlight that last B then? Hopefully my question is clear enough to understand. Will you have contiguous data in column A (or C or D) beyond the last entry for column B? Pete On Mar 11, 11:26=A0pm, AceWill <AceW...@discussions.microsoft.com> wrote: > Is it possible to highlight a cell if that data has not been en...