Concatenation Frustration!

[ocular]

Hey everyone,
Well i've searched, and have been trying to use 'fConcatenateFldGroupBy' in a query in order to perform the following combining of data with a criteria requirement...

All data is in one table: (frequently re-imported, so no relationships)
Obviously data needs to be stored more efficiently, but I have no control over the imported data structure. Here is what I'm after:

Table structure:
ID - Name - Status - Comments
1120 - One - Pass - uniquecomment1
1120 - One - Fail - uniquecomment2
1120 - One - WIP - uniquecomment3
345 - Two - Pass - uniquecomment5
345 - Two - WIP - uniquecomment6

Need a query to format data as:

ID - Name - Status - Comments
1120 - One - Fail - uniquecomment1, 2, 3...
345 - Two - WIP - uniquecomment5, 6..

As you can see comments need to be concatenated with commas seperating them.
As for the Status determination, it needs to be determined for the ID based on certain conditions. If at least 1 is fail, all fail. Iff all pass, all pass. If no fails and 1 wip, WIP, etc.

I have wasted too many hours trying to figure this out on my own, so I'm seeking your help! Any input would be appreciated. Thank you.

 

[Jon K]

See the Conc() function and the query in the attached database.

ADO code is used in the function.

Since you haven't specified whether the ID field is numeric or text, extra code has been used in the function to test for its data type. Removing the test can make the query run faster.


Note:
If the table is large, you can modify the code to update a temporary table instead of displaying the results in a query. For this kind of concatenation, updating a temp table is more efficient.
.

 

[Cosmos75]

I assume you mean the fConcatenateFldGroupBy() function from Concatenating unique field values for records matching a query's GROUP BY clause. (Old link)

Please find attached an example Access 2000 file. The SQL statement is as follows

SELECT DISTINCT ID, Name, Status,
fConcatenateFldGroupBy("tblData","Comments","ID",[ID],"Name",[Name],"Status",[Status])
AS strConcatenated
FROM tblData;

Also, it isn't a good idea to give your fields names like "name". Here's a list of Microsoft Jet 4.0 reserved words just to give you an idea of words to be avoided.

 

[EMP]

Table structure:
ID - Name - Status - Comments
1120 - One - Pass - uniquecomment1
1120 - One - Fail - uniquecomment2
1120 - One - WIP - uniquecomment3
345 - Two - Pass - uniquecomment5
345 - Two - WIP - uniquecomment6

Need a query to format data as:

ID - Name - Status - Comments
1120 - One - Fail - uniquecomment1, 2, 3...
345 - Two - WIP - uniquecomment5, 6..

fConcatenateFldGroupBy didn't concatenate anything on the given five sample records. The same five records were returned.

^

 

[Cosmos75]

fConcatenateFldGroupBy didn't concatenate anything on the given five sample records. The same five records were returned.

For the 5 records that were given there wasn't anything to concatenate by (see below).

Table structure:
ID - Name - Status - Comments
1120 - One - Pass - uniquecomment1
1120 - One - Fail - uniquecomment2
1120 - One - WIP - uniquecomment3
345 - Two - Pass - uniquecomment5
345 - Two - WIP - uniquecomment6

My example only concatenated those records where the combination of ID, Name, and Status were the same.

ID - Name - Status - Comments
1120 - One - Pass - uniquecomment1a
1120 - One - Fail - uniquecomment2a
1120 - One - WIP - uniquecomment3a
345 - Two - Pass - uniquecomment5a
345 - Two - WIP - uniquecomment6a
1120 - One - Pass - uniquecomment1b
1120 - One - Fail - uniquecomment2b
1120 - One - WIP - uniquecomment3b
345 - Two - Pass - uniquecomment5b
345 - Two - WIP - uniquecomment6b

Query results
ID - Name - Status - fConcatenateFldGroupBy()
1120 - One - Pass - uniquecomment1a, uniquecomment1b
1120 - One - Fail - uniquecomment2a, uniquecomment2b
1120 - One - WIP - uniquecomment3a, uniquecomment3b
345 - Two - Pass - uniquecomment5a, uniquecomment5b
345 - Two - WIP - uniquecomment6a, uniquecomment6b

As for the Status determination, it needs to be determined for the ID based on certain conditions. If at least 1 is fail, all fail. Iff all pass, all pass. If no fails and 1 wip, WIP, etc.

