Inserts a blank empty row at each change in column

[KrazyAl]

Hello

I am trying to create a macro that would enter an empty row after each group of data, in my query.
I was able to create it in Excel but Access is not cooperating.

I have 4 columns - Conversation ID, Date & Time, Participants, Conversations

I need to add a blank row after each group of numbers in Conversation ID (I.e. 20000,20000,2000, BLANK, 20001,20001,BLANK,25222,25222)

Any help would be appreciated!

 

[Joe4]

It is important to understand the differences between Excel and Access. Access is a relational database program. Relational database programs have table with fields and records in them. Every single record contains meaningful data. You wouldn't (or shouldn't) have "blank" records in an Access table.

Also, unlike Access, the order of the records in a table really has no meaning. Someone once gave a great example and said to think of a relational database table as "a bag or marbles", that are all mixed up, so the concept of order doesn't really exist. If you want to sort the records in a table, you usually do so in a query.

If you want to change how the data is presented to users, you usually do that with formatting in a Form or Report.

So, that brings us to your question. For what purpose do you want a blank row between grouping? If it is just presenting the information in a report, you can use Grouping and Group Footers to insert a blank space between groups.

If it is to export data to Excel, I think I would have an Excel macro that inserts a blank row afterwards. The only way you are going to get a blank row in a query and have it between each ID is to actually have some field with a value in it somewhere, and either have a blank row for every ID, or something like that.

 

[KrazyAl]

Hi

This query extracts the data and will exported to Excel. I wanted to have Access to spit out the data so I don't have an extra step in my process. This is a query not a table.

 

[Joe4]

A query simply returns data from an existing table, based on any criteria and sorting you want to apply.
If the blank rows do not already exist in the table, how do you anticipate being able to return something that does not exist?
You would need to create records in that table or another table in order to do that.

 

[KrazyAl]

Hi Joe

I can create a table or another query that adds the empty row between the groups. I can do this Excel using the results of the query that I copy and paste (or extract) and run the macro. It does what I want, after each group of data in the Conversation ID adds and empty two. I wanted to save an extra step and have Access do that, I figure if Excel can do it so can Access.

Here is the Excel Code:
Sub AddBlankRows()
'
Dim iRow As Integer, iCol As Integer
Dim oRng As Range

Set oRng = Range("a1")

iRow = oRng.Row
iCol = oRng.Column

Do
'
If Cells(iRow + 1, iCol) <> Cells(iRow, iCol) Then
Cells(iRow + 1, iCol).EntireRow.Insert shift:=xlDown
iRow = iRow + 2
Else
iRow = iRow + 1
End If
'
Loop While Not Cells(iRow, iCol).Text = ""
'
End Sub

Thanks for your assistance and feedback.

 

[Joe4]

I figure if Excel can do it so can Access

You need to understand that you are comparing apples and oranges here. Access is not "an advanced version of Excel". It is an entirely different beast altogether. It is a relational database program. Excel is a spreadsheet program. By their very nature, there are some things that each can do much easier than the other. So you really cannot apply the same logic thought process to both.

Can Access do what you want? Yes, but it won't be as easy as doing it in Excel.
Just like you could link up two tables in Excel with hundreds of VLOOKUP formulas, but Access does all that simply by linking the two tables together.

My advice would be this: Create an Excel macro to insert your rows, but have your Access process call and run the Excel macro if you all want to do it from Access. Then it is no extra steps for the person running it.

If you go a Search on "Run Excel Macro from Access", you should be able to find the code that you need.

 

[KrazyAl]

Ok, thanks I'll look into that.