How do I split a table without the wizard?

[ErikZ]

I'm normalizing an existing table. It's a list of stories and the "Author" can be placed in a new table with a link back to the list of stories.

The Access is useless for this. I've managed to create a table with a list of authors using some queries, but I can't figure out how to link it to the main table.

My goal is, to remove the authors name from the main table, and replace it with an ID number that's linked to the "Authors" table.

It seems like a function that Access should be able to do, or am I missing something?

 

[Lightning]

Step 1:
Use a Make Table query to split the Authors out to the separate table.
Then add your ID field to the Authors table

Step 2:
Use an Update query to update the Authors field in the original table to the Authors ID from the new table.

HTH

Lightning

 

[jazzz]

Do you plan on using a combo box to retreive the author in the future?

If so create your Author_ID field in your main table and make it a long integer. Now click on the Lookup Tab and select Combo Box. Select Row Source click on the eclipse ... and select your authors table and create your query using the PK as the first Column, then the Authors Name in the second I would do some type of sorting here also.

Now click the close button on this build query and select Yes. In column count make this the number to the number of columns you created or you want to show remember the first is the PK which we want but don't really want to show so in column widths make them something like this 0";2.5; or whatever but the first one is 0" so it won't be shown.

Hope this helps?

jaz

 

[ErikZ]

Ahem, this is sort of embarrasing. I missed the checkbox "No, I want to choose the fields myself". when going though the wizard.

It's still not perfect, it doesn't like memo fields, and it will only split out a max of 15 new fields per table.

But with a little tinkering it will work.

I have to do it this way, instead of using an update field, there's over 200 seperate authors, and I'd have to write a query for each one!

 

[Lightning]

ErikZ

NO!!!!!!!!!!!!

An Access Update query will update all the author fields in one run-through, if you specify the criteria using field names not author names.

Read the On-line help topic "Change records as a group using an update query" . This will guide you through each step of setting up your query.

You seem to be doing more than your original post discussed (your mention of memo fields and splitting out "Up to 15 fields to each new table&quot. Exactly what else are you trying to do?

 

[ErikZ]

The original table has ID, Author, title, size, commentary, and about 20 yes/no fields that are "Story Codes".

Looking at the table is annoying, so I wanted to separate out the story codes onto their own table.

Then I wanted to take out the authors and put them in their own table.

I don't know why you're reacting so loudly. I now have list of stories. In it is two fields both are numeric. One links to a table of authors, the other links to a table of story codes.

 

[Lightning]

Sorry Erik, I didn't mean to come on so strong.

From your post it seemed that you were missing the point of using an update query across the whole table. I certainly wasn't trying to upset you.