Table with 20+ Fields

[Fernando]

Hi, I have a well normalized data base with a table with 20 Fields. Since on my queries i will be using most of the time just 5 fields of it, should i split my table into two 1 to 1 tables? since I've never done this, which will be the best way to do so?

 

[neileg]

Why would you want to do this? If you have a performance issue, ther are other things to address first, such as indexing.

 

[Fernando]

I was thinking in the long run, I don't really know how it works when you pull data from the server in an Access BE, when u run a query even though you are selecting less fields, does it actually Pull all the table to the user pc and only shows u the one u selected? Or does it pull only the fields you asked? . if it was the first case maybe it would be better to separate for future better performance.

The case is this one

tblChangeOrder
fldChangeOrderID as PK
fldJobID as FK
fldCONum 'Change Order Number
+ 10 text fields for Notes (never to be searched for) that will never repeat for the fine print (ej: work description, sets of plans)

so i will be always using a query to select the first 3 fields for another purposes but i am afraid that Access will actually pull all the table from the server and then select the fields.

 

[hai1111]

Hi, I have some fields: CodeID, ID, MonthYear, [1],[2],..,[31];
How to count from field [1] to [31] if those fields have "+" and have a result in new fields TOTAL.
sorry for my bad English. Thanks

 

[The_Doc_Man]

Fernando: There are reasons for and against splitting tables from single table to have two 1:1 tables. In general, the reasons are based on (1) performance issues or (2) differences in the visibility rules for two sub-segments of a record. I.e. public and private information in the same record.

If the information has public and private parts, splitting the tables so they have different security attributes is ALMOST a no-brainer.

If the problem is performance, indexing the fields you intend to search is probably a better choice. I'm with Nieleg on that point. Access WOULD pull in the entire record if you had to search it (though as you point out, Memo fields don't search...). But if there is a viable index, Access searches that first and the factor of pulling in the whole record is less of an issue.

There are normalization reasons to NOT split tables 1:1 if the only issue is performance. In the end analysis, you must choose how you do this. Search the forum (it has a good SEARCH function) for topics in "Splitting Tables" and "One-to-one tables" (and "One-to-one relationships" too.)

Purists would say NEVER split to form 1:1. Me, I'm a pragmatist. If you think it is truly the right thing to do, you do some reading, and you STILL think it is the right thing to do, it is your DB. If the added maintenance requirements are worth it to you, go for it. If it were left to me, I would ONLY split when the issue was different security needs, leaving performance issues to be handled by indexing. But that's one man's opinion.

hai1111, I'm going to suggest that you repost to a new thread. But your data set is DRASTICALLY not normalized. Read up on "Database Normalization" and then come back with your question in its own post.

 

[Fernando]

Thanks docman, btw before i posted i've already searched using some of the keywords that u r suggesting but all i found was Topics about if its better to split a table in two because it had lots of records.

I don't have any security issues so i wont split it. Thanks

 

[Simon_MT]

I agree with the above, but I always invariably hive off Memo fields no other reason than these fields are, in my case, seldom accessed. These notation fields are mainly used for Invoice or transportation information and therefore are used on invoicing and delivery notes.

I do see #Error on these Memo fields but as this only affects the Note Table then you can simply trash the ancilliary note record.

Simon

 

[Fernando]

thanks for the replys, i was thinking of using text fields with 100 characters not memo fields. Should i change it them to memo?????

 

[boblarson]

thanks for the replys, i was thinking of using text fields with 100 characters not memo fields. Should i change it them to memo?????

It all depends on what your users will need. But, if you were thinking 100 then there's no problem in doing 255 (max for a text field) as Access doesn't pad things like it does in SQL Server.

But if it is a single memo field, then it might be useful to them.

 

[Fernando]

Thanks again, What i understand from what u r saying using or 'declaring' 100 for a textbox its the same as 255 and better that a Memo field in terms of system resources?

 

[boblarson]

Thanks again, What i understand from what u r saying using or 'declaring' 100 for a textbox its the same as 255 and better that a Memo field in terms of system resources?

Sort of - declaring for 255 is not going to be a big system drain versus declaring for 100 but it will be able to store more than twice the characters, which can be a drain on resources if you have thousands and thousands of records. But, if the field is not filled out then declaring as 255 takes the same resources as doing 100.

Memo fields CAN be a drain on the system but it doesn't really have to be so, but it would obviously take more resources than a 255 character field as it can store over 65,000 characters.

I haven't had a problem using memo fields, but I haven't had to worry about thousands and thousands of records either.