Solved

MS Access concatenate across records

Posted on 2014-01-31
6
228 Views
Last Modified: 2014-03-26
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
0
Comment
Question by:hwassinger
  • 3
  • 2
6 Comments
 
LVL 10

Accepted Solution

by:
Gozreh earned 500 total points
Comment Utility
i wrote for you a function in VBA
MS-Access-concatenate-across-rec.accdb
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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
 

Author Comment

by:hwassinger
Comment Utility
@ Gozreh, That is EXACTLY what I want, what do I need to do to make this work with my actual database?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 10

Expert Comment

by:Gozreh
Comment Utility
please explain the structure of your database, or upload some sample
0
 
LVL 11

Expert Comment

by:Angelp1ay
Comment Utility
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
 
LVL 10

Expert Comment

by:Gozreh
Comment Utility
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

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Introduction This Article briefly covers methods of calculating the NPV and IRR variants in Excel as well as the limitations in calculating and interpreting IRR results. Paraphrasing Richard Shockley, author of my favourite finance reference tex…
This article will guide you to convert a grid from a picture into Excel format using Microsoft OneNote and no other 3rd party application.
The viewer will learn how to simulate a series of sales calls dependent on a single skill level and learn how to simulate a series of sales calls dependent on two skill levels. Simulating Independent Sales Calls: Enter .75 into cell C2 – “skill leve…
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…

772 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now