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

[Asher]

Hi all,

I'm querying 2 tables using an outer join.
Each table has multiple columns using external data sources in linked tables.
The relationship is 1 to Many so my tables and results look kinda like this (uber simplified):

Request Table:

Req#	Owner	Area	Machine
5	Smith	bs	rga6
89	Jones	sub	rgb5

Plan Table:

Series	Req#	Plan
Rock1	5	rk_45
Rock2	5	rk_56
Paper5	89	ppr_19
Scissors4		scs_36

Results = Req & Plan:

Req#	Plan
5	rk_45
5	rk_56
89	ppr_19

What I want it to look like is this:

Req#	Plan
5	rk_45 rk_56
89	ppr_19

Any suggestions?

[HansV]

Years ago, in another board, I posted a Concat function; unfortunately much of the code in posts there became mangled during migrations to other software. Here is a slightly modified version:

Code: Select all

Function DConcat _
  (FieldName As String, _
   TableName As String, _
   Optional WhereCondition As String, _
   Optional OrderBy 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 OrderBy = "" Then
    OrderBy = FieldName
  End If
  If Delimiter = "" Then
    Delimiter = ", "
  End If
  If strSQL <> "" Then
    strSQL = " WHERE" & Mid$(strSQL, 5)
  End If
  strSQL = "SELECT [" & FieldName & "] FROM [" & TableName & "]" & _
    strSQL & " ORDER BY [" & OrderBy & "];"
  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
  DConcat = strRes
  rst.Close
  Set rst = Nothing
  Set dbs = Nothing
End Function

You can use it in a calculated column in a Totals query that groups by Req#:

Plans: DConcat("Plan", "Results", "[Req#]=" & [Req#], , Chr(13) & Chr(10))

Note: you need to be able to use DAO.Database and DAO.Recordset in your code.

[Asher]

This is the actual calculated column expression I'm trying to use: CO_Conc: DConcat("Change Order", "Results", "[Report]=" & [Report], Chr(13) & Chr(10))

It relates to the example like this:

Req# = Report
Plan = Change Order

I did a Totals Query, "grouped by" shows under every column. I left the actual "Change Order" column out of the field list and instead put that expression in.

I keep getting an error that says:

Run-Time error '3078':
The Microsoft Office Access database engine cannot find the input table or query "Results". Make sure it exists and that its name is spelled correctly.

[HansV]

Do you have a table or select query named Results?

[Asher]

Doh, I didn't register that was the table name of example I gave. Ok so now I updated the table name (Results = ALL_Open) and it looks like this :

CO_Conc: DConcat("Change Order", "ALL_Open", "[Report]=" & [Report], Chr(13) & Chr(10))

Now I am getting this error:

Run-Time error 3061: Too few parameters expected 1

On this line:

Set rst = dbs.OpenRecordset(strSQL)

I feel like there is some small thing I am doing wrong (or maybe a large one)... ???

[HansV]

Is Change Order the name of a field in the ALL_Open table?
Is Report the name of a field in the ALL_Open table?

[Asher]

Post Posted: 09 May 2011, 14:10
Is Change Order the name of a field in the ALL_Open table?
Is Report the name of a field in the ALL_Open table?

Yes and yes.

ALL_Open is the query results (the one I'm trying to use the function on) from the joining of the 2 tables Report Export and Change Order, like this:

Table 1 = Report Export:

Report	Project	Status	Date Created	...
101	System Operation	Open	6/11/99	...
156	System Functionality	Closed	5/28/01	...
195	System Functionality	Open	9/8/95	...
231	System Operation	Open	6/4/91	...

Table 2 = Change Order:

Change Order	Report	...
92 63 17	101	...
62 12 23 46	156	...
97 16	195	...
53 20 13	231	...

This is what I get from my query:
Query = ALL_Open:

Report	Change Order	Status	Date Created
101	92	Open	6/11/99
101	63	Open	6/11/99
101	17	Open	6/11/99
195	97	Open	9/8/95
195	16	Open	9/8/95
231	53	Open	6/4/91
231	20	Open	6/4/91
231	13	Open	6/4/91

This is what I want to get from my query ALL_Open: [/tr]

Report	Change Order	Status	Date Created
101	92 63 17	Open	6/11/99
195	97 16	Open	9/8/95
231	53 20 13	Open	6/4/91

To try to implement the function in ALL_Open I removed the Change Order field from the query and instead added the CO_Conc: field with the formula: CO_Conc: DConcat("Change Order", "ALL_Open", "[Report]=" & [Report], Chr(13) & Chr(10))

[HansV]

What happens if you use

CO_Conc: DConcat("Change Order", "Change Order", "[Report]=" & [Report], Chr(13) & Chr(10))

[Asher]

It says its expecting 1 parameter and I have too few.
Can you explain your syntax, it's hard to tell cause my table name is the same as my field name. Is "Change Order","Change Order" relative to "Field Name","Table Name"?
So if , for clarification, I changed the Change Order table name to Change Order Export, and I changed the "Report" field in Change Order Export to CO_Report the formula would read:

CO_Conc: DConcat("Change Order", "Change Order Export", "[CO_Report]=" & [Report], Chr(13) & Chr(10)) ???

That's what I did because I was confusing myself with the similar names and it came up with the need more parameters, expecting 1 error.

[HansV]

In DConcat("Change Order", "Change Order Export", "[CO_Report]=" & [Report], Chr(13) & Chr(10)):
"Change Order" is the name of the field whose values you want to look up.
"Change Order Export" is the name of the table in which you want to look up the values.
"CO_Report" is the name of the field in the table that you want to compare to a field from the "calling" query.
"Report" is the name of the field in the "calling" query on which the records are grouped.

[Asher]

I did a debug.print on str.SQL using this formula: Formula: CO_Conc: DConcat("Change Order","Change Order Export","[CO_Report]=" & [Report],Chr(13) & Chr(10)), when it stops (always at line: Set rst = dbs.OpenRecordset(strSQL)), it comes out like this:

SELECT [Change Order] FROM [Change Order Export] WHERE ([CO_Report]=57370) ORDER BY [
];

Is there something missing in the syntax for the order by to not show up?

[HansV]

Sorry, I should have seen that. You have omitted a comma (cf. my original example):

CO_Conc: DConcat("Change Order","Change Order Export","[CO_Report]=" & [Report],,Chr(13) & Chr(10))

[Asher]

When I had that comma in it tells me its an invalid syntax, I "may have entered a comma without a preceding value or identifier"

[HansV]

Aargh! I was testing in the Visual Basic Editor, not in a query. This should work in a query...

CO_Conc: DConcat("Change Order","Change Order Export","[CO_Report]=" & [Report],"",Chr(13) & Chr(10))

[Asher]

ok, so with this formula: CO_Conc: DConcat("Change Order","Change Order Export","[CO_Report]=" & [Report],"",Chr(13) & Chr(10))
I get this from debug.Print:
SELECT [Change Order] FROM [Change Order Export] WHERE ([CO_Report]=57370) ORDER BY [Change Order];

and This error when I run it:

Data Type Mismatch in criteria expression.

All the fields that are being read into this are text though, they are all the same...

[HansV]

So CO_Report is a text field, despite the numeric-looking contents 57370? If so, change to

CO_Conc: DConcat("Change Order","Change Order Export","[CO_Report]=" & Chr(34) & [Report] & Chr(34),"",Chr(13) & Chr(10))

[Asher]

HANS YOU ARE A GENIUS!!!!!!!!! It worked with that last one. I think Access is reading those numbers in as text because they are connected by a linked table and the numbers themselves have a hyperlink associated with them.

Thanks so much, I have a number of other (somewhat similar) databases I can make use of this function on. I really appreciate your help.


-Asher

[Asher]

Oh ugh I just realized something! It totally works. It's awesome. But there are 4 other fields associated with the original CO_Conc field. I thought I could just apply the formula to those fields as well, so I did, but when they all calculate into one record, the field values don't line up anymore, the are no longer in the right order.

Example:

if I am concatenating the Change Order numbers:
CO_Conc:
15
63
25

the next fields for them are CProd, CProdRev, CProdTtl,Status

and each Change Order number lines up with it's respective values in the other fields.

But when I concatenate the other fields as well, the values order gets mixed.

Is there any way to fix that? If they don't line up it becomes an inaccurate record. Is there possibly a way to conc all five fields at once so they are ordered correctly instead of individually?

[Asher]

I think it doesn't line up because Access looks like it automatically doing an A-Z sort during calculation.

[HansV]

That's what the extra argument that you're currently setting to "" is for: you can specify a field in the Change Order Export table on which you want to sort:

CO_Conc: DConcat("Change Order","Change Order Export","[CO_Report]=" & Chr(34) & [Report] & Chr(34),"FieldToSortOn",Chr(13) & Chr(10))

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?