Table or no Table - Data Manipulation

[mattcdse]

Hi all,

So once again I have bumped into one of the slightly odd and initially very confusing nuances of MS Access/Jet 3.0/VBA.

Currently I have a table which holds data about changes that need to be made to our various PC images. The main fields in this table are the Software code, the Image code, the Date the record was added and whether the update has been completed.

This is great for record keeping, but for checking which images I have made a change to is tricky due to the columnular nature of the table.

I would like to ba able to view the data so that a single row shows the sofware code and a check box for each image code showing whether the change has been made for that specific image.

To start with I created some code which created a new table, added the correct fields and inserted the data. Then I found that constantly adding and deleting tables can cause your database to bloat unless you compact and repair regularly....something I don't want to have to do.

So instead I created a single tabe and created and deleted the fields I required. This worked until eventually I hit a 3190 error "Too Many Fields Defined". This is due to the internal field counter for the table not being reduced by 1 for each field deleted. I have not been able to find a way to reset this counter via code and a Compact does not remove this problem. The only method appears to be to copy the data to a new table, but this runs straight back into the creating and deleteing tables problem mentioned earlier.

Something that should be mentioned is that the number of image codes is dynamic as new hardware generally means a new image. Therefore I cannot create a static table with fields for all the image codes as the number of codes is subject to fairly regular change.

So I am thinking...

1. I need to find an alternative method...
2. Am I completely missing the point and could I do this easily using a query? (If so, please show me how! I can't think quite how to make a query work)
3. There is a way round it and I haven't found it yet. (I'd really like to avoid having to compact the database as much as possible)
4 There isn't a way in access, what I'm trying to do is beyond it's scope (I doubt this!)


So any insight would be much appreciated

Cheers,

Matt

 

[RuralGuy]

If it helps, each "image code" should be a new record in a table and not another field in a table. Search on normalization here and with google.

 

[mattcdse]

Thanks for the reply Rural

I already have a table in this form as mentioned in my previous post.

Currently I have a table which holds data about changes that need to be made to our various PC images. The main fields in this table are the Software code, the Image code, the Date the record was added and whether the update has been completed.

I need to be able to manipulate this data so I can display it in a form where the software code is on one row, with a column for each image code so that for each software code I can simply read accross and see which images I have altered. Therefore the data is already resident in a normalised form...I just need to traspose it into a more user friendly form for a report.

Cheers,

Matt

 

[RuralGuy]

You are dynamically adding fields to a table when all you should be doing is adding records.

 

[MSAccessRookie]

Hi all,

So once again I have bumped into one of the slightly odd and initially very confusing nuances of MS Access/Jet 3.0/VBA.

Currently I have a table which holds data about changes that need to be made to our various PC images. The main fields in this table are the Software code, the Image code, the Date the record was added and whether the update has been completed.

This is great for record keeping, but for checking which images I have made a change to is tricky due to the columnular nature of the table.

I would like to ba able to view the data so that a single row shows the sofware code and a check box for each image code showing whether the change has been made for that specific image.

To start with I created some code which created a new table, added the correct fields and inserted the data. Then I found that constantly adding and deleting tables can cause your database to bloat unless you compact and repair regularly....something I don't want to have to do.

So instead I created a single tabe and created and deleted the fields I required. This worked until eventually I hit a 3190 error "Too Many Fields Defined". This is due to the internal field counter for the table not being reduced by 1 for each field deleted. I have not been able to find a way to reset this counter via code and a Compact does not remove this problem. The only method appears to be to copy the data to a new table, but this runs straight back into the creating and deleteing tables problem mentioned earlier.

Something that should be mentioned is that the number of image codes is dynamic as new hardware generally means a new image. Therefore I cannot create a static table with fields for all the image codes as the number of codes is subject to fairly regular change.

So I am thinking...

1. I need to find an alternative method...
2. Am I completely missing the point and could I do this easily using a query? (If so, please show me how! I can't think quite how to make a query work)
3. There is a way round it and I haven't found it yet. (I'd really like to avoid having to compact the database as much as possible)
4 There isn't a way in access, what I'm trying to do is beyond it's scope (I doubt this!)


So any insight would be much appreciated

Cheers,

Matt

I sense a need for redesign and/or reconstruction. If you constantly need to add and remove columns, then your database might benefit from NORMALIZATION. You can read more about NORMALIZATION in the AWF archives, at MicroSoft's Support Site ( See Below ), or on one of the many other sites that explain about the subject.

Showing us the Table design will also be very helpful, as we can provide tips based on the structure.

MicroSoft's Support Site: http://support.microsoft.com/kb/283878

 

[mattcdse]

Thanks for the replies

Appolgies for not being very clear in my previous posts

This the table I am speaking of......

SoftwareCode DateAdded ImageCode Completed
A 1 1A N
B 1 1A Y
C 1 1A N
A 1 2A Y
B 1 2A N
C 1 2A Y
A 2 1B Y
D 2 1B N


EDIT: Appologies for the data being squashed up, I can;t work out how to format it. There are four columns; a letter, a number, a letter and a number, a Y or a N.


This is normalised as far as I am aware. What I need to achieve is to manipulate this data so I can see it in the form.....

SoftwareCode ImageCode1A ImageCode2A ImageCode1B etc....
A N Y Y
B Y N N/A
C N Y N/A
D N/A N/A Y


EDIT: Appologies for the data being squashed up here as well. There are four columns again; a letter, 3 columns of Y,N or N/A.

In this form, the data can be used as a quick reference check list so that poeple can easily where changes have been made and where they have not.


So I have the table, I mearly need to manipulate the data within it, not the table structure itself. I have tried to create a new table as mentioned, but as this is not normalised I run into difficulty. Hence the questions I asked myself like, do I need to find an alternative method to manipulate this data, could I do it with a query (quite likely, I am just bad with queries) or is this kind of manipulation just a bad idea?

Hopefully it is more clear what I would like to be able to do now

Thanks again for all the help

Cheers,

Matt

 

[RuralGuy]

Have you played around with Pivot Table's yet?

 

[mattcdse]

Hi Rural,

No I haven't played with them at all I've not had any experience of them at all. Sad but true.

I'm guessing you're suggesting this is the way forward, can you give me some pointers??

Cheers,

Matt

 

[RuralGuy]

I have very little experience with them but the wizard is pretty helpful. Give it a try.