Table Splitting Questions

[Sammie]

I have a table with way too many fields, and I am trying to use the table
analyzer to split it. I'm not sure how to approach this, and I'd appreciate
some guidance.

I would like to break off a group of fields into a table called “ShipToâ€.
So I separated my group of fields such as companyname, address, city,
country, etc., created the new table “shiptoâ€, and created the query
generated by the wizzard so my queries and forms continue to work. My
questions:

1. Does the new “shipto†table eliminate duplicates? And if so, what
constitutes a duplicate record? (Eliminating duplicates is the whole point,
right?)
2. When I add new records on my invoice form and enter data into to the
“Shipto†fields, how can I look up an existing record on the shipto table so
duplicates are not created? I'm thinking limit to list/not in list event,
but is it a subform, a list box, a combo box, or can I just use text boxes
like I have now? There are about 15 fields on the shipto table that need to
be populated on the shipto page of my invoice form. Usually, I will use the
whole record with all 15 fields.
3. If I pull a shipto record over to the invoice form, and I want to change
just 1 field in the shipto record (for example, a contactname), how do I
update the shipto table with the change?
4. Is it ok in the long run to leave Table1 and the new query named the
same as my old table? Or should I make a table out of the new query, name it
the same as the old table and eliminate Table1? Are relationships involved
here? I am worried that Table1 might get deleted along the way because of
its ambiguous name, which of course would be disastrous. My database is
complex and I am concerned about its integrity as I make these changes.
5. I am going to need to split my table again. Will I then have table2 and
a query named like the old table? What happens to table1 then? Can I delete
it?

 

[TonyT]

Hi Sammie,

From your description, it sounds like you still have to much information in
one or both tables. Each table should only contain data that is relevant to
its Primary Key, so I your example 'Contact Name' is dependant on 'ShipTo',
but 'ShipTo' is not dependant on Contact (you can have more than 1 contact at
any 1 ShipTo address) so Contact should have it's own table linked to the
ShipTo table 1 to many.

Ignoring that for now, I'll answer the following questions inline;

I have a table with way too many fields, and I am trying to use the table
analyzer to split it. I'm not sure how to approach this, and I'd appreciate
some guidance.

Only you know the 'data model' you are using, so try to understand the basic
principles of relational database design so you umderstand what the analyzer
is suggesting and how it will affect the use of your design.

I would like to break off a group of fields into a table called “ShipToâ€.
So I separated my group of fields such as companyname, address, city,
country, etc., created the new table “shiptoâ€, and created the query
generated by the wizzard so my queries and forms continue to work. My
questions:

CompanyName should remain in the main table unless it is the foreign key in
the ShipTo table.

1. Does the new “shipto†table eliminate duplicates? And if so, what
constitutes a duplicate record? (Eliminating duplicates is the whole point,
right?)

Look at the table design and see which fields have Indexed(No Duplicates)
set to yes, none of these fields will allow duplications of that data. You
presumably have multiple ShipTo addresses for any 1 Business, each one of
these addresses should have a single record each.

2. When I add new records on my invoice form and enter data into to the
“Shipto†fields, how can I look up an existing record on the shipto table so
duplicates are not created? I'm thinking limit to list/not in list event,
but is it a subform, a list box, a combo box, or can I just use text boxes
like I have now? There are about 15 fields on the shipto table that need to
be populated on the shipto page of my invoice form. Usually, I will use the
whole record with all 15 fields.

You can use a ComboBox whose rowsource is the ShipTo table to list all the
available addresses for the selected Business in the main form or a subform.

3. If I pull a shipto record over to the invoice form, and I want to change
just 1 field in the shipto record (for example, a contactname), how do I
update the shipto table with the change?

Keep the ShipTo info in a bound subform (bound to ShipTo table) and as you
update it, the changes will be saved to the underlying table.

4. Is it ok in the long run to leave Table1 and the new query named the
same as my old table? Or should I make a table out of the new query, name it
the same as the old table and eliminate Table1? Are relationships involved
here? I am worried that Table1 might get deleted along the way because of
its ambiguous name, which of course would be disastrous. My database is
complex and I am concerned about its integrity as I make these changes.

Not good practice to a leave any object named as 'TableX' or 'FormX' as it
makes future upkeep very timeconsuming - try to use as meaningfull names as
possible (without spaces)
b having more than one object with the same name, neither you nor access
will know which 1 you are referring to.

Yes, relationships should be involved now you have split, the ShipTo table
should be joined to your Table1

Unfortunately, you will need to change any forms and queries that are based
on the tables who's name you change.

5. I am going to need to split my table again. Will I then have table2 and
a query named like the old table? What happens to table1 then? Can I delete
it?

