Delete blank fields in a table

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.

Who is Participating?
Bill PrewConnect With a Mentor Commented:
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.

HainKurtSr. System AnalystCommented:
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...
Bill PrewCommented:
This seems like it would be related to the join logic you are using to access the tables, what does that look like?

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

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...
mtrussellAuthor Commented:
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.
Bill PrewCommented:
Still need to see the Access query that returns the data...

mtrussellAuthor Commented:
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
Bill PrewCommented:
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?

mtrussellAuthor Commented:
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.  

mtrussellAuthor Commented:
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...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.