Splitting a Table with 15 Columns into Separate Tables Vs. Queries

[R Tanner]

Hi,

Is there any disadvantage/advantage to splitting a large table into
smaller tables versus just querying one table based on what you want?
I am having trouble splitting my table - it's telling me I have to
increase my maxlocksperfile so I just figured I would run queries
instead but I wanted to see if I could get any feedback on any
features or anything I will be missing out on.

I'm new to access by the way.

Thanks

 

[Jeff Boyce]

Access tables are not like Excel spreadsheets, even if they look similar.

A decision to move data to a different table (or add fields to an existing
table) has less to do with convenience (to you, to users), and more to do
with the efficiencies that a well-normalized design can offer when using
Access' relationally-oriented features/functions.

If "normalization" and "relational" are not familiar terms, plan on spending
some time coming up to speed on them before you get good use of Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[R Tanner]

Access tables are not like Excel spreadsheets, even if they look similar.

A decision to move data to a different table (or add fields to an existing
table) has less to do with convenience (to you, to users), and more to do
with the efficiencies that a well-normalized design can offer when using
Access' relationally-oriented features/functions.

If "normalization" and "relational" are not familiar terms, plan on spending
some time coming up to speed on them before you get good use of Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

I have read quite a bit on it...From what I understand, to maintain
the integrity of the data, it would be better to divide my one table
into say, 5 tables so that if something changes in my parent table,
because of referential integrity, that change will be duplicated
throughout it's child table. The problem I am having is that I cannot
divide my one table into multiple tables with the table analyzer
because it is giving me a MaxLocksPerFile error.

 

[John W. Vinson]

Hi,

Is there any disadvantage/advantage to splitting a large table into
smaller tables versus just querying one table based on what you want?
I am having trouble splitting my table - it's telling me I have to
increase my maxlocksperfile so I just figured I would run queries
instead but I wanted to see if I could get any feedback on any
features or anything I will be missing out on.

I'm new to access by the way.

Thanks

Only if it's logical to do so. Each Table should refer to a particular type of
Entity - real-life person, thing, or event. Each example of that Entity should
be modeled by a record in the table; each field in the table should refer to
an Attribute (a distinct, atomic, nonrepeating chunk of information) of that
entity.

The only reason to split a table would be if it in fact muddles information
about two different kinds of entities. For example, if you have a table of
Employees and it contains fields for VacationTaken, you have two different
kinds of entities - the date of a vacation trip is NOT an attribute of an
employee, since one employee might take more than one vacation.

Splitting tables just for the sake of splitting them is not a good idea; and
using Cascade Updates probably does NOT do what you're assuming that it does!
Perhaps you could post a description of your table (fieldnames, datatypes, and
what information the fields contain).

To get around your problem - IF you in fact need to split the tables, I'd
create new, empty tables with the desired fields and datatypes, and then run
Append queries to populate them.

 

[Jeff Boyce]

You are asking a specific question ("should I use all-season radials?")
without providing specific information ("on my 1973 Corvette, in Phoenix,
AZ").

Until we understand what data is being stored in those columns, telling you
to use multiple tables (or not) is moot.

Regards

Jeff Boyce
Microsoft Office/Access MVP

Access tables are not like Excel spreadsheets, even if they look similar.

A decision to move data to a different table (or add fields to an existing
table) has less to do with convenience (to you, to users), and more to do
with the efficiencies that a well-normalized design can offer when using
Access' relationally-oriented features/functions.

If "normalization" and "relational" are not familiar terms, plan on
spending
some time coming up to speed on them before you get good use of Access.

Regards

Jeff Boyce
Microsoft Office/Access MVP









- Show quoted text -

I have read quite a bit on it...From what I understand, to maintain
the integrity of the data, it would be better to divide my one table
into say, 5 tables so that if something changes in my parent table,
because of referential integrity, that change will be duplicated
throughout it's child table. The problem I am having is that I cannot
divide my one table into multiple tables with the table analyzer
because it is giving me a MaxLocksPerFile error.

 

[R Tanner]

You are asking a specific question ("should I use all-season radials?")
without providing specific information ("on my 1973 Corvette, in Phoenix,
AZ").

Until we understand what data is being stored in those columns, telling you
to use multiple tables (or not) is moot.

Regards

Jeff Boyce
Microsoft Office/Access MVP








I have read quite a bit on it...From what I understand, to maintain
the integrity of the data, it would be better to divide my one table
into say, 5 tables so that if something changes in my parent table,
because of referential integrity, that change will be duplicated
throughout it's child table.  The problem I am having is that I cannot
divide my one table into multiple tables with the table analyzer
because it is giving me a MaxLocksPerFile error.- Hide quoted text -

- Show quoted text -

Okay...I have two different posts on this and you have already
responded to the one Jeff...Here is what I said in the other post...


The data is right now in one big table called Tickets.
In this Tickets table, I have a series of drop down fields with
specific options to choose from. These are the drop down fields:

1. Issue
2. System Adjustment
3. Manager
4. Resolved By
5. Satisfied
6. Accountability
7. Supervisor Initials


After these fields, I have the following:


1. Start Time
2. End Time
3. Description
4. If Other (This goes with Issue - There is an 'Other' option)
5. Credit
6. Adjusted Price
7. Account
8. Account
9. Original Details
10. Resolution


Now what I want to do is have this data stored in the back end and
have a front end for each specialist to enter new records into. To
organize the data and maintain the integrity of it from here on out,
I
am under the impression I should have parent tables. With that
understanding, I wanted to split up all of the drop down fields into
other tables.


It is also important to know that, although for the drop down field
'Issue' listed above there are only currently about 10 - 15 choices
for specialists to choose from, there have been a total of 670
different entries in this field over the past 2 years. What I did
with this was I split the tickets table and I have a relationship
between it and my issues table with a boolean column I labeled
current
in the issues table. If the issue is currently one we use, then the
box is checked.

The data is not normalized. I was trying to do that with the table
analyzer. It doesn't work. Tells me to increase the MaxLocksPerFile
in the registry.