headers number reach 255 into a table (1 Viewer)
- Thread starter Alt
- Start date
- Local time
- Today, 08:36
- Joined
- Jul 9, 2003
- Messages
- 16,329
It very much sounds like your friend is adding a field to the table to represent some sort of added information.
A typical example would be where your database handles students and the subjects they take. A new subject is added to the curriculum, let's say a new language:- "Klingon"
The subject table has a different field for each subject, and because a new subject is added to the curriculum, you have to add a new field to the subject table.
If this is the case, then the structure of your database resembles a spreadsheet. It is very likely that it started out in a spreadsheet, and was later transferred to MS Access.
This is a very common mistake made by most people I reckon.
It will eventually make the database unusable, as your friend has discovered because he now has a problem with a table with too many Fields.
The solution is to take the subject data and put it in a slightly different table structure as a explained in my blog here:-
www.niftyaccess.com
A typical example would be where your database handles students and the subjects they take. A new subject is added to the curriculum, let's say a new language:- "Klingon"
The subject table has a different field for each subject, and because a new subject is added to the curriculum, you have to add a new field to the subject table.
If this is the case, then the structure of your database resembles a spreadsheet. It is very likely that it started out in a spreadsheet, and was later transferred to MS Access.
This is a very common mistake made by most people I reckon.
It will eventually make the database unusable, as your friend has discovered because he now has a problem with a table with too many Fields.
The solution is to take the subject data and put it in a slightly different table structure as a explained in my blog here:-
Excel in Access - Nifty Access
Excel in Access - Problem & Solution! One of the most frequent problems I see is people confusing MS Access with Microsoft Excel. This leads to fundamental design errors. Now, the problem is, this error "A Major Error" does not become apparent straight away. Unfortunately your database will work...
www.niftyaccess.com
Your "friend" should read up on normalization:
en.m.wikipedia.org
That's the process of properly setting up your tables. Give it a read, work thru some tutorials and then apply what you learn to your data. Sorry, your friends data. You could also post a screenshot of the table so we can see all it's fields and we can give you/your friend specific advice
Database normalization - Wikipedia
That's the process of properly setting up your tables. Give it a read, work thru some tutorials and then apply what you learn to your data. Sorry, your friends data. You could also post a screenshot of the table so we can see all it's fields and we can give you/your friend specific advice
After looking carefully into his works, I found out than he put headers for 4 different reports. I think the best will be to split those fields into 3 new different tables and used specific keys on each new tables.
reports:
1- instruments list (only one document, weekly issued)
2- instruments datasheets (each datasheet will be generated in excel and will have a specific DocNo)
3- process conditions (linked to the instruments datasheets, only tracking import date from cells)
4- documents tracker (to track 1 to 3 above)
reports:
1- instruments list (only one document, weekly issued)
2- instruments datasheets (each datasheet will be generated in excel and will have a specific DocNo)
3- process conditions (linked to the instruments datasheets, only tracking import date from cells)
4- documents tracker (to track 1 to 3 above)
Last edited:
- Local time
- Today, 08:36
- Joined
- Jul 9, 2003
- Messages
- 16,329
If you mean you are going to export data from your MS Access database into Excel spreadsheets then you might find this blog of mine helpful:-
Make Excel Sheets From Access Table - Nifty Access
Excel Sheets From Access Table OP "avalve" asked this question on Access World Forums:- "Howdy! Let me make a small scenario. Let's say I have a table with fields CarMake/CarModel/Year/Color. What I am trying to do is to export the table to a single Excel Workbook with each CarMake having their...
www.niftyaccess.com
- Local time
- Today, 03:36
- Joined
- Feb 19, 2002
- Messages
- 43,430
I'll bet there are still some 1-many relationships lurking in there. Usually, we can spot them because they have numeric suffixes but sometimes, the repeating group is less obvious. For example, you have rental properties and you want to track expenses so you build columns. Gas, Oil, Electric, Water, Lawn, Snow.
Then you buy a new property with a pool and you have to change forms, queries, report, code to accommodate the new expense types. Turns out that expenses is a repeating group and the items belong in a separate, many-side table. That way when you find a new type, it is just a new row in the definition table. NOTHING else has to change.
Then you buy a new property with a pool and you have to change forms, queries, report, code to accommodate the new expense types. Turns out that expenses is a repeating group and the items belong in a separate, many-side table. That way when you find a new type, it is just a new row in the definition table. NOTHING else has to change.
GPGeorge
George Hepworth
- Local time
- Today, 00:36
- Joined
- Nov 25, 2004
- Messages
- 1,967
Here's an excellent series of articles that describe the problem of spreadsheet style tables, and more importantly, how to fix them (and no, it's not just randomly splitting fields off into new tables). Roger applies the rules of normalization to the problem. You should help your friend do the same.
