combining data

[Guest]

I have a database with several tables. I was able to generate a query that
has the information that I need for another program. Currently all of the
data I need is in the query. I have a field that contains my number that I
filter on. When I filter the data I may get one line or I get several lines
of data. On the several lines of data most of the information is the same, it
is the items and quantity that are different. I need to get the data down to
one line.
Is there a way to get the multiple lines to be in one record?
For example, the data currently looks like:
23456 label 0550 Item #1 23
23456 label 0550 Item #2 25
23456 label 0550 Item #5 18
23456 label 0550 Item #9 2
I would like the data to read:
23456 label 0550 Item #1 23 Item #2 25 Item #5 18 Item #9
2

Thanks in advance.
Ron Berns

 

[Guest]

You did not give any field names or output column labels. This is not quite
what you said you wanted but it might do.

TRANSFORM Sum(klnmis.Field5) AS SumOfField5
SELECT klnmis.Field1, klnmis.Field2, klnmis.Field3
FROM klnmis
GROUP BY klnmis.Field1, klnmis.Field2, klnmis.Field3
PIVOT klnmis.Field4;

Table klnmis --
Field1 Field2 Field3 Field4 Field5
23456 label 0550 Item 1 23
23456 label 0550 Item 2 25
23456 label 0550 Item 5 18
23456 label 0550 Item 9 2

 

[Guest]

You did not list any field names or output labels. This is not exactly what
you asked for but it might do.

TRANSFORM Sum(klnmis.Field5) AS SumOfField5
SELECT klnmis.Field1, klnmis.Field2, klnmis.Field3
FROM klnmis
GROUP BY klnmis.Field1, klnmis.Field2, klnmis.Field3
PIVOT klnmis.Field4;

Table klnmis --
Field1 Field2 Field3 Field4 Field5
23456 label 0550 Item 1 23
23456 label 0550 Item 2 25
23456 label 0550 Item 5 18
23456 label 0550 Item 9 2

 

[Guest]

Karl,
It is close to what I need, but I need field4 to print next to field 5 in
the line. Your solution puts field4 as the header of each column of field5.
Is there any way to make field3 the header for field4 and field5?
Ron