Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
MS Access concatenate across records
I have a table that contains customer ID's, order numbers and part numbers.
I need to identify orders that are identical across customers to make ordering groups of parts easier.
E.g. Cust 1 orders parts 7, 11 and 4 in a batch. How many times has that combination been ordered?
Ideally I see this as creating a field that contains the 3 fields in numerical order separated by a delimiter and then counting across records.
In the attached file I would expect
20,304,1626 2 records
20,304,988,1626 1 record
20,304,1911 2 records
unique-orders.xls
I need to identify orders that are identical across customers to make ordering groups of parts easier.
E.g. Cust 1 orders parts 7, 11 and 4 in a batch. How many times has that combination been ordered?
Ideally I see this as creating a field that contains the 3 fields in numerical order separated by a delimiter and then counting across records.
In the attached file I would expect
20,304,1626 2 records
20,304,988,1626 1 record
20,304,1911 2 records
unique-orders.xls
Who is Participating?
Maybe something like this?
- Requires the records to be sorted into orders (i.e. by customer, then order, then part).
- Incrementally build each order in col D
- Pick out only complete orders in col E
example.xls
- Requires the records to be sorted into orders (i.e. by customer, then order, then part).
- Incrementally build each order in col D
- Pick out only complete orders in col E
example.xls
@ Gozreh, That is EXACTLY what I want, what do I need to do to make this work with my actual database?
This works in SQL Server. I don't have access to hand, not sure if CROSS APPLY is legal in Access.
If it works you can do the whole thing directly in SQL :)
If it works you can do the whole thing directly in SQL :)
SELECT cust_num, order_num, part_codes = MAX(SUBSTRING(combined.code_list, 3, 1000))
FROM your_table AS e
CROSS APPLY (
SELECT ', ' + CAST(part_code AS nvarchar(10))
FROM your_table AS i
WHERE e.cust_num = i.cust_num AND e.order_num = i.order_num
ORDER BY part_code ASC
FOR XML PATH ('')
) AS combined (code_list)
GROUP BY cust_num, order_num
ORDER BY cust_num, order_num
Question has a verified solution.
Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.
Have a better answer? Share it in a comment.
All Courses
From novice to tech pro — start learning today.
MS-Access-concatenate-across-rec.accdb