?
Solved

MS Access concatenate across records

Posted on 2014-01-31
6
Medium Priority
?
241 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 2000 total points
ID: 39824992
i wrote for you a function in VBA
MS-Access-concatenate-across-rec.accdb
0
 
LVL 11

Expert Comment

by:Angelp1ay
ID: 39825065
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
ID: 39825212
@ Gozreh, That is EXACTLY what I want, what do I need to do to make this work with my actual database?
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 10

Expert Comment

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

Expert Comment

by:Angelp1ay
ID: 39825679
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
ID: 39833348
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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…

615 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