Concatenate rows

  • Thread starter Thread starter Persh
  • Start date Start date
P

Persh

I need to know how i can get a result similar to this
using queries or some VB code.

Table 1:
---------
Part Name1 Name2 Name3 Name4
1 A1 AWD .5 Extended
1 A1 BWD .5 Regular
1 A1 AWD .75 Extended
2 A2 BWD .5 Extended
2 A2 AWD .5 Extended

To be displayed as:
Part Name1 Name2 Name3 Name4
1 A1 AWD BWD .5 Extended Regular
1 A1 AWD .75 Extended
2 A2 AWD BWD .5 Extended

Thanks,
Persh
 
Hi Persh,

It looks like you want to group by the Part, Name1, and
Name2, then concatenate any matches for Name2 and Name4.

If Name2 and Name4 can only consist of the two choices
listed, you could use a group by query (click the totals
button on the toolbar), group by the three fields
mentioned, and use iif(), min() and max() functions to
calculate the concatenated string. This would look
something like (air code)

iif(min([Name2])=max([Name2],min([Name2]),min([Name2]
& ", " & max([Name2])

This may give problems with Null values though, so you
may need to substitute Nz([Name2]) for each [Name2] above.

If, however, Name2 and Name4 could consist of more than
just the two choices, you will probably need to write a
custom function, which is fairly easy. There was a post
in this group this morning (7:05) titled vertical
concatenation by Di that addressed how to do this. You
would just need to modify the code slightly to get the
grouping that you want.

Hope this helps. Post back with more info if you have
any other questions.

-Ted Allen
 
Ted,

I tried your code with some modifications. However, i get
the data to display like this:

Part Name1 Name2 Name3 Name4 BodyStyle
1 A1 2WD .5 All Regular 2WD 4WD 4WD
1 A1 2WD .5 Crew Cab 2WD 4WD 4WD
1 A1 4WD .75 Extended Cab 2WD 4WD 4WD
1 A1 4WD .75 Regular Cab 2WD 4WD 4WD

The first problem is that it skips the first 2WD so i only
writes three values in BodyStyle (Field that is generated
to concatenate the rows). I would like the final result
to look like this in two rows based on how they match up.

Part BodyStyle
1 2WD .5 All Regular, Crew Cab
1 4WD .75 Extended Cab, Regular Cab

I guess in order to answer your question for each unique
Part and Name1 there can exist at most 2 Name2 (2WD or
4WD), 3 Name3 (.5, .75 or 1) and 4 Name4 (All Regular,
Crew Cab, Extended Cab and Regular Cab).

Thanks for your help. If you need the code i used in my
module let me know. It is the same code you used to build
your Council Districts.

Hope this helps,
Persh
-----Original Message-----
Hi Persh,

It looks like you want to group by the Part, Name1, and
Name2, then concatenate any matches for Name2 and Name4.

If Name2 and Name4 can only consist of the two choices
listed, you could use a group by query (click the totals
button on the toolbar), group by the three fields
mentioned, and use iif(), min() and max() functions to
calculate the concatenated string. This would look
something like (air code)

iif(min([Name2])=max([Name2],min([Name2]),min([Name2]
& ", " & max([Name2])

This may give problems with Null values though, so you
may need to substitute Nz([Name2]) for each [Name2] above.

If, however, Name2 and Name4 could consist of more than
just the two choices, you will probably need to write a
custom function, which is fairly easy. There was a post
in this group this morning (7:05) titled vertical
concatenation by Di that addressed how to do this. You
would just need to modify the code slightly to get the
grouping that you want.

Hope this helps. Post back with more info if you have
any other questions.

-Ted Allen
-----Original Message-----
I need to know how i can get a result similar to this
using queries or some VB code.

Table 1:
---------
Part Name1 Name2 Name3 Name4
1 A1 AWD .5 Extended
1 A1 BWD .5 Regular
1 A1 AWD .75 Extended
2 A2 BWD .5 Extended
2 A2 AWD .5 Extended

To be displayed as:
Part Name1 Name2 Name3 Name4
1 A1 AWD BWD .5 Extended Regular
1 A1 AWD .75 Extended
2 A2 AWD BWD .5 Extended

Thanks,
Persh
.
Click to expand...
.
Click to expand...
 
Hi Persh,

Yes, please post the code and I will see if I can spot
the problem.

-Ted
-----Original Message-----
Ted,

I tried your code with some modifications. However, i get
the data to display like this:

Part Name1 Name2 Name3 Name4 BodyStyle
1 A1 2WD .5 All Regular 2WD 4WD 4WD
1 A1 2WD .5 Crew Cab 2WD 4WD 4WD
1 A1 4WD .75 Extended Cab 2WD 4WD 4WD
1 A1 4WD .75 Regular Cab 2WD 4WD 4WD

The first problem is that it skips the first 2WD so i only
writes three values in BodyStyle (Field that is generated
to concatenate the rows). I would like the final result
to look like this in two rows based on how they match up.

Part BodyStyle
1 2WD .5 All Regular, Crew Cab
1 4WD .75 Extended Cab, Regular Cab

I guess in order to answer your question for each unique
Part and Name1 there can exist at most 2 Name2 (2WD or
4WD), 3 Name3 (.5, .75 or 1) and 4 Name4 (All Regular,
Crew Cab, Extended Cab and Regular Cab).

Thanks for your help. If you need the code i used in my
module let me know. It is the same code you used to build
your Council Districts.

Hope this helps,
Persh
-----Original Message-----
Hi Persh,

It looks like you want to group by the Part, Name1, and
Name2, then concatenate any matches for Name2 and Name4.

If Name2 and Name4 can only consist of the two choices
listed, you could use a group by query (click the totals
button on the toolbar), group by the three fields
mentioned, and use iif(), min() and max() functions to
calculate the concatenated string. This would look
something like (air code)

iif(min([Name2])=max([Name2],min([Name2]),min([Name2]
& ", " & max([Name2])

This may give problems with Null values though, so you
may need to substitute Nz([Name2]) for each [Name2] above.

If, however, Name2 and Name4 could consist of more than
just the two choices, you will probably need to write a
custom function, which is fairly easy. There was a post
in this group this morning (7:05) titled vertical
concatenation by Di that addressed how to do this. You
would just need to modify the code slightly to get the
grouping that you want.

Hope this helps. Post back with more info if you have
any other questions.

-Ted Allen
-----Original Message-----
I need to know how i can get a result similar to this
using queries or some VB code.

Table 1:
---------
Part Name1 Name2 Name3 Name4
1 A1 AWD .5 Extended
1 A1 BWD .5 Regular
1 A1 AWD .75 Extended
2 A2 BWD .5 Extended
2 A2 AWD .5 Extended

To be displayed as:
Part Name1 Name2 Name3 Name4
1 A1 AWD BWD .5 Extended Regular
1 A1 AWD .75 Extended
2 A2 AWD BWD .5 Extended

Thanks,
Persh
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
P.S., I would guess without seeing the code that the
likely problem is that you will need to modify the
function to recieve both of your group by fields (Part
and Name1), and the sql text would then have to select
all records from the table matching that Part and that
Name1. Also, the query that is calling the function
should be grouped by Part and Name1 so that there is only
one row for each combination (if you haven't done this
you do it by clicking the totals button on the toolbar).

Of course, since you are concatenating multiple fields,
you will either need to write a separate function for
each concatenated field, or add a third argument to the
function to designate which field you want to return
(since the function can only return one string).

-Ted
-----Original Message-----
Hi Persh,

Yes, please post the code and I will see if I can spot
the problem.

-Ted
-----Original Message-----
Ted,

I tried your code with some modifications. However, i get
the data to display like this:

Part Name1 Name2 Name3 Name4 BodyStyle
1 A1 2WD .5 All Regular 2WD 4WD 4WD
1 A1 2WD .5 Crew Cab 2WD 4WD 4WD
1 A1 4WD .75 Extended Cab 2WD 4WD 4WD
1 A1 4WD .75 Regular Cab 2WD 4WD 4WD

The first problem is that it skips the first 2WD so i only
writes three values in BodyStyle (Field that is generated
to concatenate the rows). I would like the final result
to look like this in two rows based on how they match up.

Part BodyStyle
1 2WD .5 All Regular, Crew Cab
1 4WD .75 Extended Cab, Regular Cab

I guess in order to answer your question for each unique
Part and Name1 there can exist at most 2 Name2 (2WD or
4WD), 3 Name3 (.5, .75 or 1) and 4 Name4 (All Regular,
Crew Cab, Extended Cab and Regular Cab).

Thanks for your help. If you need the code i used in my
module let me know. It is the same code you used to build
your Council Districts.

Hope this helps,
Persh
-----Original Message-----
Hi Persh,

It looks like you want to group by the Part, Name1, and
Name2, then concatenate any matches for Name2 and Name4.

If Name2 and Name4 can only consist of the two choices
listed, you could use a group by query (click the totals
button on the toolbar), group by the three fields
mentioned, and use iif(), min() and max() functions to
calculate the concatenated string. This would look
something like (air code)

iif(min([Name2])=max([Name2],min([Name2]),min([Name2]
& ", " & max([Name2])

This may give problems with Null values though, so you
may need to substitute Nz([Name2]) for each [Name2] above.

If, however, Name2 and Name4 could consist of more than
just the two choices, you will probably need to write a
custom function, which is fairly easy. There was a post
in this group this morning (7:05) titled vertical
concatenation by Di that addressed how to do this. You
would just need to modify the code slightly to get the
grouping that you want.

Hope this helps. Post back with more info if you have
any other questions.

-Ted Allen
-----Original Message-----
I need to know how i can get a result similar to this
using queries or some VB code.

Table 1:
---------
Part Name1 Name2 Name3 Name4
1 A1 AWD .5 Extended
1 A1 BWD .5 Regular
1 A1 AWD .75 Extended
2 A2 BWD .5 Extended
2 A2 AWD .5 Extended

To be displayed as:
Part Name1 Name2 Name3 Name4
1 A1 AWD BWD .5 Extended Regular
1 A1 AWD .75 Extended
2 A2 AWD BWD .5 Extended

Thanks,
Persh
.

.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Ted,

Here is the code, right now its grouped by Part and Name1
And i call it using => BodyStyle: cmdCon([Part])

Public Function cmdCon(Part As Double)
Dim dbs As Database, rstReadCDTable As Recordset
Set dbs = CurrentDb
strSQL = "SELECT * FROM Tbl_Complete_Web_02_DoSplit WHERE
[Part]= " & Part
Set rstReadCDTable = dbs.OpenRecordset(strSQL,
dbOpenSnapshot)
With rstReadCDTable
If .BOF = True Then
cmdCon = Null
Exit Function
End If
.MoveNext

Do While Not .EOF
If ![Drive] = "2WD" Then
cmdCon = cmdCon & " " & ![Drive]
Debug.Print cmdCon

ElseIf ![Drive] = "4WD" Then
cmdCon = cmdCon & " " & ![Drive]
Debug.Print cmdCon
End If
.MoveNext
Loop
End With
End Function
 
Hi Persh,

The first thing that I notice with your function and sql
statement is that you are only passing and querying based
on the Part. But, you say you are grouping by Part and
Name1. Are these redundant so that Part 1 will always
have the same Name1, which no other parts will have? If
so there is no problem, but if not you should pass both
the part and the Name1 values to the function and use
both in the criteria for your sql statement.

In looking back at your earlier messages, it also seems
like you are grouping by Name3 (which I assume is the
capacity in tons?) because these are not being
concatenated, and you have a separate line for the same
Part and Name 1, for different Name3 values. If this is
the case, you should also group by Name 3 in your query
and pass the Name3 value to your function, and include it
in the criteria for the sql statement.

With regard to the body style, your second to last post
wasn't clear to me. Your second example showing what you
wanted the data to look like only listed 2WD. Do you
want this field to contain a list of all of the matches
for the grouping; such as 2WD, 2WD, 4WD, etc. Or, do you
want it to just list at most 2WD, 4WD no matter how many
of each of these there would be? Or, do you want to
group by this field also.

In looking back at your examples again, I am realizing
that I'm really not sure what you want to group by and
what you want to display. Maybe post back to clarify
that. Once I get a clear understanding of that the
actual code modifications will be easy.

-Ted Allen
 
Ted,

I guess the best way to explain is to start from the
beginning. Lets say my data looks like this:

Part Name1 Name2 Name3 Name4
1 A1 2WD .5 All Regular
1 A1 2WD .5 Crew Cab
1 A1 4WD .75 Extended Cab
1 A1 4WD .75 Regular Cab

2 A1 2WD 1 Crew Cab
2 A1 2WD .5 Crew Cab

3 B1 2WD 1 All Regular
3 B1 2WD .5 Crew Cab
3 B1 2WD .75 All Regular

4 C1 2WD .5 All Regular
4 C1 2WD .5 Crew Cab

And this is how i need the output:

Part Name1 BodyStyle
1 A1 2WD .5 All Regular, Crew Cab
1 A1 4WD .75 Extended Cab, Regular Cab

2 A1 2WD .1, .5 Crew Cab

3 B1 2WD 1, .75 All Regular
3 B1 2WD .5 Crew Cab

4 C1 2WD .5 All Regular, Crew Cab

In my DB, Part and Name1 togeather make up a unique key.
And what i want to do is group on Name2, Name3 and Name4.
So, in the above example (lets take Part 1) Name2 has two
options (2WD and 4WD) but only where Name3 are equal to
each other they are grouped togeather. And going back to
your questions i only want it to display at most 2WD or
4WD no matter how many matches it finds.

If we take Part 2, we can see that Name2 and Name4 are the
same and Name3 is different so that data would be
displayed as such:
2 A1 2WD .1, .5 Crew Cab

If we take Part 3, Name2 and Name4 are common values with
2WD and All Regular. But for Crew Cab, Name3 is different
(.5) so, this would be listed in a new record.

If we take Part 4, Name2 and Name3 are the same so the
data would be displayed as such:
4 C1 2WD .5 All Regular, Crew Cab

Again, i only need at most the unique value for each
column no matter how many different combination matches it
creates.

Hope this helps.

Thanks,
Persh
 
Hi Persh,

OK, now I think I understand what you are trying to do.
Basically you are always grouping on Part and Name1, but
you then are generally grouping on two other fields and
concatenating the third, with the tricky part being that
you want to vary which other fields you are grouping on
based on which one has duplicates. This is actually much
more complex, because you will not be able to just call a
function from a query, because in order to do this you
would have to have one record in your query for each
output record that you want, which you can't do because
you want to group differently for each record.

Depending how much you really need to do this, you could
write a procedure (called by clicking a button or
something) that would step through the entire table,
analyze the records, and group the records & build the
strings accordingly - then write these to another table.
But, this would probably be pretty tricky to put together
due to the need to determine the groupings for each group
of records. This is one of those things that is much
easier to do in your own mind then to put into code.

For instance, in the example you gave, if I add an extra
record to the first group as follows:

Part Name1 Name2 Name3 Name4
1 A1 2WD .5 All Regular
1 A1 2WD .5 Crew Cab
1 A1 2WD .75 Crew Cab
1 A1 4WD .75 Extended Cab
1 A1 4WD .75 Regular Cab

Would you then just group by 2WD as follows:
1 A1 2WD .5,.75 All Regular, Crew Cab
1 A1 4WD .75 Extended Cab, Regular Cab

But then you wouldn't know which .5,.75 go with All
Regular/Crew Cab.

Or, would you create a new record for .75 as follows:
1 A1 2WD .5 All Regular, Crew Cab
1 A1 2WD .75 Crew Cab
1 A1 4WD .75 Extended Cab, Regular Cab

Now, what if there was also a .75 All Regular, would you
then go with the previous option.

What if you had 20 1/A1's such that all of the fields had
duplicates. How would you group then?

These are the things that are easy for the human mind to
do, but tough to put into code. The code syntax is easy,
but first you would have to define the logic process that
you want the code to go through such that it will be able
to produce what you want.

You may want to consider approaching this another way.
You could put together a query grouping by all but Name4,
and use a function to concatenate the list for Name4
(very easy). Then, you could build another query
grouping by all but Name3, and use a function to build a
list for Name3 (also very easy) for any duplicates. You
could do a further step to do the same for Name2,
although I don't know how many duplicates there would be
at that point. I think this would give you pretty much
what you want, but you would be able to run it instantly
any time at run-time.

If you did this with your original list, the first query
would give you:

1 A1 2WD .5 All Regular, Crew Cab
1 A1 4WD .75 Extended Cab, Regular Cab
2 A1 2WD 1 Crew Cab
2 A1 2WD .5 Crew Cab
3 B1 2WD 1 All Regular
3 B1 2WD .5 Crew Cab
3 B1 2WD .75 All Regular
4 C1 2WD .5 All Regular, Crew Cab

The second query would then give you:
1 A1 2WD .5 All Regular, Crew Cab
1 A1 4WD .75 Extended Cab, Regular Cab
2 A1 2WD .5,1 Crew Cab
3 B1 2WD .75,1 All Regular
3 B1 2WD .5 Crew Cab
4 C1 2WD .5 All Regular, Crew Cab

Which is exactly what you were looking for. As I
mentioned, you could run a third query to further group
by Name2, but the strings in Name3 and Name4 would have
to match in order for a grouping to occur. One other
note with this method, it would allow for multiple fields
to be concatenated, and in those cases you would know
that all options exist.

-Ted Allen
 
Back
Top