I'm afraid it sounds like you have a lot of work to do to get where you want
to be, it might be worth considering starting over with properley normalised
tables, which should give you plenty of scope to change things later on. I
describe it like a building, if the foundations are wrong (too few or
unnormalised tables) and you try to build a solution on top of those
foundations it will soon fall over, get the foundations right and you can
build what you desire!

TonyT..

 

[Jeff Boyce]

Sammie

Access is a relational database. The features/functions Access offers
"expect" well-normalized data.

If neither of these terms is familiar ("normalized", "relational"), plan on
spending some time working your way up the learning curve.

If you try to treat Access like a herky spreadsheet, you will "pay now AND
pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sammie]

Dear Jeff,

The task of starting over is daunting. I use this database to run my small
business (read: can't afford to hire a consultant), including generating all
invoices and purchase orders. It has taken me 5 years to develop, and I am
self-taught (which explains why I am not normalized as I should be). I based
my database on the Northwind database, learning as I transferred the design
to my own.

I have the following main tables: invoices, purchaseorders, products (links
to both invoices and purchaseorders), customers, suppliers, payments. There
are many other tables as well, and some of them are normalized! I debug and
compact/repair regularly, and have multiple backups. Size of the backend is
36 mb, size of the front end is 40 mb. There are 5 users. I know from
renaming an object that many useful forms and reports are compromised, so my
relationships are complicated.

This database is not my only responsibility. I am not a full-time systems
manager.

Can I get good results by splitting my tables with the wizzard and
re-designing the database as I work with the new structure? I do not want to
re-design my finely tuned reports.

As you can see, I do not want to start over. I understand the pickle I am
in. I need to continue to use my database as the redesign is underway. If
starting over is the only way out, how can I copy what I have already learned
and created into the new design?

I am most grateful for your guidance.
--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7

Sammie

Access is a relational database. The features/functions Access offers
"expect" well-normalized data.

If neither of these terms is familiar ("normalized", "relational"), plan on
spending some time working your way up the learning curve.

If you try to treat Access like a herky spreadsheet, you will "pay now AND
pay later"...

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[Sammie]

Thank you, Tony for your detailed response. You turned on a light bulb with
"Each table should only contain data that is relevant to its Primary Key". I
am pondering re-design versus starting over. See my post to Jeff.
--
Thanks.
Sammie SBS 2003, OFFICE 2003, XP-PRO-SP3, IE7

Hi Sammie,

From your description, it sounds like you still have to much information in
one or both tables. Each table should only contain data that is relevant to
its Primary Key, so I your example 'Contact Name' is dependant on 'ShipTo',
but 'ShipTo' is not dependant on Contact (you can have more than 1 contact at
any 1 ShipTo address) so Contact should have it's own table linked to the
ShipTo table 1 to many.

Ignoring that for now, I'll answer the following questions inline;

I have a table with way too many fields, and I am trying to use the table
analyzer to split it. I'm not sure how to approach this, and I'd appreciate
some guidance.

Only you know the 'data model' you are using, so try to understand the basic
principles of relational database design so you umderstand what the analyzer
is suggesting and how it will affect the use of your design.

I would like to break off a group of fields into a table called “ShipToâ€.
So I separated my group of fields such as companyname, address, city,
country, etc., created the new table “shiptoâ€, and created the query
generated by the wizzard so my queries and forms continue to work. My
questions:

CompanyName should remain in the main table unless it is the foreign key in
the ShipTo table.

1. Does the new “shipto†table eliminate duplicates? And if so, what
constitutes a duplicate record? (Eliminating duplicates is the whole point,
right?)

Look at the table design and see which fields have Indexed(No Duplicates)
set to yes, none of these fields will allow duplications of that data. You
presumably have multiple ShipTo addresses for any 1 Business, each one of
these addresses should have a single record each.

2. When I add new records on my invoice form and enter data into to the
“Shipto†fields, how can I look up an existing record on the shipto table so
duplicates are not created? I'm thinking limit to list/not in list event,
but is it a subform, a list box, a combo box, or can I just use text boxes
like I have now? There are about 15 fields on the shipto table that need to
be populated on the shipto page of my invoice form. Usually, I will use the
whole record with all 15 fields.

You can use a ComboBox whose rowsource is the ShipTo table to list all the
available addresses for the selected Business in the main form or a subform.

3. If I pull a shipto record over to the invoice form, and I want to change
just 1 field in the shipto record (for example, a contactname), how do I
update the shipto table with the change?

Keep the ShipTo info in a bound subform (bound to ShipTo table) and as you
update it, the changes will be saved to the underlying table.

4. Is it ok in the long run to leave Table1 and the new query named the
same as my old table? Or should I make a table out of the new query, name it
the same as the old table and eliminate Table1? Are relationships involved
here? I am worried that Table1 might get deleted along the way because of
its ambiguous name, which of course would be disastrous. My database is
complex and I am concerned about its integrity as I make these changes.

Not good practice to a leave any object named as 'TableX' or 'FormX' as it
makes future upkeep very timeconsuming - try to use as meaningfull names as
possible (without spaces)
b having more than one object with the same name, neither you nor access
will know which 1 you are referring to.

Yes, relationships should be involved now you have split, the ShipTo table
should be joined to your Table1

Unfortunately, you will need to change any forms and queries that are based
on the tables who's name you change.

5. I am going to need to split my table again. Will I then have table2 and
a query named like the old table? What happens to table1 then? Can I delete
it?

I'm afraid it sounds like you have a lot of work to do to get where you want
to be, it might be worth considering starting over with properley normalised
tables, which should give you plenty of scope to change things later on. I
describe it like a building, if the foundations are wrong (too few or
unnormalised tables) and you try to build a solution on top of those
foundations it will soon fall over, get the foundations right and you can
build what you desire!

TonyT..

 

[Jeff Boyce]

Sammie

Not sure if this answers your questions...

You can have Access "look" for its data where-ever you choose. Even if you
split your application and put the data in a new location, you can still
"link" to that data, use the same table names, and all your queries,
functions, reports, etc. should continue to work (one small wrinkle happens
in the naming if you link to a SQL-Server back-end instead of an Access
backend, but the new (linked) table names can be modified to reflect the old
namings).

Then, if you create a second copy of the data and the front-end, and use
that as your development version, you can work on the normalization that
will give you greater use and ease of use for Access' features/functions.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Dear Jeff,

The task of starting over is daunting. I use this database to run my
small
business (read: can't afford to hire a consultant), including generating
all
invoices and purchase orders. It has taken me 5 years to develop, and I
am
self-taught (which explains why I am not normalized as I should be). I
based
my database on the Northwind database, learning as I transferred the
design
to my own.

I have the following main tables: invoices, purchaseorders, products
(links
to both invoices and purchaseorders), customers, suppliers, payments.
There
are many other tables as well, and some of them are normalized! I debug
and
compact/repair regularly, and have multiple backups. Size of the backend
is
36 mb, size of the front end is 40 mb. There are 5 users. I know from
renaming an object that many useful forms and reports are compromised, so
my
relationships are complicated.

This database is not my only responsibility. I am not a full-time systems
manager.

Can I get good results by splitting my tables with the wizzard and
re-designing the database as I work with the new structure? I do not want
to
re-design my finely tuned reports.

As you can see, I do not want to start over. I understand the pickle I am
in. I need to continue to use my database as the redesign is underway.
If
starting over is the only way out, how can I copy what I have already
learned
and created into the new design?

I am most grateful for your guidance.

 

[Sammie]

Thanks for your reply. This all started from getting the error "too many
fields defined" recently. Other than that, my database has been working
great. So I want to fix the current problem and prevent future ones by
working in the direction of a normalized database, gradually.

Given that I have some key tables with WAY TOO MANY fields, what I really
want to know is if it is possible to effectively normalize my database by
using the table analyzer to split my tables or not?

 

[John W. Vinson]

Given that I have some key tables with WAY TOO MANY fields, what I really
want to know is if it is possible to effectively normalize my database by
using the table analyzer to split my tables or not?

The table analyzer is one automated way. But a MUCH BETTER way is to use the
USB interface... Using Someone's Brain, preferably yours!

You presumably understand the nature of the information represented in your
table(s) better than any mindless computer program possibly could. I'd really
suggest putting a nice Christmas or holiday screen saver up on your monitor,
stepping away from the keyboard, and getting a #2 pencil (with a good eraser)
and a pad of paper. On that paper, draw out an "entity relationship diagram".

Identify the Entities - real-life people, things, or events - of importance to
your application. Each kind of Entity will have Attributes - distinct "chunks"
of information that you need to know about the Entity. For example, a Person
is a kind of entity; a person might have attributes such as LastName,
FirstName, Title, DateOfBirth, and on and on; you'll need to decide which
attributes of a person you need to track.

Each kind of Entity gets a Table; each relevant attribute becomes a Field in
that table.

You then need to identify how the tables relate to one another. Use phrases
like "Each Purchase Order is issued for one and only one Customer; each
Customer can have zero, one or many Purchase Orders". This defines a one to
many relationship from Customers to Purchase Orders.

If you find yourself needing repeating attributes (e.g. Invoice1, Invoice2,
Invoice3...) then you've found a need for a new one to many relationship.

By all means use your existing tables *as a source of information* and ideas
for the design, but don't slavishly try to include your current table
structure in the new tables!

You'll be able to run multiple Append queries to migrate the data from your
current tables into the new ones.


By all means, try the analyzer... but don't trust it excessively; you're a lot
smarter than it is!