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

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.
Barney EvansIT System AdministratorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
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.
0
Barney EvansIT System AdministratorAuthor Commented:
Thanks Jim, I'll see if it works for  me.
0
Barney EvansIT System AdministratorAuthor 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!
0
Determine the Perfect Price for Your IT Services

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

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
What does your export query look like?

Jim.
0
Barney EvansIT System AdministratorAuthor 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.
0
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<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.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Barney EvansIT System AdministratorAuthor Commented:
Thanks Jim! I'll try it.
0
Barney EvansIT System AdministratorAuthor Commented:
Jim,

Got it to export! Thanks again!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
SQL

From novice to tech pro — start learning today.