Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 274
  • Last Modified:

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.

0
mtrussell
Asked:
mtrussell
1 Solution
 
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...
0
 
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?

~bp
0
 
gopaltaydeCommented:
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...
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

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

~bp
0
 
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

Else


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
dbs.TableDefs.Refresh
Application.RefreshDatabaseWindow


' 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;"

Else

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

End If


DoCmd.RunSQL copyfld
0
 
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?

~bp
0
 
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.  

0
 
Bill PrewCommented:
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.

~bp
0
 
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...
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now