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

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

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" wrote in message
...
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

On Oct 16, 10:34*am, "Jeff Boyce" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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.

On Thu, 16 Oct 2008 08:40:04 -0700 (PDT), R Tanner
wrote:

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.
--

John W. Vinson [MVP]

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

"R Tanner" wrote in message
...
On Oct 16, 10:34 am, "Jeff Boyce" wrote:
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" wrote in message

...



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- Hide quoted text -

- 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.

On Oct 16, 11:43*am, "Jeff Boyce" wrote:
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

"R Tanner" wrote in message

...
On Oct 16, 10:34 am, "Jeff Boyce" wrote:





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