Access 2016, Excel 2016. Move repeating data from a row to a new column.

Barney Evans
Barney Evans used Ask the Experts™
on
I have a data table in SQL, Access and I have also exported it to Excel. It has two columns "item_no" and "alt_item_no".

Here is an example of the current table:
item_no                             alt_item_no
FBA03HA450AB-00      A002-0.0
FBA03HA450KE-00      A002-0.0
FBA03HA450NA-00      A002-0.0
FBA03HA450UB-00      A002-0.0
FBA04HA450AB-00      A003-0.0
FBA04HA450BA-00      A003-0.0
FBA04HA450BB-00      A003-0.0
FBA04HA450KE-00      A003-0.0
FBA04HA450NA-00      A003-0.0
FBA04HA450VB-00      A003-0.0
FBA04HA600AB-00      A004-0.0
FBA04HA600KE-00      A004-0.0
FBA04HA600NA-00      A004-0.0
FBA04HA600VB-00      A004-0.0

I need to reformat the data so it displays like this:

alt_item_no                 item_no_1                            item_no_2                              item_no_3                         item_no_4                      item_no_5                       item_no_6          
A002-0.0                      FBA03HA450AB-00             FBA03HA450KE-00              FBA03HA450NA-00          FBA03HA450UB-00
A003-0.0                      FBA04HA450AB-00             FBA04HA450BA-00              FBA04HA450BB-00          FBA04HA450KE-00        FBA04HA450NA-00       FBA04HA450VB-00
A004-0.0                      FBA04HA600AB-00             FBA04HA600KE-00              FBA04HA600NA-00          FBA04HA600VB-00





Seems like it should be simple, but I've spent a day looking at SQL queries and pivot tables and cannot find a solution. I would do this by hand, but the table has 200,000 rows. Doesn't matter if I use SQL, Access, or Excel as long as the final table can be exported to a CSV for import

Each alt_item_no can have anywhere from one to twenty or more items associated with it. This is for an import into a new inventory system that handles alternate items differently than my current system.

Any help would be appreciated.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Take a look at the following article.   Should get you on your way:

https://www.experts-exchange.com/articles/2380/Domain-Aggregate-for-Concatenating-Values-by-Group-in-Microsoft-Access.html

Holler if you need more detail or that doesn't do it for you.

Jim.
Barney EvansIT System Administrator

Author

Commented:
Thanks Jim, I'll see if it works for  me.
Barney EvansIT System Administrator

Author

Commented:
Hi Jim,

It's works a great! I am having an issue on the export. Access 2016 is truncating the second field with the concatenated values to 256 characters.  I've tried every export format I can think of with no success.
Any suggestions?

Also, can this be changed to output each value to it's own column? That would fix the export issue.

Thanks!
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
What does your export query look like?

Jim.
Barney EvansIT System Administrator

Author

Commented:
SELECT Alt_no.Alt_Item_no, DConcat("Item_no","Alt_no","[Alt_Item_no] = '" & [Alt_Item_no] & "'") AS Item_no
FROM Alt_no
GROUP BY Alt_no.Alt_Item_no;

If I view the query in Access the data is not truncated.
President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012
Commented:
<<If I view the query in Access the data is not truncated.>>

 OK, so it's not the query that is the issue.   Your only workaround then is to write it to a temp table with a memo field and then export the temp table.

Jim.
Barney EvansIT System Administrator

Author

Commented:
Thanks Jim! I'll try it.
Barney EvansIT System Administrator

Author

Commented:
Jim,

Got it to export! Thanks again!

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial