Angel said:
In access, I need to run a query that will give me a concatenate of certain
fields IF the same order number number shows up more than once (if the order
# shows up twice then I need Access to concat both rows, 3 times, then concat
all 3, etc). Example:
Order number part number part description
Aa 123 phone
Aa 456 accessory
Aa 185 accessory
Aa 844 misc
document
Bg 454 phone
Bg 888 phone
Bg 928 accessory
Cp 988 accessory
Cp 909 misc
document
From the table above, I want access to concatenate the “part number†and
“part description†fields to show me “123phone 456accessory 185accessory
844misc document†for order number Aa. I want it to do the same for Bg and
Cp.
OK, my Table looks a lot like yours:
Order part part description
number number
------ ------ ----------------
Aa 123 phone
Aa 456 accessory
Aa 185 accessory
Aa 844 misc document
Bg 454 phone
Bg 888 phone
Bg 928 accessory
Cp 988 accessory
Cp 909 misc document
I wrote 3 Queries. The first adds a sequence number to each record with
a given part number.
[Q_Seq] SQL:
SELECT Parts.[Order number], [Parts]![part number]
& [Parts]![part description] AS Stuff,
Count(Parts_1.[part number]) AS [CountOfpart number]
FROM Parts INNER JOIN Parts AS Parts_1
ON Parts.[Order number] = Parts_1.[Order number]
WHERE (((Parts_1.[part number])<=[Parts].[part number]))
GROUP BY Parts.[Order number],
[Parts]![part number] & [Parts]![part description]
ORDER BY [Parts]![part number]
& [Parts]![part description];
The third column contains the sequence number.
[Q_Seq]
Order Stuff CountOfpart number
number
------ ------------------ ------------------
Aa 123phone 1
Aa 185accessory 2
Bg 454phone 1
Aa 456accessory 3
Aa 844misc document 4
Bg 888phone 2
Cp 909misc document 1
Bg 928accessory 3
Cp 988accessory 2
The 2nd Query crosstabs them in the order specified by the generated
sequence number. If you have more than four of any of them, there will
be additional columns in the Query Datasheet View. I showed only the
first 3 of them. You can limit how many you have by setting some criteria.
[Q_Seq_Crosstab]
TRANSFORM Min(Q_Seq.Stuff) AS MinOfStuff
SELECT Q_Seq.[Order number]
FROM Q_Seq
GROUP BY Q_Seq.[Order number]
PIVOT Q_Seq.[CountOfpart number];
[Q_Seq_Crosstab]
Order 1 2 3 ...
number
------ ---------------- ------------- ------------
Aa 123phone 185accessory 456accessory ...
Bg 454phone 888phone 928accessory ...
Cp 909misc document 988accessory
Then the 3rd Query piles the numbered columns together into a combined
field. If you expect more than 4 values for any one [Order number],
you'll need to include them here.
[Q_Cat]
SELECT Q_Seq_Crosstab.[Order number],
[Q_Seq_Crosstab]![1] & " "
& [Q_Seq_Crosstab]![2] & " "
& [Q_Seq_Crosstab]![3] & " "
& [Q_Seq_Crosstab]![4] AS Cat
FROM Q_Seq_Crosstab
ORDER BY Q_Seq_Crosstab.[Order number];
[Q_Cat]
Order Cat
number
------ ---------------------------------------------------
Aa 123phone 185accessory 456accessory 844misc document
Bg 454phone 888phone 928accessory
Cp 909misc document 988accessory
If you don't like the order I used, you should modify the SQL for
[Q_Seq] to generate those numbers differently.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
