The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.
COURSE of the MONTH
9 days, 2 hours left to enrollBecome a Premium Member and unlock a new, free course in leading technologies each month.
Solved
MS Access concatenate across records
Posted on 2014-01-31
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
[X]
Welcome to Experts Exchange
Add your voice to the tech community where 5M+ people just like you are talking about what matters.
- Help others & share knowledge
- Earn cash & points
- Learn & ask questions
-
3
2
6 Comments
i wrote for you a function in VBA
MS-Access-concatenate-across-rec.accdb
MS-Access-concatenate-across-rec.accdb
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
Featured Post
Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.
Question has a verified solution.
If you are experiencing a similar issue, please ask a related question
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications. You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…
Suggested Courses
Course of the Month9 days, 2 hours left to enroll
765 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.