How to Structure the table

[prabhus]

Hi,

I have a table where in 286 different fields have to be updated by the users. ( Nature of project

And i have 10 fields which are coming as a input from another table, so i use Append and Update query to add the new datas and update the datas in case of any changes in the input from another table. ( to avoid manual corrections)

Now coming to my real problem, all the 286 fields have to be updated by the user's are check box type, now what is the solution since access will not accept more than 255 fields?

 

[CJ_London]

it does sound like you have not normalised your data and you should consider having a table which has one record per checkbox. However if you insist on doing it all in one table then use one field - a memo field (or you could split it into 2 or more text fields, perhaps based on some grouping of your data) - and set each character to 0 (=false) or 1(=true) so you will end up with a field that looks like

0011101010011010001...etc

then in your form/report etc, you need to split this out into the individual characters and assign them to the different check boxes - using something like

for i=1 to 286
    me("ctrl" & i)=0-mid(myfield(i,1)
next i

you will need to set the controls names in a way that you can run through them - I'm suggesting you call them ctrl1, ctrl2, ctrl3 etc, but it's up to you.

You should see this involves you with a lot more work and maintenance so I would still consider the normalised way of doing things

 

[Rabbie]

Just to endorse what CJ_London has said you really must normalise your data. It will make maintaining your DB much easier.

As a general rule Access tables should be tall and thin rather than broad and short.

In these situations you need to consider exactly what it is you are trying to achieve and what information you need to do this. Once you have clarified that then you can start to design your tables in a normalised fashion. Then and only then should you start designing your forms and reports together with the queries that support them.

Remember if you can'tshiw your method on pecncil and paper you won't be able to do it in Access.

Good luck

 

[prabhus]

Hello,

I am putting my effort to create a tracking tool to simplity the work flow and status in my team.

My table is like the attached file.

I have the work input from two source one i upload directly from the DB2 database and sometime i add the changes manully ( i have dataentry form for this ) This data will be stored in Table 1

Then i have the TL who will assign this work to 3 different teams and team members using the check box. ( also in Table 1 )
Then my 20 team members have to udpate 280 fields with the check box. SO i deceided to split this 280 fields by creating 3 tables with one for each team.

But how should i do a relationship with the Table 1?
Do i also need the append and & Update table 1 fiedls in Table 2, 3 & 4 ?

 

[CJ_London]

You really need to move away from the Excel way of thinking and learn about database design.

These links on normalisation will help you to understand the principles involved
http://www.youtube.com/watch?v=y_MDbbqQIUU
http://support.microsoft.com/kb/283878
http://agiledata.org/essays/dataNormalization.html

Without knowing what data you have in your columns and what each column means it is not possible to advise.