subform/relationship question

  • Thread starter Thread starter gibsonsgman
  • Start date Start date
G

gibsonsgman

Hello, i have a database that i am trying to make run faster.
initially, i had one massive table containing about 130 or so fields.
I recently decided to break up this table into about10 or so smaller
tables and create relationships. i have one form to display all of
this data, and there is a subform for each table. all of my tables
have an 'Equipment_ID' field that i have created relationships between.
if i need to add a new record to a main table, is there a way i can add
that same record to all of the other tables at once? sorry if this is
a really easy question, i do not really understand the relationship
business.
 
I can't tell for sure from your description, but it sounds like you may have
broken your larger table up into smaller tables on some basis other than
normalization. Before you start using relationships to join tables
together, it makes sense to have your tables reflect a well-normalized data
structure.

Could you post your table structure (just a few of them), and an example of
the kind of data you are storing in those you describe?

--
More info, please ...

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Hello, i have a database that i am trying to make run faster.
initially, i had one massive table containing about 130 or so fields.
I recently decided to break up this table into about10 or so smaller
tables and create relationships. i have one form to display all of
this data, and there is a subform for each table. all of my tables
have an 'Equipment_ID' field that i have created relationships
between. if i need to add a new record to a main table, is there a
way i can add that same record to all of the other tables at once?
sorry if this is a really easy question, i do not really understand
the relationship business.
Click to expand...

You should not create entries in the other tables until and unless they need
some of their other fields to be filled in.

Why would you think breaking a big table into 10 tables with 1 to 1
relationships would be any faster? You now have a more complex design than
before and a form with lots of subforms requires much more resources.

When someone tells you that a table has too many fields and could benefit
from normalization what they mean is that you likely have some 1 to many
relationships incorrectly built into the single table. Breaking your design
into multiple tables having 1 to many relationships actually DOES accomplish
something because you are using fewer columns and more rows. Simply
splitting into a bunch of 1 to 1 tables does nothing to improve your design.
It can save some disk space if some of the tables end up not being populated
for every record but that's about it.
 
Rick said:
You should not create entries in the other tables until and unless they need
some of their other fields to be filled in.

Why would you think breaking a big table into 10 tables with 1 to 1
relationships would be any faster? You now have a more complex design than
before and a form with lots of subforms requires much more resources.

When someone tells you that a table has too many fields and could benefit
from normalization what they mean is that you likely have some 1 to many
relationships incorrectly built into the single table. Breaking your design
into multiple tables having 1 to many relationships actually DOES accomplish
something because you are using fewer columns and more rows. Simply
splitting into a bunch of 1 to 1 tables does nothing to improve your design.
It can save some disk space if some of the tables end up not being populated
for every record but that's about it.
Click to expand...

alright, thanks. i don't really understand the one to one, and one to
many relationship idea, but i kind of suspected my table did not need
to be broken up. I just thought i would give it a try since i read it
can speed up a database, but i have decided to just keep the large
table because i think that it is based on one to one relationships
 
As Mr. Brandt said, breaking a huge table up into many small tables
with a 1-to-1 doesn't really help you.

If what you're trying to accomplish by breaking it up is ease of
understanding and use for the enduser, here's what I've done in a very
similar situation: I kept the large table, but used a Master Form with
tabbed subforms for each of the logical groupings of data. That way
the user can add a new record easily and then click around to add the
approriate fields for that record. It helped my users to break the
large number of fields up into logical groupings like that.

Hope that's some help.
Troy
 
alright, thanks. i don't really understand the one to one, and one to
many relationship idea, but i kind of suspected my table did not need
to be broken up. I just thought i would give it a try since i read it
can speed up a database, but i have decided to just keep the large
table because i think that it is based on one to one relationships
Click to expand...

Don't misunderstand. If you have a table with 130 fields then chances are very
good that it *should* be broken up. But it should be broken into tables with
one-to-many relationships, not one-to-one.

For example do you have any repeating fields with names like Something1,
Something2, etc., or fields that store data for each day, or month, or year?
Any time you have multiple fields with the same kind of data in them and the
only thing that distinguishes them is the name of the field they are stored in
then that is a red flag.
 
