Concatenate in Access~special circumstances

matchmole

New Member
Joined
Jun 18, 2002
Messages
26
Hi,
I need to be able to combine (concatenate?) fields in Access so that each item in Field 1 is joined to each item in Fields 2 thru 5. I have tried to illustrate this in the samples below...

In the samples below, Account code is in Field 1... String(s) to be added are in Field 2. Then in Field 3 I need for each code in Field 1 to be joined with all of the strings in Field 2 thru Field 5.

Account Code String(s) to be added to each Account Code
001-001001-001234- 401260
001-001001-002345- 401270
001-001001-003456- 401280
001-001001-004567- 750400
001-001001-005678- 410000
001-001001-006789- 420000
002-002001-001234- 430000
002-002001-002345- 440000
002-002001-003456- 450000
002-002001-004567- 460000
002-002001-005678-
002-002001-006789-
002-002001-006543-
002-002001-006432-
002-002001-006321-

So I would like to end up with this result:
001-001001-001234-401260
001-001001-001234-401270
001-001001-001234-401280
001-001001-001234-750400
001-001001-001234-410000 ... and on and on until each account code in Field 1 has been combined with every string in Field 2 thru Field 5. Is that possible to accomplish using Access?

Thanks,
Ken
 

Excel Facts

Best way to learn Power Query?
Read M is for (Data) Monkey book by Ken Puls and Miguel Escobar. It is the complete guide to Power Query.

SydneyGeek

MrExcel MVP
Joined
Aug 5, 2003
Messages
12,251
Add the table to a new query, 5 times.
Double-click Field1 from the first table
Double-click Field2 from the second table
...etc until Field5 is in the grid.
Don't join any of the tables.

When you run the query you should get all possible combinations of the 5 fields.

Denis
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,163,492
Messages
5,832,011
Members
430,103
Latest member
BIGGAZ

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top