Problem querying for unique entries

[npearson713]

Hello everyone! I've been working on this database for quite some time now, and I've got a pretty in depth question that I haven't been able to answer.

Some background: The company I work for plants trees for funeral homes. A single funeral home can plant 10 trees for 10 different people. 3 of these people might have belonged to the same organization that has requested a notification of the tree planting. However, instead of sending 3 letters to this organization, we send out 1 letter w/ 3 names on it.

My problem is that I can't figure out how to get those 3 names on 1 letter. Currently, I have a field in the data tables that will have the combined information of street address, city, and state. I'll call it "AddressCityState". I need something that will query all of the AddressCityState entries and find the ones that repeat, then compile the names associated with the repeat so that one letter will be printed with those 3 names.

I can't really tell how clear this is, but I'm certainly able to elaborate on it should someone think they can help me. Thanks in advance!
Nate

 

[lespaul]

sounds like you want to combine the "many", check out FAQ701-3499 on concatenating.


Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for anyone working with databases:
The Fundamentals of Relational Database Design
Understanding SQL Joi

 

[npearson713]

Thanks a lot for the point in the right direction, however I'm now getting the runtime error '13' Type Mismatch error. Maybe it's just early, but I can't figure out why it's happening. When I debug, the highlighted line starts with Set rs = db.OpenRecordset. The key field is LOA_Shipping_addcity, and it's a text field. Here's my adapted code:

Private Sub Command0_Click()
Dim db As Database
Dim rs As Recordset
Dim rs1 As Recordset
Dim vCode5 As String ' string to contain list of stores

Set db = CurrentDb

'clear out TempTable
db.Execute "DELETE tmpLOACode5.* FROM tmpLOACode5;"

'first of all insert list of projects into table TempTable
db.Execute "INSERT into tmpLOACode5 (LOA_Shipping_addcity) SELECT DISTINCT LOA_Shipping_addcity FROM LOA_Orders"

Set rs = db.OpenRecordset("SELECT DISTINCT LOA_Shipping_addcity FROM tmpLOACode5")
    'outer loop containing list of project numbers

Do Until rs.EOF = True
    'for each project number create a string of stores
    vCode5 = ""

Dim sql As String
    sql = "SELECT Order_HonoredPerson FROM LOA_Orders WHERE LOA_Shipping_addcity ='" & rs!LOA_Shipping_addcity & "'"
    'select list of stores for current project number
    
    Set rs1 = db.OpenRecordset(sql)
    'inner loop to create a concatenated string of stores

    Do While rs1.EOF = False
        vCode5 = vCode5 + ", " + rs1!Order_HonoredPerson
        rs1.MoveNext
    Loop
    
    If Len(vCode5) > 0 Then
        'trim off leading comma
        vCode5 = Right(vCode5, Len(vCode5) - 1)
        
        'update project list with store string
        db.Execute ("UPDATE tmpLOACode5 SET Order_HonoredPerson = '" + vCode5 + "' WHERE LOA_Shipping_addcity = '" & rs!LOA_Shipping_addcity & "'")

        Else
        'dont bother with update
    End If
       'move to next project number

    rs.MoveNext
    Loop

    'release the memory
    Set rs = Nothing
    Set rs1 = Nothing
    Set db = Nothing

End Sub

Thanks again for your help!
Nate

 

[npearson713]

Alright, figured it out. Instead of defining rs and rs1 as recordsets, I just defined them as Variants. The code now works great, but I've got a minor formatting question. I'm gonna start a new thread for that because it belongs in a different forum!

Thanks again,
Nate