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
hwassingerAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GozrehConnect With a Mentor Commented:
i wrote for you a function in VBA
MS-Access-concatenate-across-rec.accdb
0
 
Angelp1ayCommented:
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
0
 
hwassingerAuthor Commented:
@ Gozreh, That is EXACTLY what I want, what do I need to do to make this work with my actual database?
0
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
GozrehCommented:
please explain the structure of your database, or upload some sample
0
 
Angelp1ayCommented:
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 :)
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

Open in new window


SQL query that directly aggregates orders
0
 
GozrehCommented:
Hi @hwassinger, did you resolve this question?
if not, please explain the structure of your database, or upload some sample, so i can try to help it work with your actual database.

the solution from @Angelp1ay will not work in a access database, only if you are using SQL server.
0
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.