Access 1 to Many, show the Many results in one record

[Asher]

By the way, do you really need to do this in a query? Couldn't you create a report that groups on the Report field?

That depends. In query form I can access the query as a data source from excel to create an excel table through MS Query. Can I do the same thing with a report?

[HansV]

Er, no. You can export reports to Excel, but the result is rubbish.

[Asher]

Too bad about the report, that would have made lots easier, however, the sorting argument worked spot on!!!

The main problem with why I have to do these overly complex things is people keep giving me jumbles of data that make sense to them but not in data management ways and I am expected to bandage them together to form meaningful tables that can then be used to create metrics.

I really appreciate your patience in helping me jump some of the larger hurdles they put before me.

[Asher]

I am a little confused. Now that I have the procedure working right, for some reason the query isn't showing up when I try to bring it in to Excel from MS Query, or even when I go right through Access in the Get External Data options... As this is the whole reason for making the query work, that is a major setback.

[Asher]

Correction,

in MS Query it lets me see the query, but when I try to add the fields I want I get the message: Undefined function 'DConcat' in expression.

[HansV]

You'll have to export the query to Excel from Access, you can't import it into Excel because it uses a custom VBA function. As far as I know, there is no way to make a query do what you want without VBA, so importing into Excel is not an option.

Alternatively, bring the raw data into Excel and process them there.

[Asher]

Just as an fyi, I found a workaround for the import from excel. I made a Make Table query from the query with the custom function and used the resulting table for my Excel data source. its one extra step but it gets the results they want with just a refresh (for them anyway).

[HansV]

Good idea!

[Asher]

Hans,

Your DConcat function is awesome! So Awesome, in fact, that I am now trying to implement it in a couple other dBases I'm working on. I have 2 questions though,

Question 1. In one of my DConcat columns, there ends up being duplicate entries, or multiple same entries (I have many TACs that are assigned a Group ID and one or many Test Request numbers. I am looking up the Group ID's assigned to each Test Request. There are often many, because each TAC has a Group ID that may be the same as another TAC Group ID and they also may have the same Test Requests.) My formula is this:
Group ID: DConcat("Group ID","qryTAC_ALL_Test_Request","[Test_Request]=" & [Test Request],"",Chr(13) & Chr(10))

So, if 3 different TACs have GroupID 1,GroupID 2, GroupID 2, respectively, and they are all assigned Test Request 1, it comes out like this:

Test Request	Group ID
1	GroupID 1 GroupID 2 GroupID 2

Notice the duplicate. That really messes up my formulas. Is there any way to avoid that?

Question 2: There seems to be a 255 character limit applied to these cells. Is there any way to get that increased? It truncates a LOT of necessary data...?

[HansV]

1. Here is a version that lists unique values. It doesn't have an OrderBy argument because that clashes with the requirement of uniqueness. The results are automatically sorted in ascending order.

Example of use:

Group ID: DConcatUnique("Group ID","qryTAC_ALL_Test_Request","[Test_Request]=" & [Test Request],Chr(13) & Chr(10))

Code: Select all

Function DConcatUnique _
  (FieldName As String, _
   TableName As String, _
   Optional WhereCondition As String, _
   Optional Delimiter As String) As String
  Dim dbs As DAO.Database
  Dim rst As DAO.Recordset
  Dim strSQL As String
  Dim strRes As String
  If WhereCondition <> "" Then
    strSQL = " AND (" & WhereCondition & ")"
  End If
  If Delimiter = "" Then
    Delimiter = ", "
  End If
  If strSQL <> "" Then
    strSQL = " WHERE" & Mid$(strSQL, 5)
  End If
  strSQL = "SELECT DISTINCT [" & FieldName & "] FROM [" & TableName & "]" & _
    strSQL
  Set dbs = CurrentDb
  Set rst = dbs.OpenRecordset(strSQL)
  Do While Not rst.EOF
    strRes = strRes & Delimiter & rst(FieldName)
    rst.MoveNext
  Loop
  If strRes <> "" Then
    strRes = Mid$(strRes, Len(Delimiter) + 1)
  End If
  DConcatUnique = strRes
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
End Function

2. DConcat itself doesn't truncate at 255 characters, Perhaps you applied a format to the text box?

x615.png

[Asher]

For some reason the truncation to 255 characters is occurring when I try to run make a table query on the query that has the DConcat formula (because if you remember from before, I can't import it from Excel with a user-made function). It doesn't seem like it should truncate because in the source table and the DConcat table the fields are memo...


BTW... the new formula for Unique values worked like a charm, WOOT!

[HansV]

In a make-table query, Access creates a text field instead of a memo field.
Workaround:

• Create a table with the correct field types, including a memo field. A simple way to do this is to take the table created by the make-table query and change the relevant text field to a memo field.
• Create a delete query that deletes all records from the new/modified table.
• Change the make-table query to an append query that adds records to the new/modified table.
• Each time you need to update the records, first run the delete query, then the append query.

The append query should concatenate the memo field correctly, without truncating it.

[Asher]

The workaround worked like a charm!
Thanks again Hans!