Summary of field contents

[Hardworker]

I'm not sure if this is possible but here we go:
I have a table listing the products we have for sale, each product falls
into a particular category - G1, G2... etc. up to G15. So the fields in the
table could be: Category; Description; Part Number; etc.
If a number of products from different categories are sold on a single
order, is it possible to produce a summary/report where the different
categories on that order are listed in a single text string?
E.g. four products are sold: two are from category G1, one from G4 and one
from G12 so what I want is 'G1, G4, G12'

 

[Pieter Wijnen]

Yes

This Function (Or simillar) Should do it:

Public Function OrderCategories(ByVal OrderID As Long) As String
Dim Db As DAO.Database
Dim Rs As DAO.Recordset
Dim thQ as String
Dim Res As String

Set Db = Access.CurrentDb()
thQ = "SELECT DISTINCT CATEGORY FROM ORDERLINE " & _
"WHERE ORDERID=" & OrderID
Set Rs = Db.OpenRecordset(thQ, DAO.dbOpensnapshot)
While Not Rs.EOF
Res = Res & "," & Rs.fields(0).Value
Rs.Movenext
Wend
Rs.Close : Set Rs = Nothing
Set Db = Nothing
OrderCategories = VBA.Mid(Res,2) ' Extra Comma
End Function

Hth

Pieter

 

[Larry Daugherty]

Yes, play with the Sorting and Grouping features to get what you want.
In this case it looks like you'd Group on "Category" sorted Ascending.

HTH