data in queries

[Robert]

Hi!

From a dataset I retrieve customer names and the products they have
purchased:

client name product name
client 1 product a
client 1 product b
client 1 product c
client 2 product b
client 2 product d
client 3 product a
client 3 product e

No problem. But how can I avoid the repetitions in the ClientName column:

client name product name
client 1 product a
product b
product c
client 2 product b
product d
client 3 product a
product e

In Query View. I think in a Report this is done through Group By?

Thanks - Robert

 

[Jason Lepack]

It's not worthwhile to handle this at the query level. If you need
this to export to excel then export a report like you described above.

Cheers,
Jason Lepack

 

[Guest]

You can't. A query is simply a different representation of the tables.

 

[Jamie Collins]

From a dataset I retrieve customer names and the products they have
purchased:

client name product name
client 1 product a
client 1 product b
client 1 product c
client 2 product b
client 2 product d
client 3 product a
client 3 product e

No problem. But how can I avoid the repetitions in the ClientName column:

client name product name
client 1 product a
product b
product c
client 2 product b
product d
client 3 product a
product e

Perhaps you'd find a hierarchical recordset approach preferable.
Here's an example using Northwind:

SHAPE
{
SELECT DISTINCT C1.CustomerID
FROM Customers AS C1
INNER JOIN Orders AS O1
ON C1.CustomerID = O1.CustomerID
}
APPEND
(
{
SELECT DISTINCT C1.CustomerID, D1.ProductID
FROM (Customers AS C1
INNER JOIN Orders AS O1
ON C1.CustomerID = O1.CustomerID)
INNER JOIN [ORDER Details] AS D1
ON O1.OrderID = D1.OrderID
}
AS chapProducts RELATE CustomerID TO CustomerID
)

And here's it is as VBA (you path to Northwind may be different):

Sub HierPurchases()
Dim rs
Set rs = CreateObject("ADODB.Recordset")
With rs
..ActiveConnection = _
"Provider=MSDataShape;" & _
"Data Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Program Files\Microsoft Visual Studio\VB98\NWIND.mdb"
..Source = _
"SHAPE " & vbCr & "{" & vbCr & " SELECT DISTINCT C1.CustomerID" &
vbCr & "" & _
" FROM Customers AS C1" & vbCr & " INNER" & _
" JOIN Orders AS O1" & vbCr & " ON C1.CustomerID" & _
" = O1.CustomerID" & vbCr & "}" & vbCr & "APPEND" & vbCr & "(" & vbCr
& " " & _
" {" & vbCr & " SELECT DISTINCT C1.CustomerID," & _
" D1.ProductID" & vbCr & " FROM (Customers" & _
" AS C1" & vbCr & " INNER JOIN Orders" & _
" AS O1" & vbCr & " ON C1.CustomerID" & _
" = O1.CustomerID)" & vbCr & " INNER" & _
" JOIN [Order Details] AS D1" & vbCr & " " & _
" ON O1.OrderID = D1.OrderID" & vbCr & "" & _
" } " & vbCr & " AS chapProducts RELATE" & _
" CustomerID TO CustomerID" & vbCr & ")"

..LockType = 4 ' adLockBatchOptimistic
..Open

MsgBox .GetString
..MoveFirst

Do While Not .EOF
MsgBox .Fields(1).value.GetString
..MoveNext
Loop

End With
End Sub

I don't think you can do this in a Query window in the Access user
interface because you need to use the MSDataShape provider.

Jamie.

--