By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
464,760 Members | 1,120 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 464,760 IT Pros & Developers. It's quick & easy.

How to break up table?

P: 83
I have a table with about 80 fields and 400 lines. I separated the tables into a database that sits on a server and all people have a copy of the access program with forms, tables, and reports on their desktop. The remote access connects to the server, it's pretty simple.
I want to improve speed.
So far, I have included onload events in a form that define the record source and set for the main source and combo boxes. I also clear them on exit. Both of these improved speed. The most common form uses about 30 fields and the other form uses about 30. Some fields overlap.
As a separate issue, but which I think is related:
Lets say there are 5 clients and each client has 6 fields that are associated with it. Every line of the database therefore has 30 fields that are associated with the 5 clients. Not every line will have all client fields filled out, actually on a form, there is a program that I wrote that determines which clients will be used for each line. The problem arises when I want to create a report by clients. So far, I have one query that selects a line if any one of the clients has one field filled out(the field is the first in the sequence of the 6). Then I create separate reports that filter for each client, then I create one report with all the subreports because otherwise, I would have to include a report with 30 variables.
Is this the most efficient way, if not, will breaking up the tables help this situation. Thanks
May 22 '07 #1
Share this Question
Share on Google+
2 Replies

P: 83
Does anyone have any advice?
May 23 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
I have a table with about 80 fields and 400 lines. I separated the tables into a database that sits on a server and all people have a copy of the access program with forms, tables, and reports on their desktop. The remote access connects to the server, it's pretty simple.
I want to improve speed.
So far, I have included onload events in a form that define the record source and set for the main source and combo boxes. I also clear them on exit. Both of these improved speed. The most common form uses about 30 fields and the other form uses about 30. Some fields overlap.
As a separate issue, but which I think is related:
Lets say there are 5 clients and each client has 6 fields that are associated with it. Every line of the database therefore has 30 fields that are associated with the 5 clients. Not every line will have all client fields filled out, actually on a form, there is a program that I wrote that determines which clients will be used for each line. The problem arises when I want to create a report by clients. So far, I have one query that selects a line if any one of the clients has one field filled out(the field is the first in the sequence of the 6). Then I create separate reports that filter for each client, then I create one report with all the subreports because otherwise, I would have to include a report with 30 variables.
Is this the most efficient way, if not, will breaking up the tables help this situation. Thanks
Have a look at the following tutorial.

Database Normalisation and Table structures

Remeber you can break up tables in a one to one relationship.
May 25 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.