Excel code convert to Access code - Concat & eliminate duplicates

[italia]

I have a database with 2 columns and more than million rows. The first
column is the id

Example of the data (2 columns)-

04731 CRM
04731 CRM
04731 CRM
04731 RVB
04731 RVB
25475 MMX
25475 MMX
25475 FRB
25475 FRB

Result desired (2 columns)-

04731 CRM; RVB
25475 FRB; MMX

Idea is to summarize the data and eliminate the duplicates. The second
column should have the distinct fields separated by semicolon.

I am using the following Code in Excel it provides the desired result-

Sub testme()
Dim InputRng As Range
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long


Set wks = Worksheets("Sheet1")
With wks
Set InputRng = .Range("a1:b" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
InputRng.Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("b1"), order2:=xlAscending, _
header:=xlYes


InputRng.AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("D1"),
Unique:=True


.Range("a1:c1").EntireColumn.Delete


FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value
Then
'same value
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ", " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow


End With


End Sub


Now my data is in access with more than a million records. Can anybody
help me with doing the same thing in access?


Any help is greatly appreciated.


Thanks !!!

 

[Guest]

I'm not sure what the eventual use is of this concatenated field, but I would
suggest eliminating duplicates rather than concatenating values. This will
give you better searchability and summing capability in the long run. What
I'm suggesting is that your data should end up looking like this:

04731 CRM
04731 RVB
25475 MMX
25475 FRB

You can accomplish this with a Find Duplicates query and changing it's type
to a delete query.

Barry

 

[Dirk Goldgar]

I have a database with 2 columns and more than million rows. The first
column is the id

Example of the data (2 columns)-

04731 CRM
04731 CRM
04731 CRM
04731 RVB
04731 RVB
25475 MMX
25475 MMX
25475 FRB
25475 FRB

Result desired (2 columns)-

04731 CRM; RVB
25475 FRB; MMX

Idea is to summarize the data and eliminate the duplicates. The second
column should have the distinct fields separated by semicolon.

I am using the following Code in Excel it provides the desired result-

Sub testme()
Dim InputRng As Range
Dim wks As Worksheet
Dim iRow As Long
Dim FirstRow As Long
Dim LastRow As Long


Set wks = Worksheets("Sheet1")
With wks
Set InputRng = .Range("a1:b" & .Cells(.Rows.Count,
"A").End(xlUp).Row)
InputRng.Sort _
key1:=.Range("A1"), order1:=xlAscending, _
key2:=.Range("b1"), order2:=xlAscending, _
header:=xlYes


InputRng.AdvancedFilter _
Action:=xlFilterCopy, CopyToRange:=.Range("D1"),
Unique:=True


.Range("a1:c1").EntireColumn.Delete


FirstRow = 2 'headers in row 1
LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row


For iRow = LastRow To FirstRow + 1 Step -1
If .Cells(iRow, "A").Value = .Cells(iRow - 1, "A").Value
Then
'same value
.Cells(iRow - 1, "B").Value _
= .Cells(iRow - 1, "B").Value _
& ", " & .Cells(iRow, "B").Value
.Rows(iRow).Delete
End If
Next iRow


End With


End Sub


Now my data is in access with more than a million records. Can anybody
help me with doing the same thing in access?

You can do it with a couple of queries and the function fConcatChild()
that is posted here:

http://www.mvps.org/access/modules/mdl0004.htm

If you paste that function into a standard module, you can use it in
conjunction with queries to de-normalize the data.

Do you have a table of distinct IDs? I'll assume you don't, so you need
a query like this:

Query: qIDs
SQL:
SELECT DISTINCT ID
FROM SourceTable;

(I'll assume for these examples that your original table is named
"SourceTable".)

Now you need a query that returns only the distinct ID/Value
combinations from the source table:

Query: qValues
SQL:
SELECT DISTINCT ID, IDValue
FROM SourceTable;

With these queries defined and saved, you can get the final results
with:

SELECT
qIDs.ID,
fConcatChild("qValues","ID","IDValue","Long",[ID])
AS ValueList
FROM qIDs;

You could turn that into a make-table query, if you want.