record splitting

[Guest]

Hi,

I receive via Excel a report about documents. It list several fields, like
doc_name, doc_id, …
A document can be linked to several clients. For instance, a document doc_A
can be linked to 3 customers, client_1, client_2, client_3. In my report, the
clients linked to a document will be grouped in one field, separated by
commas. For record doc_A, it will be client_1, client_2, client_3.

With one record of my document report, I must create one record per client.
For instance I have :

doc_id : doc_A
doc_client : client_1, client_2, client_3

I must get 3 records in a separate table:

doc_id : doc_A
doc_client : client_1

doc_id : doc_A
doc_client : client_2

doc_id : doc_A
doc_client : client_3

Could you please help me on that problem.

Rds
Marco

 

[John Vinson]

doc_id : doc_A
doc_client : client_1, client_2, client_3

I must get 3 records in a separate table:

doc_id : doc_A
doc_client : client_1

doc_id : doc_A
doc_client : client_2

doc_id : doc_A
doc_client : client_3

Could you please help me on that problem.

A "Normalizing Union Query" is the ticket here. Let's assume you have
either linked or imported the spreadsheet as tblSource, and that you
have a properly normalized table with the two fields (or perhaps more,
the logic is the same) named tblDest.

Create a Query in the SQL window (the query grid can't do it):

SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_1 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_2 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_3 IS NOT NULL;

Save this query as uniAllClients. Then base an Append query on it to
populate tblDest:

INSERT INTO tblDest
(Doc_ID, Doc_Client)
SELECT Doc_ID, Doc_Client
FROM uniAllClients;


John W. Vinson[MVP]

 

[Guest]

HI,

Many thanks for your help. The problem is that in my document report, all
clients are specified in only one field, doc_client, separated by a comma :
customer_a, customer_b, customer_c, .... They are not into separated fields
client_1, client_2, ...

That's the key point here.

Rds
Marco

doc_id : doc_A
doc_client : client_1, client_2, client_3

I must get 3 records in a separate table:

doc_id : doc_A
doc_client : client_1

doc_id : doc_A
doc_client : client_2

doc_id : doc_A
doc_client : client_3

Could you please help me on that problem.

A "Normalizing Union Query" is the ticket here. Let's assume you have
either linked or imported the spreadsheet as tblSource, and that you
have a properly normalized table with the two fields (or perhaps more,
the logic is the same) named tblDest.

Create a Query in the SQL window (the query grid can't do it):

SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_1 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_2 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_3 IS NOT NULL;

Save this query as uniAllClients. Then base an Append query on it to
populate tblDest:

INSERT INTO tblDest
(Doc_ID, Doc_Client)
SELECT Doc_ID, Doc_Client
FROM uniAllClients;


John W. Vinson[MVP]

 

[BD]

Do an import before into a table and named it "tblSource" and after
that do what John Vinson said.

[]'s
BD

 

[Douglas J. Steele]

Is it always 3 records in doc_client?

If so, you should be able to use

SELECT Doc_ID, Trim(Split(Client_1, ",")(0)) AS [Doc_Client]
FROM tblSource
UNION ALL
SELECT Doc_ID, Trim(Split(Client_1, ",")(1)) AS [Doc_Client]
FROM tblSource
UNION ALL
SELECT Doc_ID, Trim(Split(Client_1, ",")(2)) AS [Doc_Client]
FROM tblSource

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI,

Many thanks for your help. The problem is that in my document report, all
clients are specified in only one field, doc_client, separated by a comma
:
customer_a, customer_b, customer_c, .... They are not into separated
fields
client_1, client_2, ...

That's the key point here.

Rds
Marco

doc_id : doc_A
doc_client : client_1, client_2, client_3

I must get 3 records in a separate table:

doc_id : doc_A
doc_client : client_1

doc_id : doc_A
doc_client : client_2

doc_id : doc_A
doc_client : client_3

Could you please help me on that problem.

A "Normalizing Union Query" is the ticket here. Let's assume you have
either linked or imported the spreadsheet as tblSource, and that you
have a properly normalized table with the two fields (or perhaps more,
the logic is the same) named tblDest.

Create a Query in the SQL window (the query grid can't do it):

SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_1 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_2 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_3 IS NOT NULL;

Save this query as uniAllClients. Then base an Append query on it to
populate tblDest:

INSERT INTO tblDest
(Doc_ID, Doc_Client)
SELECT Doc_ID, Doc_Client
FROM uniAllClients;


John W. Vinson[MVP]

 

[Douglas J. Steele]

I just realized that John Nurick's "SafeSplit" function would be useful
here if you don't always have exactly 3 records.

The function is defined as:

Public Function SafeSplit(V As Variant, _
Delim As String, Item As Long) As Variant

On Error Resume Next 'to return Null if Item is out of range
SafeSplit = Split(V, Delim)(Item)
End Function

You'd then change your query to:

SELECT Doc_ID, Trim(SafeSplit(Client_1, ",", 0)) AS [Doc_Client]
FROM tblSource
UNION ALL
SELECT Doc_ID, Trim(SafeSplit(Client_1, ",", 1)) AS [Doc_Client]
FROM tblSource
UNION ALL
SELECT Doc_ID, Trim(SafeSplit(Client_1, ",", 2)) AS [Doc_Client]
FROM tblSource



--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

Is it always 3 records in doc_client?

If so, you should be able to use

SELECT Doc_ID, Trim(Split(Client_1, ",")(0)) AS [Doc_Client]
FROM tblSource
UNION ALL
SELECT Doc_ID, Trim(Split(Client_1, ",")(1)) AS [Doc_Client]
FROM tblSource
UNION ALL
SELECT Doc_ID, Trim(Split(Client_1, ",")(2)) AS [Doc_Client]
FROM tblSource

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)

HI,

Many thanks for your help. The problem is that in my document report, all
clients are specified in only one field, doc_client, separated by a comma
:
customer_a, customer_b, customer_c, .... They are not into separated
fields
client_1, client_2, ...

That's the key point here.

Rds
Marco

On Wed, 14 Jun 2006 10:19:02 -0700, deco

doc_id : doc_A
doc_client : client_1, client_2, client_3

I must get 3 records in a separate table:

doc_id : doc_A
doc_client : client_1

doc_id : doc_A
doc_client : client_2

doc_id : doc_A
doc_client : client_3

Could you please help me on that problem.

A "Normalizing Union Query" is the ticket here. Let's assume you have
either linked or imported the spreadsheet as tblSource, and that you
have a properly normalized table with the two fields (or perhaps more,
the logic is the same) named tblDest.

Create a Query in the SQL window (the query grid can't do it):

SELECT tblSource.Doc_ID, tblSource.Client_1 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_1 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_2 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_2 IS NOT NULL
UNION ALL
SELECT tblSource.Doc_ID, tblSource.Client_3 AS [Doc_Client]
FROM tblSource
WHERE tblSource.Client_3 IS NOT NULL;

Save this query as uniAllClients. Then base an Append query on it to
populate tblDest:

INSERT INTO tblDest
(Doc_ID, Doc_Client)
SELECT Doc_ID, Doc_Client
FROM uniAllClients;


John W. Vinson[MVP]

 

[Guest]

HI,

It did work perfectly. Many thanks y'all for your help.

Rds
Marco