Newbie question

[theKruser]

I am relatively new with Access, but am trying to learn. I am developing a DB for my orginization that stared off small and has blossomed from there. Without getting too far into the weeds with specifics, I have close to 1,000 fields needed per record. With a limit of 255 fields per table, I am wondering how to accomplish this. I have tried to research and have discovered "child tables" might be a viable option, but I cannot figure out how to set that up completely. Any help would be greatly appreciated.

 

[namliam]

A 1.000 fields per record??? N O WAY !

Are you sure you got that right???
If you have fields like Date1, Date2, Date3 then that should be changed into "child" or "related" tables for sure....

 

[KenHigg]

I would suggest you read up on normalization. There are several good threads here on the forum on the topic.

 

[theKruser]

Believe me, I was shocked at the number as well. 876 and still counting. Staggering. That number is magnified exponentially when you are one of only two people who are being used for data entry/DB construction/administration. The boss gets what the boss wants, right?!? How do I set up child tables to accomplish this? Thanks for the reply and help.

I will also read up on normalization. I am not sure how this will help, but I will do anything to get my mind around this beast. None of my data fields are repeated throughout. Hard to imagine, but it is true. Many of the fields pertain to personnel training and training tracking. Some training is required annually, some are one-time deals.

 

[theKruser]

Another question would be for eaese of data entry. Is there a way to have a single form with tabs input info into multiple tables?

 

[KenHigg]

Can you copy the table structures to a new database and post it. I'm betting you need to normalize -

 

[theKruser]

Unfortunately, I cannot. The tables, even without the record info, contain information that I cannot release. I do thank you for your offer to help, though.

 

[namliam]

The boss gets what the boss wants, right?!?

Yes but to a degree tho... I am the developer, he the boss man...
He does what he is good at (bossing people around while actually accomplishing nearly nothing ) while I do what I am good at.

Many of the fields pertain to personnel training and training tracking. Some training is required annually, some are one-time deals.

If you have i.e. 20 trainings that convert into 20 columns... then your design is flawed...
You will want a related table containing something like...
ID TraingingType TraingingDate

Relating tables is easy, just have a primary key (usually just an autonumber) in one table. Then "copy" or have it copied over by your application into another table as the "foreign key".
Each table can hold 255 fields. Using 4 tables you can store 254 * 4 = 1016 columns.
Tho I am ABSOLUTELY FREAKING SURE that any table over 200 columns is a 'less than optimal' design
I have worked with some HUGE tables in oracle (both in records as in Columns), in some pretty complex ERP systems but never have I even counted a 100 columns in one table.
Even some older but highly complex AS400 stuff... a lot of columns, but not more than 100. Certainly not!

 

[namliam]

Another question would be for eaese of data entry. Is there a way to have a single form with tabs input info into multiple tables?

This statement in itself suggests that there are "parts" of data that belong together... Otherwize you wouldnt be able to split up the information right??

Seperate information belongs in a seperate (normalized) table.

There is a tab control available on the form, which you can use to create your desired tabs.

 

[theKruser]

If you have i.e. 20 trainings that convert into 20 columns... then your design is flawed...
You will want a related table containing something like...
ID TraingingType TraingingDate

Based on this statement...my design is flawed. Here is more info on my specific goal as applied to training completion dates. I have the following types of training:

1. Permanent Certification
2. Fiscal Year Annual
3. Calendar Year Annual
4. Fiscal Year Semi-annual
5. Calendar Year Semi-annual
6. Quarterly
7. Monthly

So far, I have appx 640 different training events divided up between:

1. the intervals listed above
2. seperate training packages
3. required by different personnel depending on 30+ levels of experience/tenure

Some training items cross between different packages (i.e. one event in one package is also required by another, but a single instance of said training qualifies for both packages). I need to not only track them, but need to be notified when:

Annual training expires by FY and CY and give 180-day countdown
Semi-annual training expires by FY and CY (good or bad result only...no countdown)
Quarterly/monthly training expires (good/bad only)

This might be too complex to handle over a forum without me being able to post my DB for you to actually see, but as you can see, I am not well-versed in Access and am dire need of some help. Thank you so much for your help.

 

[namliam]

Yep... Definatly...
Seferal trainings in different timeperiods with different trainings for different employee's

You really need to normalize this into a trainings/certifications table.
This way you can make a single record of each training followed/completed etc. and find anything that has expired.

You will also need a third table, training name.
This will include
PK Name ValidMonths
Among likely other fields
PK = Primary Key (db only)
Name = Name (obviously to be used by the user)
ValidMonths = an integer field that determains the validity duration of the course/training.

You can enter i.e. 3 for 3 months, then 12 for a year 48 for 4 years. Maybe use -1 or 9999999999 for never ending stuff.

Perhaps even add a field called "countdown" yes/no or something like that to differentiate between "counting down" trainings vs "good/bad" trainings.

I am sorry to say this, but I think you have to get back to the drawingboard and start from scratch.
Good news (may be) that you will likely learn A WHOLE LOT if you can bring this project to completion, but in all honesty... and this is not meant to hurt on insult you... but you have a long road ahead of you my friend.
A really looooooooooooong road...

 

[theKruser]

I completely agree with you about the road length. No offense taken. I realize that I am in WAY over my head with this one. Good news is I have a semi-functional DB for the day-to-day and about 4 months to build, beta, and deploy the final project. It satred out small, but worked so good for everyone that the requirements keep piling up. No end in sight for the immediate future!!

I understand what you are getting at with the tables. Here are my thoughts:

tblPersonnel: personal data: empl# (pk), contact info, tenure, one-to-one type data
tblTraining_Info: course id (pk), name, length, expiration rate
tblTraining_Data: training id (pk), empl#, course id, completion date

Here is my delima...my organization operates with Excel and Word. All of my reports are spreadsheets or Mail Merge docs. With this in mind, having a one-to-many relationship (personnel-data) yields multiple instances for each empl# in my queries which wreaks havoc on my spreadsheets not to mention trying to import to Word. How should/could I procede?

 

[namliam]

Databases are NOT to be compared IN ANY WAY SHAPE OR FORM to anything anyone has ever done in excel or word!
Databases are totaly different period!

Your tables look good or atleast much better IMHO and look to be a far cry from 1.000 columns.
Reporting will be a seperate beast to handle later on, for now FIRST and FOREMOST, you must get your BASIC database down AND right...
Once the basics are down reporting will be a headache later on...

 

[dmonney]

1000 fields way too much

Try separating yout your field into different tables. My guess is that alor of these field are flags, you could combine them and write your code to interprit those flags.

 

[theKruser]

I agree. I know that there is no comparison; however, I am building this DB (as does everyone) for the pruposes of reporting information. Since I already know that format of what is required as the end result, I want (actually NEED) to ensure I design the DB around the reporting requirements. I can handle the data export as long as I can drill results down to a one-to-one final product applied to the empl#. Am I setting myself up for failure? Any ideas??

 

[namliam]

As long as your base design is good, reporting (in whatever format) is mearly a matter of formatting...
As long as your data is in the right place in the right way...

The right place is in your DB, the right way is 'normalized' that is the way things are done in Databases!
Ultimatly the report will be possible... if your base is right...

Now guarantees are impossible to give... but this normalized way of storing this information is THE way to go... Having 1000+ fields is just WAY WAY WAY wrong.

 

[theKruser]

Already started building a new DB. Gonna leave the old in place for the day-to-day. thanks for the help. I am sure I will have more issues as time goes on. I have read many threads and how-to's and at least I have an idea now. I am sure i will have a myriad of questions in the future!! Thanks again for all of your help.