can this be grouped?

  • Thread starter Thread starter Emilio
  • Start date Start date
E

Emilio

Hi all,

I need to have this result:

OrderID Customer Items
3456 Jones Windows, Doors
(where the OrderID appears only once)
-------------------------------------------
Right now I have:

OrderID Customer Items
3456 Jones Windows
3456 Jones Doors

I am a newbie and can't figure this out.
Any help greatly appreciated.

Thanks,
Emilio





???????

Gracias
 
Hi Duane, thanks for youe help.

I am using the aFunction with DAO and followed the lines
instructions:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
-----------------------------------------
When I run my query I get:
Run-time Error '3061':
Too few parameters. Expected 1.

When I debug it highlights the following:

"Set rs = db.OpenRecordset(pstrSQL)"

I am using Access 2003

Any ideas.

Thanks again,
Emilio
 
You must provide your expression that you are sending to the function as
well as some data types of significant fields.
 
Is it because the table name is the same as field name?

AllItems: Concatenate("SELECT Items FROM Items WHERE
OrderID =" & [Items.OrderID] & " ORDER BY
[Items.OrderID]")
 
You didn't provide the field types so I can assume OrderID is numeric:
AllItems: Concatenate("SELECT Items FROM Items WHERE OrderID =" &
[Items].[OrderID] & " ORDER BY [OrderID]")
Your []s were off. Also, your "ORDER BY [OrderID] shouldn't do anything
since the returned results contains Items from only one OrderID.

I have fairly strict naming conventions which would never allow any two
objects in a database to have the same name. Check
http://www.granite.ab.ca/access/tablefieldnaming.htm.

--
Duane Hookom
MS Access MVP


Emilio said:
Is it because the table name is the same as field name?

AllItems: Concatenate("SELECT Items FROM Items WHERE
OrderID =" & [Items.OrderID] & " ORDER BY
[Items.OrderID]")



-----Original Message-----
You must provide your expression that you are sending to the function as
well as some data types of significant fields.

--
Duane Hookom
MS Access MVP





.
Click to expand...
Click to expand...
 
Thanks again Duane, but it won't work for me.
I am going to start a new post to see if there is a
simpler solution, yours is great but for a larger scope.
Mine is a one time need.

You have lots of patience,
Thanks again,
Emilio


-----Original Message-----
You didn't provide the field types so I can assume OrderID is numeric:
AllItems: Concatenate("SELECT Items FROM Items WHERE OrderID =" &
[Items].[OrderID] & " ORDER BY [OrderID]")
Your []s were off. Also, your "ORDER BY [OrderID] shouldn't do anything
since the returned results contains Items from only one OrderID.

I have fairly strict naming conventions which would never allow any two
objects in a database to have the same name. Check
http://www.granite.ab.ca/access/tablefieldnaming.htm.

--
Duane Hookom
MS Access MVP


Is it because the table name is the same as field name?

AllItems: Concatenate("SELECT Items FROM Items WHERE
OrderID =" & [Items.OrderID] & " ORDER BY
[Items.OrderID]")



-----Original Message-----
You must provide your expression that you are sending
Click to expand...
to
the function as
well as some data types of significant fields.

--
Duane Hookom
MS Access MVP


"Emilio" <[email protected]> wrote
Click to expand...
in
message
Hi Duane, thanks for youe help.

I am using the aFunction with DAO and followed the lines
instructions:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(pstrSQL)

'======For ADO uncomment next two lines=====
'====== comment out DAO above ======
'Dim rs As New ADODB.Recordset
'rs.Open pstrSQL, CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic
Dim strConcat As String 'build return string
With rs
If Not .EOF Then
.MoveFirst
Do While Not .EOF
strConcat = strConcat & _
.Fields(0) & pstrDelim
.MoveNext
Loop
End If
.Close
End With
Set rs = Nothing
'====== uncomment next line for DAO ========
Set db = Nothing
If Len(strConcat) > 0 Then
strConcat = Left(strConcat, _
Len(strConcat) - Len(pstrDelim))
End If
Concatenate = strConcat
End Function
-----------------------------------------
When I run my query I get:
Run-time Error '3061':
Too few parameters. Expected 1.

When I debug it highlights the following:

"Set rs = db.OpenRecordset(pstrSQL)"

I am using Access 2003

Any ideas.

Thanks again,
Emilio



-----Original Message-----
There is code and samples of this in the generic
concatenate function at
Click to expand...
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hoo
kom,Duane

--
Duane Hookom
MS Access MVP


"Emilio" <[email protected]>
Click to expand...
wrote
in
message
Hi all,

I need to have this result:

OrderID Customer Items
3456 Jones Windows, Doors
(where the OrderID appears only once)
-------------------------------------------
Right now I have:

OrderID Customer Items
3456 Jones Windows
3456 Jones Doors

I am a newbie and can't figure this out.
Any help greatly appreciated.

Thanks,
Emilio





???????

Gracias




.



.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top