Are you interested in becoming an AWS Certified Solutions Architect?
Discover a new interactive way of training for the exam.
Course Of The Month
6 days, 5 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
Active 5 days ago
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
Are you interested in becoming an AWS Certified Solutions Architect?
Discover a new interactive way of training for the exam.
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…
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.
Add bar graphs to Access queries using Unicode block characters. Graphs appear on every record in the color you want. Give life to numbers.
Hopes this gives you ideas on visualizing your data in new ways ~
Create a calculated field in a query:
…
Suggested Courses
Course of the Month6 days, 5 hours left to enroll
705 members asked questions and received personalized solutions in the past 7 days.
Join the community of 500,000 technology professionals and ask your questions.