Splitting a table - how do you do it exactly?

[Maki]

Hi,

I'm trying to re-build our dog training club's membership database, orginally in Access 2003, eventually with A5 v10 but am currently experimenting with v9.

The database currently has three major tables; member, dog and payment. Our membership, however, is per household and as there can be more than one handler in one household, I've decided to separate handler portion from member table.

Sounded easy enough but I can't seem to find instruction on how to split a table into multiple tables within Alpha 5.

Could someone point me to the right direction?

Thank you heaps.

Maki

[CALocklin]

Re: Splitting a table - how do you do it exactly?

There really is no "split the table" routine.

There are probably a lot of missing details here because the details of each specific application are unique. So, here is just a basic outline:

Step 1: MAKE A BACKUP! (This is ALWAYS rule #1.)
Step 1a: (This is my personal rule) Don't delete that backup until the application has been in production for at least a month and preferably longer. Unexpected things can happen - and Murphy says they are more likely to happen if you delete the backup too soon. (Think about that a minute.)
Step 2: Determine what fields need to be moved into a new table.
Step 3: Run a 'Copy' operation* to copy all records from the current table to a new table. (The Copy operation will create the new table.) You will probably want to copy the Field Rules and Indexes as well. (Hmmm, I've never tried that when only copying a subset of the fields - not sure how it will work.)
Step 4: Open the new table to Edit Structure and add any other fields that might be needed. (Maybe none - can't tell without knowing what you have and what you need.)
Step 5: Open the original table and remove the fields that were copied to the new table.

WARNING: There is probably a linking field that was copied to the new table and needs to remain in the original table as well.

*To start a Copy operation, right click on the table in the A5 Control Panel, select Create Operation ..., select Copy Records and follow the instructions.

[Maki]

Re: Splitting a table - how do you do it exactly?

Thank you, Cal, for the posting.

I read your instruction, thought through a little more and figured simple splitting wasn't going to get the results I wanted. Sorry!!:o

The situation is like this:

The memebership table started out as an ordinary one; with fields such as
-Surname
-First name
-Address
-Suburb
-State
-Postcode
-Phone number
..... etc

We soon realised that there were often times when people put more than one record in enrolment application form's fields, such as "Surname", "First name", "Mobile phone no." and "Email". So fields like "Surname 2", "First name 2", and so on, were added to the same table to accommodate secondary handler's info but this, as all database expert can tell, wasn't a smart move and I would like to normalise the table by taking out fields like "Surname", "First name", "Mobile no.", "Email" and put them in another table.

I'd since realised that simple splitting would make the new table with duplicate fields like "Surname" and "Surname 2" and that wasn't what I wanted.

I'm going to have to have records in both "Surname" and "Surname 2" in one field "Surname" in the new table, at the same time add prime and foreign keys to each record.

It might be easier to create a table using MS Excel first...
I still consider myself beginner to databasing and am more comfortable using Excel.

I'd still appreciate other suggestions.

Thanks & cheers,

Maki

[DaveM]

Re: Splitting a table - how do you do it exactly?

Hi Maki,

Having done this a few times,

1 make the backup as stated earlier - most important thing you can do.

2 duplicate your table completely to a new name(indexes, records, etc)

3 delete all unused fields in the new table and the old table. That will leave what you want in each table.

Now: the hard part:

you want one table with an address field(call it what you want) only like 123mainst34448 as a header table. The same field needs to be in each table you use in the set you need to build. Yes this is a bit advanced. Your customer table will connect to the set as a one to one on . Each handler in the handler table will also connect by this number as a one to many. The animals will do the same as a one to many. You can also connect the anilmals to the handlers by way of another field you make on a one to many connection. Same set or a different one.

I would have one table as header with st numb, address, city, st, zip, and connect field. The connect field would be a calculated field like "trim(address)+trim(zip)". this field would connect and fill the other table fields as you add records within the set.

This way your people table is connectable to the header and to the animals. Your animals are connectible to the header and handler depending on the sets. Suggest you read the help files dealing with sets.

The way I laid this out should give you all the flexibility for now and in the future. Or, you could do it other ways.

Please let it be known how it works out?

see attached example? unzip in a folder somewhere?


.