Select distinct, access to excel

[dacodac]

Hi,

I would like to put into an array a column from an access table.
I have a lot of redundant data, is someome could help me to put my data into an array?
Then I will filter the data by my own.
I've found some code but didn't work :/



Many thanks

[rpeare]

if you're looking for duplicates there is a FIND DUPLICATES wizard for making new queries.

[dacodac]

Hi,
thanks for thanks, but I should be able to send this to an excel workbook then, you will tell me that it's not a problem.

But how can i put into an array one column? I think that is possible, but I can't code it everything I did, didn't work.

I was thinking to do that first and then into my array look every different value (which should create one column into a excel workbook)

Do you have any advice?

Many thanks

[June7]

Why do you need to use code and array just to eliminate duplicates? This should be possible with query.

SELECT DISTINCT fieldname FROM tablename;

or

SELECT fieldname FROM tablename GROUP BY fieldname;

Related/duplicate thread https://www.accessforums.net/program...cel-31922.html

[dacodac]

Wouhou, here we go, I did it, thanks to you.
This is my code :

Sub Travaux()

Dim Rs As ADODB.Recordset
Dim sSQL As String
Dim VCategorie As String
sSQL = "SELECT DISTINCT Categorie FROM Table1"
Set Rs = New ADODB.Recordset
Rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
Rs.MoveFirst
Do Until Rs.EOF
VCategorie = Rs("Categorie")

Debug.Print VCategorie

Rs.MoveNext
Loop
Rs.Close
End Sub


Now I have to create new column into my excel workbook, from the different categorie!
I will have to do it in my loop, what do you think?

Many thanks

[dacodac]

Hi, I'm still on it, but close to the end.
I was thinking to do something like that, could you help me please?

Many thanks

Sub WorkC()

Dim Rs As ADODB.Recordset
Dim sSQL As String
Dim VCategorie As String
Const cStartRow As Byte = 5
Const cStartColumn As Byte = 5
iCol = cStartColumn
iRow = cStartRow
sSQL = "SELECT DISTINCT Categorie FROM Table1"
Set Rs = New ADODB.Recordset
Rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Set appexcel = appexcel.Workbooks.Open("C:\target workbook")
appexcel.Visible = True
appexcel.Sheets("Report").Select

Rs.MoveFirst
Do Until Rs.EOF
VCategorie = Rs("Categorie")
Debug.Print VCategorie

'Check if the value already existed (compare value from range from E5 to O5)
'If iFld is not exited then add value to the next empty cell of the range E5:O5

'appexcel.Cells(iRow, iCol) = VCategorie
'iCol = iCol + 1
'Else do nothing

Rs.MoveNext
Loop
Rs.Close
End Sub

[dacodac]

Done, I share it.
Can be improve, but it works!
Thanks June7
How it looks like?

Sub WorkC()

Dim Rs As ADODB.Recordset
Dim sSQL As String
Dim VCategorie As String
Dim celluletrouvee As Range
Const cStartRow As Byte = 5
Const cStartColumn As Byte = 5
iCol = cStartColumn
iRow = cStartRow
sSQL = "SELECT DISTINCT Categorie FROM Table1"
Set Rs = New ADODB.Recordset
Rs.Open sSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

Set appexcel = CreateObject("Excel.Application")
appexcel.Visible = True
Set wbexcel = appexcel.Workbooks.Open("Path workbook")
appexcel.Sheets("Sheet1").Select

Rs.MoveFirst
Do Until Rs.EOF
VCategorie = Rs("Categorie")
Debug.Print VCategorie

Set celluletrouvee = Range("E5:O5").Find(VCategorie, lookat:=xlWhole)

'Check if the value already existed
If celluletrouvee Is Nothing Then

appexcel.Cells(iRow, iCol) = VCategorie

Else
End If
Rs.MoveNext
iCol = iCol + 1
Loop
Rs.Close
End Sub

[June7]

As long as it works, looks good. Let us know if you have specific issue, otherwise call it solved.

[dacodac]

Hi, I would like a last device!
I would like to konw the best way to export row from table (TransferSpreadsheet ?) into a workbook and placed them.
I had attached one excel example, from access how to export two columns (field Town and company) and put one column (field work) below the correct Categorie (from my previous code)
I was trying with query, but it's doesn't look the best way to go!
(once finish I will share it, is it possible to change the name of the thread?)
Many thanks in advance
Regards

[June7]

Need to know more about your data structure. Sounds like you need to transpose data with a CROSSTAB query. Provide sample of source data and what the final output should look like. If you want to attach database, follow instructions at bottom of my post.

What do you want to name the thread?

[dacodac]

Ok thank you i'm going to check crosstab query!
I attached an example of structure that i'm looking for :
I would like to change the name to help other people, I don't know something more efficient with keywords, like "Select distinct, access to excel"
Thanks

[dacodac]

With attachement is better Structure-Example.zip

Regards

[dacodac]

Thanks, to rename it
CrossTab looks complicate!
I would like to modifie something, in my access table and field Categorie it will be always by order A, B, C ...
Then in my workbook, I will add the field "Town" and "Company" from access always into column A6 and B6 (workbook) and my "work" always into categorie A (column E5) , then categorie B (column F5) etc ...
I think it would be easier, do you?
Regards

[June7]

CROSSTAB not so tough. This will result in structure that looks like the Excel:

TRANSFORM First(Table1.[Work]) AS FirstOfWork
SELECT Table1.[company], Table1.[town]
FROM Table1
GROUP BY Table1.[company], Table1.[town]
ORDER BY Table1.[company], Table1.[town]
PIVOT Table1.[categorie];

Copy/paste that into the SQL VIEW of query designer. Then look at the query design in Design View. Practice recreating that sql with the CROSSTAB design wizard.

So do you still need to export to Excel?

[dacodac]

Hi, and sorry I haven't had time to reply before!
Woah awesome! Thanks for this new thing
And yes I will try to export it to excel (i use it like a report), but thanks to that it would be easier to export my data to excel!
I don't have to be worry about to place it, I just have to add some columns in my excel file between the 2 first one and the message.
Thanks again