System Resource Exceeded

[boblarson]

Ok, but why is that?

If you have a group of "things" (such as fees) then you should have them in their own table so you can combine them with another table (clients) that way?

Okay, let's visit something. A fee is an item, correct? It is no more different than say, an apple. There are many types of apples and each type of apple may have a different price associated with it and you could, as a seller of apples sell your apples to different people at different prices depending on who they are. So what makes a fee any different? You have a FEE. That is the Item. You have different types of fees and different prices based on the clients. Those should be added to the customer as a row in a junction table along with the price of the fee.


Can you predict, with accuracy, that a new fee will never be added? No, not likely. As such it should be based on adding a ROW of data not a column. If you add a column then EVERYTHING (queries, forms, reports, etc.) will need to change, correct?

If you have a new fee and you've set this up in a properly normalized fashion - then you can just add a new fee (ROW) to the fees table, add a ROW to the client/fees table which identifies which fees a client has and how much the fee is.

Then your queries need no "fixing" and your form needs nothing added, and your reports likely need no changes.

proper normalization of data does wonders for adding information that was not known to be needed in the future (or not needed as the case may be).

 

[Voltron]

Thank you very much for that explanation. That is something that I had forgotten, but makes perfect sense. I can see where the rows will significantly reduce work down the road and make life much easier.

Hopefully the rows based structure will resolve the complexity issue that Access is having.

Thank you for all of your help and understanding.

 

[boblarson]

Thank you very much for that explanation. That is something that I had forgotten, but makes perfect sense. I can see where the rows will significantly reduce work down the road and make life much easier.

Hopefully the rows based structure will resolve the complexity issue that Access is having.

Thank you for all of your help and understanding.

Not a problem. It is a difficult task you face regardless and we're here to help try to do as much as we can to help.

Also, sometimes what seems easier is only based on the current knowledge we have and it can be very traumatic when someone comes along and says that all of the work you've done up to this point has been for naught. I don't like doing that for sure but I do like getting someone on the path that is a good and sustainable one.

Good luck and feel free to post anything along the way. We have plenty of willing volunteers here who will try to help out as best they can.

 

[Voltron]

I have another question as to the design of the new database. With the fees they can be dollar amounts, percentages, or Yes/No checkboxes (which are used for some of the queries that are based 100% off of other queries). Would I need to create 3 separate ClientFees_TBL tables to get the desired results so that I can have the "Amount" column in the ClientFees_TBL be %, $, and Yes/No?

 

[boblarson]

In the fees table (which stores the fees) you would include another column which could store the TYPE.

So, for example, you could have a text field which allows you to select %, $, or Y/N and then store that in it. Then when you are building things you use that field to determine what the entry in the VALUE field means.

 

[Voltron]

I'm trying to update my queries, but it is not working properly. I have 3 fees that multiply the total assets by a given percentage. The percentage is in the ClientFees_TBL and is given for each of the fees. Total Assets is located in another query and is easily accessible.

There are multiple companies who have various combinations of the these 3 fees. What I am getting is the percentage multiplied by the total assets for all 3 fees 3 times (once for each fee) because all 3 of the clients have these fees right now.

How do I correct this?

I'd send screen shots, but the 100KB limit is apparently too small for me to deal with with the software I have available to me.

 

[boblarson]

I'd send screen shots, but the 100KB limit is apparently too small for me to deal with with the software I have available to me.

If you ZIP the files the zip file can be up to 2Mb. You can attach up to 5 attachments per message.

 

[Voltron]

Have to download winzip first.

 

[boblarson]

Have to beg to download winzip first.

Why? You have WinXP or greater don't you? Just right click on the file/files and select SEND TO > COMPRESSED FOLDER.

There you go - built in zip utility since WinXP.

 

[Voltron]

Got it. That was painless.

 

[Voltron]

Why? You have WinXP or greater don't you? Just right click on the file/files and select SEND TO > COMPRESSED FOLDER.

There you go - built in zip utility since WinXP.

Sorry. Missed your post.

 

[Voltron]

Would it just make more sense to do it where each fee has its own query? I know how to do that with the new setup of the database, but this is driving me insane.

 

[boblarson]

Would it just make more sense to do it where each fee has its own query? I know how to do that with the new setup of the database, but this is driving me insane.

Any chance of being able to upload a copy of the database but with BOGUS DATA? Just a few "made up" records?

I think we're on the edge of a breakthrough but it is really hard for me to tell from just the screenshots. I don't think you want GROUP for most of it, I think you want SUM for the ones with the calculations OR EXPRESSION, but it is hard to tell without playing with it (I'm more 'hands on' for that).

 

[boblarson]

Would it just make more sense to do it where each fee has its own query? I know how to do that with the new setup of the database, but this is driving me insane.

You could but I really don't think it is necessary. See if this helps at all.

 

[Voltron]

Any chance of being able to upload a copy of the database but with BOGUS DATA? Just a few "made up" records?

I think we're on the edge of a breakthrough but it is really hard for me to tell from just the screenshots. I don't think you want GROUP for most of it, I think you want SUM for the ones with the calculations OR EXPRESSION, but it is hard to tell without playing with it (I'm more 'hands on' for that).

Unfortunately, I cannot upload anything.

It is set to Group By because this is what it defaulted to. There are some queries where I need to use Total: Sum so I click the crosstab button and then click back to Select and it keeps it on there and lets me do a Select query. Most of the queries don't have this, but it is the default so I figured it would be alright.

 

[boblarson]

Unfortunately, I cannot upload anything. Because of the way things are set up I would have to rename everything in the database for privacy reasons and probably wouldn't be allowed to even then.

I understand, but I'll have to bow out now. I am a very "visual" person and need to have things like this in my hands to play with in order to give the right guidance. I'll have to leave it to someone else who can do it without that hand-on approach. Sorry.

 

[Voltron]

I completely understand. Thank you for all of your help to this point.

 

[Voltron]

What I'm looking for is not to sum up the numbers in the columns, but there should be only 1 cell that has a numerical value out of the 3 possible for each row.

 

[FlyingBoat]

I have a very simple query, one join, two columns, summing results with a couple of criteria. Access 2010 runs for 20 minutes and gives a System Resource Exceeded message. Access 2003 finishes the query in 15 seconds with no problem. The table has 500K records.