Delete blank fields in a table

Posted on 2011-09-02
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 56

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 56

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...
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!


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 56

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 56

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 56

Accepted Solution

Bill Prew earned 500 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

[Webinar] Learn How Hackers Steal Your Credentials

Do You Know How Hackers Steal Your Credentials? Join us and Skyport Systems to learn how hackers steal your credentials and why Active Directory must be secure to stop them. Thursday, July 13, 2017 10:00 A.M. PDT

Question has a verified solution.

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

Welcome, welcome!  If you are new to the series and haven't been following along, please take a brief moment to review the first three installments: Part 1 (…
Introduction During my participation as a VBScript contributor at Experts Exchange, one of the most common questions I come across is this: "I have a script that runs against only one computer. How can I make it run against a list of computers in …
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
Monitoring a network: how to monitor network services and why? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the philosophy behind service monitoring and why a handshake validation is critical in network monitoring. Software utilized …

623 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