Help Required - Cant work this out

[Paddyddd]

Hi All



Apologies if I have posted this in the wrong section.

I have an Excel Spreadsheet that I have imported into Access. I has 8 columns, Acct_fm, Acct_to, Dept_fm, Dept_To, OrderNo_fm, OrderNo_to, CostCentre_fm, CostCentre_to.

The fm columns can all have several strings in them, separated by , or it can form part of a range with the to column.

What I am trying to do, is blow this out, so I have every combination within the 4 major strings.

So for example

Example 1

Acct_fm, Acct_to, Dept_fm, Dept_To, OrderNo_fm, OrderNo_to, CostCentre_fm, CostCentre_to
10001 10010 345 390 4324 7778 123456 234567

Example 2


Acct_fm, Acct_to, Dept_fm , Dept_To, OrderNo_fm , OrderNo_to, CostCentre_fm , CostCentre_to
10001,10002,10003,10004 345,456,7896 4324,4325,4326 123456,123457,123458

So in each of the examples above, I want one column for Acct, Dept, OrderNo and CostCentre with all the values in the range in there own row in a table or all the strings separate by columns within there different rows in the same table as the range work out.

So that would be

Acct Dept OrderNo CostCentre
10001 345 4324 123456
10002 345 4324 123456
10001 346 4324 123456

I hope someone can help me.

Many Thanks

Paddy

[Paddyddd]

Example 2 should read

Acct_frm
10001,10002,10003,10004

Dept_fm
345,456

OrderNo_Fm
7896 4324,4325,4326

CostCentre_Fm
123456,123457,123458

There will be nothing in the to columns

[aytee111]

Do these one at a time. If you know VBA then do it there, otherwise if will be lots of queries.
- q1 - list of all FM's that are single entries
- q2 - list of all to's where not null
- q3 - all first occurrence of fm
- q4 - second occurrence of fm
etc

Then use a UNION query to make one long list and your append query will be a SELECT DISTINCT.

[Micron]

10001,10002,10003,10004

Are we to understand that all these values are in one table field (e.g. all belong to Acct_fm)? I think you are going to need a function that parses the comma separated values. Yes you can devise queries to get the nth part of a string and blend them all together in a Union query - just not sure what happens when there is n-1 values the next time. For a function to be reliable, the data has to be consistent, or error checking needs to be employed; your posted examples are inconsistent:
OrderNo_Fm
7896,?4324,4325,4326
Tip: dump data into Excel, center all text, size all columns to fit then copy/paste here. You should get a decent table that if necessary, you can "Go Advanced" and increase the table width. It would keep your data aligned with the columns and make it much easier to read.