Split 1 record to multiple records in another table

[infoweb]

Hi there,

Wondering if anyone can help with this issue?

I have 2 Tables (Table A) and (Table B )

Records in Table A have the following format:

Record 1:

Transaction ID (unique) = 1

Description =blah balh

Price = 10

Cost = 5

What I need to do is gsplit each record from Table A into Table B so I can then export them once they are reformatted. The format I need is -

Record 1:

Transaction ID = 1

Description = blah blah

Price = 10

Record 2:

Transaction ID = 1

Description = blah blah

Cost = 5

There are few things I should point out.

The data in Table A is imported from a CSV file on a regular basis which often includes duplicate data (we easily deal with this using matching fields).

We're using this database for 2 purposes:

1. as a reporting tool

2. as a way to reformat the data ready to import into an external accounting package

What I'm trying to achieve is that each time we import the data into Table A at the same time Table B is updated with a split record version of the data. Table B would always have double the number of records than Table A since we always need to split each record into 2.

Any suggestions?

Thanking you in advance.

[David McQueen]

You can create a scratch table whose sole purpose is to import from table A and then act as a repository to table B.

It would go something like this:

Import new records to table A.

Go to Table Scratch Layout.

Delete all records in Table Scratch

Import ID, Description and Price from table A

Import ID Description and Cost from Table A

Go toTable B

Import Table Scratch from Table B

HTH

Dave

[comment]

What format is your export going to use? It seems like the two "half-records" need to be adjacent and in a given order?

[infoweb]

Thanks for the replies. They're much appreciated.

@Dave

Sounds like that will do the trick, will give it a try and report back.

@comment

The final export will be a csv file. the csv needs each transaction to have it's own line but it can share a common ID. Basically it builds and invoice / po line by line.

[comment]

each transaction to have it's own line but it can share a common ID.

But what distinguishes between the types of line? IOW, when you see:

1,"blah blah",10

how can you tell if 10 is the cost or the price?