I'll back Rick on this. 130 fields in a single table sounds more like a
spreadsheet than a relational database table design. Again, if you'll post
a description of the fields and their contents (even a partial one -- I
don't need to see 130 fields!), the newsgroup may be able to offer alternate
approaches.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
NOpe, all of my fields in the table are unique believe it or not.
ALso, I already do have them separated into logical tabs. I was just
searching for a way to speed up the operation of my database because of
the massive amount of controls on the one form.
 
I don't think we're talking about the same thing.

Having "unique" fields implies, to me, that the same data element is not
stored more than one time, in a single field.

The relational model Rick & I have been mentioning has to do with the
natural "groups" of data that exist (and not how your current table stores
the data).

For example, you could create an Order Fulfillment system using a "wide"
table that held OrderDate, PersonOrdering, OrderStreetAddress, ...,
OrderItem1, OrderItem1Price, OrderItem1Quantity, OrderItem2,
OrderItem2Price, ... This would be how you'd pretty much HAVE to do it in
an Excel spreadsheet, but totally misses what Access offers.

Once again, if you'll post an example of some of the names of your fields,
and the type of data you are storing in them, the newsgroup may be able to
offer an approach that proves both easier to maintain in Access, AND makes
better use of the features and functions of Access.

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
Alright here are soem of my fields in my table: (each field separated
by semicolon)

Fields:

Equipment_ID; System; Actuator_Manufacurer; Valve_Manufacturer;
Positioner_Manufacturer; Packing_Dimensions; Tech_Manual;
Testing_Needed


Values in Fields:

in the equipment_id field there are values such as:
AO-2374; CV-1001; CV-1012; PCV-7939
in the system field there are values such as:
CRD; TRB; CFW
in the Actuator_Manufacturer field there are values such as:
BS&B; Fisher, Masoneilan
Valve_Manufacture and Positioner_Manufacturer have same values as Act.
in Packing Dimensions field there are values such as:
0.875"x1.500"
in the Tech_Manual field there are values such as:
NX-32821-1
Testing-Needed field is Yes/No

There are some of my fields in my table. On my form i have logical
tabs separated between some of the different field types i.e. actuator,
valve, positioner, testing, packing etc.

hope this makes my issue a bit more clear
 
oh yeah, i forgot to mention, i don't know if this is relevant but the
primary key is Equipment_ID and they are all unique records, and the
large amount of fields are necessary to comletely describe each piece
of equipment.
 
It would appear that your table has repeating fields (you mention
xxx_manufacturer, yyy_manufacturer, zzz_manufacturer). You confirm this by
pointing out that the "Valve_... and Positioner... have the same values as
Act.".

If you want to make easy and complete use of the features and functions
offered by Access, your data needs to be in a form that Access expects
(i.e., well-normalized relational tables). If you don't need Access
features and functions, consider using a spreadsheet to do what you need
to...

--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 
what do you mean by repeating fields? because those fields that i
mentioned are by no means describing the same thing. each of those
fields describe a unique piece of the equipment and each field is not
necessarily the same accross. (i.e. as mentioned before if
positioner_manufacturer=BS&B that does not mean that
valve_Manufacturer=BS&B). I guess what i am wondering is how exactly
are these repeating fields?
 
In a spreadsheet, pretty much the only way to handle, for example, the
manufacturer of equipment type 1, type 2, and type3, is to use "repeating
fields" (Equip1_Manufacturer, Equip2_Manufacturer, Equip3_Manufacturer).
See the "repeating" pattern?

In Access, you can use a table of "Manufacturers", and a table of
"EquipmentType", and use the IDs from these to show combinations of
equipment & manufacturer. The table, along with other fields, would
include:

xxxTable
EquipmentTypeID
ManufacturerID
...

Why bother? Because with a three-table design, when you add a new
manufacturer, or a new equipment type, you don't have to revise your
"repeating fields" table, nor your queries, nor your forms, nor your ...
(entire Access database!). Instead, add a new entry in the tblManufacturer
or the tblEquipmentType. That's it!

I suspect there are other opportunities to put your data into a form Access
can better handle. Check into "normalization" in Access HELP, at mvps.org,
and at Jeff Conrad's website:

http://home.bendbroadband.com/conradsystems/accessjunkie.html


--
Regards

Jeff Boyce
Microsoft Office/Access MVP


Microsoft IT Academy Program Mentor
http://microsoftitacademy.com/

Microsoft Registered Partner
https://partner.microsoft.com/
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Disappeared Relationships; Can't Recreate 2
linking tables and relationships 3
Implementing a many-to-many relationship 4
relationships 1
Relationship Help with Tables 1
Object dependencies vs. relationships 4
Please help - I need to separate data to two tables using one form. 0
Where should relationships be created? 4

Back
Top