How to partition a table into two linked tables

[adrian.stock22]

Hi, All,

I have so far only worked with single tables and queries but not with linked tables. Now the following problem has arisen.

We have a database all of whose fields except two are of type = Text. Two fields are type = Memo.

We have to submit this database, i.e. its main table, to an external company for automatic 'cleansing' (UK: Telephone preference service). The cleansing program converts the cleansed database into a comma-delimited text file and returns it to us as such, and we then have to convert it back into an Access table.

(That's the way our supplier works, it is not under our control, and we have to learn to cope with it.)

Now the cleansing program is being screwed up by our Memo fields, because they contain commas. So when the cleansing program sees a comma, it interprets this as the start of a new field, and the same happens when we convert the text file back into a table.

Two solutions were suggested by the cleansing company, but only the following seems suffiently automatic and therefore acceptable to us.

I NEED HELP IN IMPLEMENTING THAT SOLUTION.

-----------------------------

I want to move the two memo columns into a separate table (memo table), linked to the original table (main table). Have the ID column (unique identifier) in both tables to keep the records together. Then I send only the main table for cleansing and the commas in the memo can no longer work havoc.

I have an idea of how to create the memo table: make a copy of the main table and then delete all columns except ID and the two memo columns.

But then I have to link the two tables (I do not know how to do that); where do I start.

I must also ensure that when new records are created (now usually through a form), both tables are expanded. And I must get information from both tables into one form.

At present I do not even know where to start, except for creating the Memo table by copying it and deleting certain columns.

I have a good book (John Viescas: Running MS Access 2000), but it is huge, and the problem is urgent (it stops our tiny company from marketing). I have no time to study the whole book to find what I need in this case. A chapter or page reference would be very useful.

Or some Tutorial on the web that deals with my particular task.

Thanks for your help.

Adrian

 

[RuralGuy]

Have you ever created an Action query (INSERT, UPDATE, etc.)?

 

[adrian.stock22]

Adrian replied to that

No, I have never used Action Queries. Will look in book for it. How will they help me in linking these tables? Gives a hint.

I have though, with the help of a friend who has at least a hunch (but only a hunch), succeeded in linking two tables by clicking on the Relationships icon, displaying the field lists for the two tables and then 'drawing a line' between the ID fields in the two tables.

I also managed to create a form which draws on both tables, and it behaves exactly as required.

However then I saw a possibility of further improvement by carving out a third table (so that I now have three tables,

- one with info essential for data cleansing,
- one with our non-memo information,
- and one with our memo information).

So I created that extra table, and renamed all tables except the original memo table, to reflect the current approach.

Now I want to DETACH (OR UNLINK) the memo table in order to establish entirely fresh links between all three tables.

But when clicking on the relationships icon absolutely nothing comes up, no diagram of the linked fields and tables, nothing that I could either link or unlink.

Does anybody know the reason why?

--------------------

I am now thinking of going back to the back-up copy, and doing the whole job again from scratch, this time immediately aiming at the three-table-structure, so that no UNLINKING will be necessary.

Still, I would like to know why I cannot unlink two linked tables. Does the re-naming of one of the linked tables have something to do with it?

Thanks for your help.

Adrian

 

[adrian.stock22]

Hi, Ruralguy,

No, I have never used Action Queries. Will look in book for it. How will it help me in linking these tables? Gives a hint.

I have though, with the help of a friend who has at least a hunch (but only a hunch), succeeded in linking two tables by clicking on the Relationships icon, displaying the field lists for the two tables and then 'drawing a line' between the ID fields in the two tables.

I also managed to create a form which draws on both tables, and it behaves exactly as required.

However then I saw a possibility of further improvement by carving out a third table (so that I now have three tables,

- one with info essential for data cleansing,
- one with our non-memo information,
- and one with our memo information).

So I created that extra table, and renamed all tables except the original memo table, to reflect the current approach.

Now I want to DETACH (OR UNLINK) the memo table in order to establish entirely fresh links between all three tables.

But when clicking on the relationships icon absolutely nothing comes up, no diagram of the linked fields and tables, nothing that I could either link or unlink.

Does anybody know the reason why?

--------------------

I am now thinking of going back to the back-up copy, and doing the whole job again from scratch, this time immediately aiming at the three-table-structure, so that no UNLINKING will be necessary.

Still, I would like to know why I cannot unlink two linked tables. Does the re-naming of one of the linked tables have something to do with it?

Thanks for your help.

Adrian

 

[RuralGuy]

It sounds like you have a good plan. Have fun.