I understand that my example did not take the determination of an "aggregate status", but I just wanted to show how to use fConcatenateFldGroupBy to concatenate fields for a combination of field values for a given record.

I didn't try to figure out this criteria part because there are at least combinations that don't fall under the three rules given (as I understood them); i.e.

• 1 Pass, 1 Fail, and 1 WIP.
• 2 Fail, and 1 WIP.

Also, I don't know for sure if eash ID only has 3 records or if there could be more.

Assuming that the "Status" determination is independant of the concatenation of comments you could just do this.

SELECT DISTINCT ID, Name, fConcatenateFldGroupBy("tblData","Comments","ID",[ID],"Name",[Name]) 
AS strConcatenated
FROM tblData;

Of course this doesn't get you the "Aggregate Status"
Now for the determinig the status based on aggregate status.

First, we start off with a basic grouing query to count the total number of "status".

SELECT tblData2.ID, tblData2.Name, Count(tblData2.Status) AS CountOfStatus
FROM tblData2
GROUP BY tblData2.ID, tblData2.Name;

Next, we use a subselect statements to count the number of occurences for each possible status value, e.g. for the count of Fails

(SELECT Count(f.Status) FROM tblData2 as f WHERE f.ID = tblData2.ID And f.Status = "Fail") as Fails

Then we use the Switch function for each rule given to detemine the appropriate status.

Switch([CountOfStatus]=[Fails],"FAIL!",[CountOfStatus]=[Passess],"PASS!",[WIPs]>0 And [Fails]=0,"WIP!")

However, this will return a NULL value if none of the 3 conditions are met, so we use NZ() to return something.
Nz(Switch([CountOfStatus]=[Fails],...WIP!"),"???")

I have attached the previous sample with a new data table (tblData2) and query (qryStatus).

 

[EMP]

If at least 1 is fail, all fail. Iff all pass, all pass. If no fails and 1 wip, WIP, etc.

Agreed. The etc. in the third condition does need elaboration by the OP unless what he/she meant is "If the first 2 conditions are untrue, then it's WIP."


By the way, the following two should fall under the first condition: If at least 1 is fail, all fail.

1 Pass, 1 Fail, and 1 WIP.
2 Fail, and 1 WIP.

^

 

[Cosmos75]

By the way, the following two should fall under the first condition: If at least 1 is fail, all fail.

1 Pass, 1 Fail, and 1 WIP.
2 Fail, and 1 WIP.

Thanks, EMP! Doh, I missed that!

So the Switch() should be changed to
Switch([Fails]>0,"FAIL!",[CountOfStatus]=[Passess],"PASS!",[WIPs]>0 And [Fails]=0,"WIP!")

Also, I should point out that you can use DCount instead of a Select Subquery. Just that I am used to not using Domain Aggregate functions in queries.
Passess: DCount("Status","tblData2","[ID]=" & [ID] & " And [Status] = 'Pass'")

 

[WayneRyan]

Ocular,

Isn't there any chronology here? Shouldn't it really matter when something's
status changes from --> pass/fail/"Work-In-Progress"?

There are several threads here using very simple recordsets that can transform this:

1120 - One - Pass - uniquecomment1
1120 - One - Fail - uniquecomment2
1120 - One - WIP - uniquecomment3

into this:

1120 - One - Status - uniquecomment1, uniquecomment2, uniquecomment3

But, I see two problems:

Status
======

Shouldn't there be a "most recent" status?

If something failed and then had 7 Passes afterward, would you really consider
it the same as something that passed 7 times and recently failed?

Comments
========

Shouldn't the ORDER of the comments be important?

Anyway, one query with a function call should suffice:

Select id, name, fnGetStatusInfo(id, name) <-- Returns Status: Comment1,Comment2, ...
From   YourTable
Group By id, name
Order By id, name;

The Public function returns one string.

The status which I'd like to see as the Latest status concatenated with
the appended comments in chronological order.

A) I'd like to see the status as a simple most recent lookup.
B) But Cosmos's code fits your current definition.

A) I'd like to see the comments in chronological order.
B) But if order's not important, there are examples here of doing that.

In short (long?) You just have to write one function. Its two components aren't
well defined to me, but just use either the A or B pair from above.

just some thoughts,
Wayne