Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Delete blank fields in a table

Posted on 2011-09-02
Medium Priority
Last Modified: 2012-05-12
I have data in tables that I want to push out to Excel.  I am sure there could be a better way of doing this so if there is, I am open to suggestions.  Saying this, what I have so far is the following:

TABLE 1                                             TABLE 2                                TABLE 3
Column A  Column B                           Column C                             Column D Column E
Record 1    Record1                            Record 3                              Record 6  Record 6  
Record 2    Record 2                           Record 4
                                                           Record 5  

I have a form where the end user  can select which records from which tables they want to export to Excel and I build a temporary table.  So, if the end user choose Table 1 and Table 3

The table would create

Column A                         Column B                 Column D               Column E

All this is fine until Access populates the table it does

Column A                         Column B                 Column D               Column E
Record 1                          Record 2                  Null                         Null
Record 2                          Record 2                  Null                         Null
Null                                  Null                          Record 6                 Record 6

What I want is

Column A                         Column B                 Column D               Column E
Record 1                          Record 1                  Record 6                Record 6
Record 2                          Record 2                  Null                         Null

How would I do this.  I want the records to be column header and data on the Excel worksheet.

Any ideas would be appreciated.

Question by:mtrussell
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 59

Expert Comment

ID: 36474188
what query are you using to get first result? and what is the logic of the result are you looking for? I could not see the logic...
LVL 57

Expert Comment

by:Bill Prew
ID: 36474317
This seems like it would be related to the join logic you are using to access the tables, what does that look like?


Expert Comment

ID: 36475281
Just break your logic in 2 steps -
Step1: Create the table with the selected columns (Which I believe you already did)
Step2: Now, get the data from original Table1 & Table3 in lets say dataset and then using loop populate the data into the newly created table. Here when the loop counter is 1, you will populate the 1st row of Table1 & Table3. And so  on...
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 36510211
sorry for the delay in responding.

gopaltayde - the logic is broken into the two steps.  the issue is when the table is populated it is adding records from each table (which is logical) what I want is to delete the null values from the records so the columns of data line up properly in Excel.
LVL 57

Expert Comment

by:Bill Prew
ID: 36510557
Still need to see the Access query that returns the data...


Author Comment

ID: 36510768
here you go:

Dim dbs As DAO.Database
Dim tdf As DAO.TableDef
Dim fldName As DAO.Field
Dim fldDate As DAO.Field
Dim xVariable As Long
Dim a As Long

DoCmd.DeleteObject acTable, "tbl_variableexport"

' Step 1 is to name the fields in the table.
Set dbs = CurrentDb
Set tdf = dbs.CreateTableDef("tbl_VariableExport")

xVariable = (DLookup("[A]", "tbl_variablesbytransaction"))

If IsNull(xVariable) Then GoTo thebottomofallthis

If DLookup("[variabletype]", "tbl_variablenames", "[id] =" & xVariableA) = "Single Entry" Then

a = 1

Set fldNameA = tdf.CreateField(DLookup("[variablename]", "tbl_variablenames", "[id] =" & (DLookup("[A]", "tbl_variablesbytransaction"))), dbText, 25)
tdf.Fields.Append fldNameA


Set fldName = tdf.CreateField((DLookup("[variablename]", "tbl_variablenames", "[id] =" & xVariable) & " Rate"), dbText, 25)
Set fldDate = tdf.CreateField((DLookup("[variablename]", "tbl_variablenames", "[id] =" & xVariable) & " Date"), dbText, 25)

tdf.Fields.Append fldName
tdf.Fields.Append fldDate

End If

dbs.TableDefs.Append tdf

' the second step is to fill in the data of the fields

If a = 1 Then

copyfld = "INSERT INTO tbl_VariableExport ( [" & fldName.Name & "] ) SELECT Tbl_Variables_SingleEntry.VariableValue FROM Tbl_VariablesByTransaction INNER JOIN Tbl_Variables_SingleEntry ON Tbl_VariablesByTransaction.A = Tbl_Variables_SingleEntry.Variable_ID;"


copyfld = "INSERT INTO tbl_VariableExport ( [" & fldName.Name & "], [" & fldDate.Name & "] ) SELECT Tbl_PercentageRates1.Rate, Tbl_PercentageRates1.EffectiveDate FROM Tbl_PercentageRates1;"

End If

DoCmd.RunSQL copyfld
LVL 57

Expert Comment

by:Bill Prew
ID: 36510961
Okay, the results you are getting make sense based on this.  Since you insert the columns from table 1 first, those records are added to the result set.  Then you process the next table, and insert those as additional rows below the Table 1 rows.

What you really need to do is a single insert which is a JOIN of the tables involved, but I don't see any easy way to do that in this case either, given the dynamic selection of the tales and fields, etc.

Might it make sense that after you load the data into Excel, to just delete the cells with null values?


Author Comment

ID: 36513503
I might have to go this direction but I would prefer to stage the data in Access so I can push the data through more methodically which is easier to test.

I'll continue to think about it and see if there is another option; otherwise I 'll go this direction.  

LVL 57

Accepted Solution

Bill Prew earned 1500 total points
ID: 36514866
Conceptually what you sort of want to do is an outer join based on the row number of the results from each of the tables that you are trying to merge together.  But I'm not sure there is anyway to do that.


Author Closing Comment

ID: 36566039
Thanks for your help on this.  I am going to have to go another route on this one.  Looks like I hit a dead-end on this one...

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently I finished a vbscript that I thought I'd share.  It uses a text file with a list of server names to loop through and get various status reports, then writes them all into an Excel file.  Originally it was put together for our Altiris server…
This is pretty cool.  The purpose of this VB Script is to help you document where JAR (Java ARchive) files and specifically java class files are located so that you can address issues seen with a client or that you can speak intelligently with a dev…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question