I inherited a db and one of the tables is giving me problems when trying to change some of the design.

Namely, I make the change and when I try to save it says "Property value too large.". I did a compact/repair. Beyond that, I can't remove any of the columns and am not sure what to do.

Thanks.

Exactly what are you changing, and what property values are you using? How many fields are currently in the table?

There are 200 fields in the table.

The primary key, (the Idnumber) has an input mask of 0000&0000 and I want to change it to 0000&AAAA

Lots of bad things wrong here.

200 fields is too many. You need to re-design and normalize.

You are advocating creating a smart key...another bad idea. Very expensive and prone to breaking.

Well, at the moment I don't have the time to re-design the table. I know it needs to be done, though.

And I'm not sure what you mean by "smart key". There is an input mask and I just need to change it to accommodate new idnumbers. I could remove the input mask if that's better.

George has given you good advice. I think your problem is probably the one documented by http://support.microsoft.com/?id=323657 even though the modification you're making is to an input mask.

Note that the first solution suggested is to normalize your table. You can look through the other options to see if there's a temporary workaround, but it does come down to the fact that you need to fix the underlyinbg problem if you don't want to be forever looking for bandaids.

Yeah, I've read that article and tried the work-arounds and it didn't help. If I were to split the table, how much work would I have to do in order to make the forms work as usual?

The table is also pretty normalized. Each attribute depends only on the primary key and the table is in 2NF. There are a few Z-scores that depend on the scores in other fields to prevent it from being 3NF, but only a limited number and it would be absurd to make a new table for these. It just so happens that the table in question (IQ testing) has a lot of fields.

I'll wager you that the problem lies in your datamodel. And I'll go further and guess that almost every one of those fields stores an answer to a question in a survey?

If so, your table should store four fields only.

The primary key (pref autonumber)
The respondentID (foreign key to a table of respoindents taking the survey)
The questionID (foreign key from a table of questions)
The Actual_Answer field (text/memo/or foreign key from table of allowable answers)

Actually, each field is a score on part of an IQ test and the resulting % or Z-score. So, each entry has a different range of possible values, and even number types.

And predicting a question, no the % and Z-scores are not calculate automatically but by someone scoring the test by hand and then entered in.

Then you simply need an extra field desribing whether the entered value is a percentage or z-score. So that's about 5 or 6 fields necessary to the table design.

ID (auto)
SectionID (foreign key to table of sections [or 'parts'] contained by various tests)
RespondentID (foreign key to table of respondents)
Score
ScoreType (1 for %, 2 for z-score, or whatever)

I'm sure the particulars will depend on the larger data model design, but the principle here is what is important. Instead of having an extra field for each new score, you should be adding an extra row to the table.

Your current design is essentially using repeating fields rather than properly normalizing the stored information.

As I said, I don't have the time (or expertise) to implement this as it seems like a lot of work, re-making forms etc.

Yeah, I've read that article and tried the work-arounds and it didn't help. If I were to split the table, how much work would I have to do in order to make the forms work as usual?

The table is also pretty normalized. Each attribute depends only on the primary key and the table is in 2NF. There are a few Z-scores that depend on the scores in other fields to prevent it from being 3NF, but only a limited number and it would be absurd to make a new table for these. It just so happens that the table in question (IQ testing) has a lot of fields.
If you split the table you could write a query to give a data view similar to what you have now and use that as the datasource for your forms.

The table is also pretty normalized. Each attribute depends only on the primary key and the table is in 2NF. There are a few Z-scores that depend on the scores in other fields to prevent it from being 3NF, but only a limited number and it would be absurd to make a new table for these. It just so happens that the table in question (IQ testing) has a lot of fields.

I'm awestricken. Craig gave me a tool to deal with this type of thing in another thread: http://the-prism.com/Smileys/FirstTry/2fryingpan.gif

Now, all joking aside, what precisely is your problem and how can we help you?

Well, at the moment I don't have the time to re-design the table. I know it needs to be done, though.


I am sorry ... but saying this is like saying, "If I do something, there is a 99.9% probability of it failing (with 99.9% confidence), but I am going to do it anyway."

The point is, what about when you need to put more data into it? What about the next time you want data out of it?

If the database or the company operation has ended its tenure then the priority is getting the data out for publishing whether it's in Excel, SPSS, SAS, or MiniTab.

What exactly, is it that you want?

-dK

I am sorry ... but saying this is like saying, "If I do something, there is a 99.9% probability of it failing (with 99.9% confidence), but I am going to do it anyway."

The point is, what about when you need to put more data into it? What about the next time you want data out of it?

If the database or the company operation has ended its tenure then the priority is getting the data out for publishing whether it's in Excel, SPSS, SAS, or MiniTab.

What exactly, is it that you want?

-dK

Umm, the table works fine for entering/changing data or reading data via forms. I can also use it via SAS easily (and do so often). The only thing I cannot do is change the structure. I know its a problem, but not as big a problem as you seem to make it.

In fact, one of the reasons I would be reluctant to shift to the suggested model (even if I had the time to do it) is that it would make coding in SAS a nightmare.

what precisely is your problem and how can we help you?

Nice come back, now, what precisely is your problem and how can we help you?

The day-to-day operations are fine ... but a design change is called for if you desire to alter the primary key's (Idnumber) input mask (from 0000&0000 to 0000&AAAA).

The implication that a database overhaul does not fit within your operational requirements so looking for a workaround?

Off the cuff (with no guarentees attached) is to create a new field in the table and institute your mask (primary key). Start a whole new data collection table and place in a foreign key of the new primary key.

This only helps in moving forward and does nothing for backwards consolidation without data massaging.

For backwards consolidation, you can use an update query to populate (old records) with the new primary key based on some devised scheme. Update your linked tables using the existing primary key and re-establish your relationships and finally tear out your old primary key.

-dK

In fact, one of the reasons I would be reluctant to shift to the suggested model (even if I had the time to do it) is that it would make coding in SAS a nightmare.

You can usually recreate the spreadsheet form of the data, from a normalized design, using crosstab queries/pivot tables...but I'll leave my contribution at that. Good luck with your project.

Oddly enough, if you try to enter in a idnumber like 5000.A000 via the forms (i.e. you're on the main page for this subject and go to add a record in this IQ table) it lets you. And that's what I did.

I know this database design and implementation is not optimal. But, as I said, I don't have the time or knowledge to change it. I didn't design or make it, I just inherited it.

Right now, all I'm trying to do is keep the database running smoothly.

If the mask was put on the properties of the table design, it is irrelevant if it's not put in the mask properties of the form control.

-dK

Thanks for all the replies. I fixed this particular problem.

If I came as annoyed/argumentative, I apologize. This database is just frustrating to deal with and I'm forced to apply "bandage" solutions.

IMHO, I'd go with Craig's idea. Flatten it all out into a spreadsheet. Work with these top notch guys to get the design right and import it all back in.

So you cost yourself some time with SAS. You have the formulas and points of measurements, so all the heavy lifting from that aspect is done.

I guess the overall consideration (measured in time, money, or whatever quantile fits) is whether you want to spend the time now getting it right or nickel and dime your time later applying bandages.

If retirement is a year away ... I'd go with the latter. =]

-dK

If retirement is a year away ... I'd go with the latter. =]

-dK

Funny you say that. I'm a co-op student here, so my "retirement" from this job is three weeks away. The guy replacing me has much less access experience too.

That coupled with the fact that I have lots of other stuff on my plate = no radical changes.

Glad you got it working.

As long as they don't have a job offer extended ...

Good luck.

-dK