Validation Rule Error

[London]

Hello,



I am building an Access Database for my training company. One of my tables is very large and complex, it's primary purpose is to perform calculations related to my classes. Unfortunately it contains many calculate columns that stack on each other and I haven't figured out a way to normalize it without losing functionality. As a result I have repeatedly run up against the character size limit Access imposes for tables. This table allowed me to input up to 20 records, but on my 21st record it is now showing me an error related to a validation rule. I don't have any validation rules set, and I confirmed this. I also changed the table slightly to see if it was related to the size, but that didn't seem to change anything. I can still input data on my other tables, just not this one (or variants of it, I tried copying it without the data, and it gave me the same error when I tried to enter the first record). I have scoured the internet but have failed to find any explanation as to why this error is occurring. This table was working, and does work up to 20 records.

I am using Access 2010 on Windows 10.

Here is a screen capture of the error:


Any help or insight would be much appreciated, thank you.

[Mickjav]

Don't use the calculate columns I think I heard they have problems plus you should build a form and only do the calculations as and when needed you shouln't store them in tables there are exceptions but what your doing wouldn't be covered by the exception.

I have very large tables esp in my Style manager as I had to use one to one relationships https://databasedreams.createaforum.com/style-manager/

[London]

I have seen on other forums everyone saying don't use calculated columns, but for what I need there is no alternative. Queries can't do the complex calculations I need and having to run a bunch of queries is inefficient. The biggest problem is that the calculations must build off each other (one calculation is reliant on the outcome of another). These records are critical for my company and are specific to each entry, so I must store them. I haven't been able to find another way to do it that works as efficiently or effectively as this does (prior to the error message). I've tried alternatives but they just don't work. What is causing this error specifically, and is there a way to clear it?

One to one relationships could do it, but calculated columns can't be used in relationships, and again I can't eliminate using them. If calculated columns were relational, then I could normalize my tables a lot more. But since they aren't I'm stuck with it like this.

[pbaldy]

I wonder if the error is coming from referential integrity. Can you add a record with valid selections in the fields, like 1 or 81 in the primary instructor field?

[London]

No, any attempts to add a record bring up the error message. Even when I attempt to add a record using the form I built for that purpose. I can edit existing records though no problem, just not add new ones.

It should be noted that I created a backup awhile ago (and confirmed everything worked). I attempted to add a record in the backup just to see if it would work, and I also get the error (previously I did not). Even though I have not been working with the backup nor have changed anything with it.

[Mickjav]

I have the same problem with a project I'm working on as it contans at present 2.5 million records in one table so what I did was for one set Look as picture

I was able to use queries but for the reports It was too slow so I built a function to do the work

Look at the attached file the report is built with a function
https://databasedreams.createaforum....msg269/#msg269

The third image stats screen show the Query version as it only has to work with a view charts it's very quick less than a second but for a chart report it's very slow
https://databasedreams.createaforum....msg248/#msg248

[pbaldy]

If Mickjav's thoughts don't help, can you attach the db here?

[Mickjav]

Sorry miss read your post

[London]

Okay, let's see if I'm tracking. You create a chart based off the table, then calculate using a query based off the chart?

And yes, I might be able to attach the database here.

[Mickjav]

It would help I might be able to write a function for you but will need to look first.

[London]

I wasn't sure if it was possible to upload the database directly to this forum, so I put it in my dropbox and have a link to it. I've tried changing a few things to get it to work, so I'm giving you all access to the backup I made before trying to make any changes in order to keep the variables more minimal and narrow down what the problem might be. This backup did work when I first created it, but is now also showing the same error. Any help would be appreciated, thank you.

https://www.dropbox.com/s/y6vnesbsn1...-19.accdb?dl=0

[pbaldy]

Wow, that is quite the table. You already know it's not normalized. I think you've hit some sort of limit. If I delete a bunch of fields from that table I can add a record to it. Access is not really built to work "across" like that. Hopefully Mickjav or someone else can offer a solution or some design thoughts. My head hurts and I can't look at it anymore.

Okay really I'm just out of time, family coming for Christmas Eve, gotta go!

[Mickjav]

Wow, that is quite the table. You already know it's not normalized. I think you've hit some sort of limit. If I delete a bunch of fields from that table I can add a record to it. Access is not really built to work "across" like that. Hopefully Mickjav or someone else can offer a solution or some design thoughts. My head hurts and I can't look at it anymore.

Okay really I'm just out of time, family coming for Christmas Eve, gotta go!

I will have a look and see what I can do but wont be until 26 now

[Mickjav]

I've downloaded it had a quick look and already see i'll need to redesign it I'll work on it after christmas it should be fun to sort out.

One thing that everybody should note it not to leave spaces in field names it can cause a lot of problems

[Mickjav]

Found a few hours so had a play
I have made a few assumptions

One being you are dealing with a group of students rather than individual students

This is inclomplete at present and I have been working more with the relationships rather than datafields they will come later.