Splitting Tables

[roxdrob]

What is the easiest way to split tables that already have data in them? I have a main table from which I need to extract 2 separate tables. I don't want to lose any of my data that I have already input.



TIA, Rox--

[davegri]

If you need to select some of the rows in a table to use elsewhere in the project, that's what queries can do.
Maybe you need to explain what the 'separate tables' are for, then we can see if queries can satisfy the requirement.
What values decide what rows are extracted?

[roxdrob]

The tables are "Venues" and "Contacts" from the main table "tblCraftShows"

CraftShowsV33.zip

[davegri]

CraftShowsV34.zip
Added a couple of "create table queries" to create tblTestVenue and tblTestContacts.
Queries are examples. I ran the queries and the tables exist.
If you run the queries again, in the new tables you will have to manually create the Primary key, and change the created CraftShowID_PK from autonumber to number and change its name to CraftShowID_FK.
You might want to avoid this and just use the tables I created and change their names (remove the 'test') if they look OK.
These new tables will also need to be set up properly in the relationship window after changing their names to the final name (without the 'test').

[roxdrob]

Hi Dave, all I got was the laacdb file.

[davegri]

Oops. Zipped up the wrong guy. Maybe this one.
CraftShowsV34.zip

[davegri]

CraftShowsV35.zip
This db incorporates the new split out tables.
Also got rid of tbluSpecs - put specname in tblSpecs.
Added subforms for contacts and venues. Getting a little messy with all the subforms.

[roxdrob]

I was thinking the same thing. I wonder if I made a general form, then put buttons for pop-ups - if that would look any better.

[roxdrob]

Is it safe to remove the contact and venue fields from tblCraftShows?

[davegri]

You bet. Any fields moved to another table and shown in any of the subforms doesn't need to be in tblCraftShows.
And I already removed all those unneeded _FK fields.

[roxdrob]

What about doing the pop out forms?

"I was thinking the same thing. I wonder if I made a general form, then put buttons for pop-ups - if that would look any better."

[davegri]

CraftShowsV36.zip
Another approach, to control subform sprawl, would be to put each subform on a tab control. The tab Control would be on the craftshow form. Clicking on each tab would show the corresponding subform, one at a time.

[roxdrob]

would you mind pointing me in the right direction on how to do this? (an article, or something)?

[davegri]

I added the attachment to post #12.

Going out for a few hours. Maybe be back 10PM MDT.

[roxdrob]

This is great! I love it. Thank you!

Now, can I remove the ID's from view? I tried to set "visible" to No, but it didn't change anything.

Also, the State ID shows instead of the state name. How would I change that?

I'm working on a laptop and the page keeps going to the bottom when I change tabs. I've tried changing the size of the table, background and footer, but nothing is happening. Is this something I'll just have to live with?