System Resource Exceeded (1 Viewer)

[Voltron]

I've been creating a fairly complex (the equations and calculated fields anyway) Access 2007 database for the company I'm at right now for the past several weeks. I have run into numerous issues and have been able to overcome them all (with help from Access World Forums of course).

Here's the deal. I've broken Access. System Resources Exceeded error messages are popping up when I am just 3 fields away from being completely done with the database.

There are many, many calculated fields in different queries (about 50 calculated fields in about 30 queries).

I don't have too many tables (32 is the max) for a query. I don't have too many joins (16 is the max).

EDIT: It overloads when I have 14 joins (for Calc2 query). The max is 16 but I saw the asterisk just now and it says (from Microsoft) "*Maximum values might be lower if the query includes multivalued lookup fields." However, I have another query (Calc1 query) that has 19 joins and is fine. What's the deal?

I think I might be exceeding the Nested Queries limit (50 is the max). However, I'm not exactly what is meant by a nested query or how to go about getting around this nasty error message.

Here is a little more explanation of the database itself (if you need some more please feel free to ask). There are dozens of different types of fees that have to be calculated and eventually summed together in a Query (Fee Summary Total Query). However, this is not possible to do on it's own because there are too many characters (1,024 is the max) for the calculated field area itself. Thus, it is broken down into 2 shorter calculated fields in their own queries which are then added together (Calc1+Calc2=Fee Summary Total).

Does Access count the Nested Queries as part of the other 2 parts (Calc1 and Calc2) because Fee Summary Total Query is based off of both of them?

 

[MarkK]

19 is a lot of joins for one query. What I suspect is a problem with your table design.
It is a common mistake in the early going to create mutiple tables for items that differ only by status or type, so a person new to databases might create a table each for pending orders, back-ordered orders, filled orders, cancelled orders, new orders, blue orders, and so on ....
This should be one table--Orders--with a field called status.
In your case maybe you have too many Fee tables, when you might be able to get away with one, and have fields that define fee types, descriptions, applicability to certain cases by certain criteria, etc... but all in one table. Then your WHERE clauses get complicated by you don't have 19 joins.
Just guessing,
Cheers,

 

[boblarson]

There are dozens of different types of fees that have to be calculated and eventually summed together in a Query

A big question is "how is the data stored?" What does the data look like that you are working with? It sounds mighty suspicious like there are multiple columns here where they should be records. That is just a guess though as I haven't seen your actual table structure, but based on how you've described things.

 

[Voltron]

Table design: The fees are set up all in the same table. They are in the Clients table. The fees do not have their own table because 1) It would have 2 primary connections between the Recordkeeper (not important info at this time) and the Client tables. Recordkeeper and Client tables are linked based of of the Recordkeeper_ID, but this is a 1:M relationship and a 1:M relationship is caused for Fees:Client and Fees:Recordkeeper for the tables if I create a whole new Fees tables. This also makes it infinitely more complex to do the queries based on the fact that the different fees (say Per Participant Fee) varies between clients. It is part of a contract that is made between our clients and the recordkeepers themselves.

The data is stored in the Clients table and consists of both percentages and whole dollar amounts. There are several types of fees though that end up needing to be calculated. One example is a percentage of total plan assets (such as a Wrap Fee). This Wrap Fee is a known percentage we get from our clients. The total amount for the fee is based off of the ever changing total amount of plan assets the client has. This means that I can't take the information and store it directly in the table (you could in Access 2010 but that will only cause nightmares when storing calculated fields in a table). The point of the database is to make it so people don't have to calculate this by hand or enter it into Excel or copy and paste information from Excel to create multiple (50+) separate reports.

The fees could be lumped together to reduce the number of joins, etc. But the main problem with that is it makes it much more difficult for creating the input forms and actually getting people who have never even touched Access to understand what they need to do and why it is the way it is.

I, unfortunately, cannot put anything on here as far as samples due to privacy issues. Any information you need please let me know.

I could probably post the SQL code though. Would that help?

 

[boblarson]

The thing I'm getting at is do you have the fees set up like this:

tluFees
FeeID - Autonumber
FeeDescription - Text
FeeAmt - Currency
FeeEffectiveDate - Date

tblCustomerFees
CustomerFeesID - Autonumber (PK)
CustomerID - Long Integer (FK)
FeeID - Long Integer (FK)
DateAssessed - Date (not sure you need this but just included as a sample)


And that way a ROW of data for each customer's fees is in a table (easily calculated by a query) and not via COLUMNS of data in a table you are trying to combine.

 

[Voltron]

The thing I'm getting at is do you have the fees set up like this:

tluFees
FeeID - Autonumber
FeeDescription - Text
FeeAmt - Currency
FeeEffectiveDate - Date

tblCustomerFees
CustomerFeesID - Autonumber (PK)
CustomerID - Long Integer (FK)
FeeID - Long Integer (FK)
DateAssessed - Date (not sure you need this but just included as a sample)


And that way a ROW of data for each customer's fees is in a table (easily calculated by a query) and not via COLUMNS of data in a table you are trying to combine.

Sorry for my confusion over this.

The information is set up in COLUMNS. I considered doing it based off of rows, but was not comfortable with the ways I was coming up with to do it that way. Each client can have any combination of the 35 different fees (anywhere from 1 all the way to 35), but the kicker is that the amount if different for each client.

Also, I'm not exactly sure how the queries would be any easier if I had the information stored in 2 separate tables, but in Rows as opposed to 1 tables with Columns. I still have to create calculated fields to get the information I need. Am I wrong in my logic here? (Sorry if this is a stupid question I'm still getting used to the differences between large server grade databases and complex Access databases.)

EDIT:

Clients: Client_ID, basic data such as address, etc, number of participants, Fee1, Fee2, Fee3,...Fee35

This is the setup of the Clients table.

 

[DCrake]

A simple analogy is

Columns (fields) are expensive but rows (records) are free.

So if your structure is primarily horizontal then your using to many columns. The more vertical it is the better it will work.

 

[Voltron]

A simple analogy is

Columns (fields) are expensive but rows (records) are free.

So if your structure is primarily horizontal then your using to many columns. The more vertical it is the better it will work.

Oh. I did not know that. That makes sense though.

If it were in rows it would have to be set up something like:

Fees_TBL:
Fee_ID (which is just the name of the fee and I'm not even sure this table would be necessary)

Client_Fees_TBL:
ClientFees_ID (autonumber or something)
Client_ID
Fee_ID (which is just the name of the fee

The problem I'm having is how would I cut down the joins/queries I have that the Fee Summary queries are based off of? I'd still have to create just as many queries based off of the Client_Fees_TBL as I would the current setup I have. The problem seems to be the number of queries I'm running. Is it the amount of juice the current queries are taking up though? I'm not exactly sure on this point.

 

[boblarson]

The problem seems to be the number of queries I'm running. Is it the amount of juice the current queries are taking up though? I'm not exactly sure on this point.

No, you are incorrect. It won't take as many queries (you can do more than one aggregate in a single query. See my example here.

And, as mentioned by David, ROWS are handled differently than COLUMNS when doing queries and coming to limits.

I'm still getting used to the differences between large server grade databases and complex Access databases

There are no differences in proper design. Yes, some like SQL Server or Oracle, etc. can handle doing it the way you're doing it, but that doesn't make it a proper design.

 

[DCrake]

Firstly you have a client table with a client PK

Then you have a fees table that also has a PK, description, cost, etc.

You would then have a table that would be TblClientFees.

PKClientFee
FKClientId
FKFeeID

You then create a query that first has the TblCleintFees with joins to both the clients PKClient - FKClient and PKFee and FKFee.

As clients can have as many or as little fees attached then as long as the relationship is in the TblClientFees table you will get all the info you need.

You can use this query to group by Client to see which fees are linked to which client. also you can group by Fees to see which clients have a particular type of fee.

In summary a client can have none, one or many fees. Using this principle if a new type of fee is introcuded to the Fees table you do not have to amend the clients table to acommodate the new fee type. Which is what I think is happening now.

If I am barking up the wring tree then I appologise.

 

[boblarson]

And I actually showed a way to do it (and since each customer has a different value for the fee, you wouldn't include the fee amount in the lookup table but instead include it in the junction table (the second table I showed).

 

[Voltron]

No, you are incorrect. It won't take as many queries (you can do more than one aggregate in a single query. See my example here.

And, as mentioned by David, ROWS are handled differently than COLUMNS when doing queries and coming to limits.


There are no differences in proper design. Yes, some like SQL Server or Oracle, etc. can handle doing it the way you're doing it, but that doesn't make it a proper design.

That makes sense. Thanks for straightening me out on that one.

So it wasn't the amount of memory being used? I don't think it could have been the number of queries, joins, or tables in the specific queries either because they were either under the limit or (in one case) over the limit but still worked.

 

[Voltron]

Firstly you have a client table with a client PK

Then you have a fees table that also has a PK, description, cost, etc.

You would then have a table that would be TblClientFees.

PKClientFee
FKClientId
FKFeeID

You then create a query that first has the TblCleintFees with joins to both the clients PKClient - FKClient and PKFee and FKFee.

As clients can have as many or as little fees attached then as long as the relationship is in the TblClientFees table you will get all the info you need.

You can use this query to group by Client to see which fees are linked to which client. also you can group by Fees to see which clients have a particular type of fee.

In summary a client can have none, one or many fees. Using this principle if a new type of fee is introcuded to the Fees table you do not have to amend the clients table to acommodate the new fee type. Which is what I think is happening now.

If I am barking up the wring tree then I appologise.

No. You're right. You are definitely barking up the correct tree.

 

[Voltron]

And I actually showed a way to do it (and since each customer has a different value for the fee, you wouldn't include the fee amount in the lookup table but instead include it in the junction table (the second table I showed).

I understand the table structure you are referring to. The problem is I don't understand why the database isn't working the way it is now. I have not reach any known limit on anything that I can see. The only thing I can think of is this:

3 primary queries (Part 1, Part 2, Total).

Part 1 + Part 2 = Total

Part 1: 19 joins (works fine)
Part 2: 14 joins (works fine, but anymore and it won't work)
Total: 2 joins (won't work)

However, when it is like this everything works:
Part 1: 16 joins
Part 2: 14 joins
Total: 2 joins

A grand total of 32 joins. Anymore, even with a new query added (Part 3) is still won't work.

Why?

That is what I've been trying to figure out. I get that Columns take up more power than Rows, but what I have seems like it should be working and its not.

 

[boblarson]

Too many queries and too many joins. Again - bad design makes for bad output. It just can't handle it. Remember, there are SYSTEM stuff going on in there that is included in the limits.

 

[Voltron]

Makes sense.

I've been tinkering and found out it has to do with the "cancel" and "reversal" queries. Why they are needed is unimportant.

These are set up the same was as the Fee Summary queries (Part 1, Part 2, and Total). However, there are 3 separate ones (1 for each type of revenue) for both Cancel and Reversal (total of 6). I have 3 in there right now, but even if I get right of everything else it won't handle all 6, or even 4.

These queries do the EXACT SAME calculations as the Fee Summary stuff I was talking about before, but multiplies it by -1 (cancel) or -2 (reversal) to make the output correct.

If it's just the number of joins and queries I can cut it down to 1 and go from there, but that's not going to help because I already tried that. It is purely and simply something to do with the "cancel" and "reversal" queries. These are probably too much for the database to handle when putting them all together.

Also, the output is correct. It's not bad. It's also not bad design, seeing as this is how it can work (nothing but 1:M and 1:1 relationships). It is simply too complex with the way it is set up. The fact of the matter is that I spent a week looking through things trying to figure out the best way to set up the database and columns based was what I decided upon because the rows makes it way too complex for anyone else at the company other than me to deal with it or understand what it going on.

The problem with row based is that almost everything is calculated differently. I have already combined everything as much as I can into the queries because of how they are calculated. The last thing I wanted to do was put everything into a single query and then not have anybody but me understand what was going on. By having groups of queries, such as Per Participant (which has 5 separate per participant fees in it) together makes it easier for others to understand how it is designed.

I see that row based would work, but at what cost. I will completely have to redo the entire database, create the same queries with slightly different Where clauses all to arrive at having the same number of queries and joins.

One last question for understanding if you would be so kind. When creating a query does it take the joins and number of queries from the queries it is based off of into account? (Total = Joins from Part 1 + Joins from Part 2?)

 

[boblarson]

Makes sense.

I've been tinkering and found out it has to do with the "cancel" and "reversal" queries. Why they are needed is unimportant.

Not a problem in the structure I provided.

These queries do the EXACT SAME calculations as the Fee Summary stuff I was talking about before, but multiplies it by -1 (cancel) or -2 (reversal) to make the output correct.

Again, not a problem in the structure I provided. It is a simple accounting feature that is able to be applied, especially easily when used in rows and not columns.

It is purely and simply something to do with the "cancel" and "reversal" queries. These are probably too much for the database to handle when putting them all together.

As structured now, that is correct. In my suggested structure it would not be a problem. At leaset I can't see a problem with them.

It's also not bad design, seeing as this is how it can work (nothing but 1:M and 1:1 relationships).

Sorry to disagree there but I still do. The structure you currently have is NOT NORMALIZED and is causing problems with your querying the data. That right there proves my point.

It is simply too complex with the way it is set up.

Yes, that is true, it needs to be normalized so that it will work like it should. There is no reason why it can't work well if the correct structure is used.

columns based was what I decided upon because the rows makes it way too complex for anyone else at the company other than me to deal with it or understand what it going on.

Then someone needs some help. The fact of the matter is incorrect database design is one of the major problems that people face at a work location. It is also one of the major reasons why IT departments HATE people using Access; because they design things in a bad way and then when they leave someone else ends up having to support it and IT is usually the one who gets that nod because nobody else in the department knows how to do it. So, one way to get an IT department to NOT HATE YOU, is to design it properly, give the users good UI, and make it so that there is little support needed.

I think we can help you get there but you have to be willing to try and trust that we've been around the block a few times and know what we're talking about.

The problem with row based is that almost everything is calculated differently.

How about some examples of what you mean.

I have already combined everything as much as I can into the queries because of how they are calculated. The last thing I wanted to do was put everything into a single query and then not have anybody but me understand what was going on. By having groups of queries, such as Per Participant (which has 5 separate per participant fees in it) together makes it easier for others to understand how it is designed.

One thing you should be planning on is plenty of documentation. That helps for people following on. However, it isn't like you can't build out some "flat file structure" stuff for them to use to help them out. But you need to start with a good, solid base.

I see that row based would work, but at what cost. I will completely have to redo the entire database, create the same queries with slightly different Where clauses all to arrive at having the same number of queries and joins.

Yes, that is true and learning sometimes is a painful process. But I fear that if you have queries with 19 joins and all, your structure is really NOT helping anyone out here. They won't be able to understand it anyway. You are going through hell right now trying to make it do what it doesn't want to do, hitting limits which - what if something needs to be added - ALL OF YOUR QUERIES and all will need to change. So, what if they add another FEE? How do you deal with that? Or if they add 10 more? Why not build a structure that will handle that without changes, without coding changes, etc. and know that it will live on.

One last question for understanding if you would be so kind. When creating a query does it take the joins and number of queries from the queries it is based off of into account? (Total = Joins from Part 1 + Joins from Part 2?)

I believe it would - yes.

 

[Voltron]

In what way is it not normalized right now? Based off of the knowledge you currently have, I realize you can't see the whole database.

I realize that database design is a huge issue and am working on improving my understanding.

I am currently taking your suggestions. I have been the whole time. I'm simply trying to understand why it will work. Just because something works and I know how doesn't mean its a good thing. I know how a computer works, but why is a different matter entirely because no one on the planet know why electricity works (semi-imaginary particles and all).

I'm sorry if it seemed like I was attacking you. I really wasn't trying to. I still feel that it is not bad output. The output is fine. The DB design may be less than perfect, but everything I ask it for is correct.

I realize you are almost certainly right with suggesting the row based structure (not 100% only because you can't see the whole database. It may be something else I'm doing that I have overlooked that's all ).

 

[boblarson]

In what way is it not normalized right now?

Each fee is a different column - that right there is the biggest most glaring example.

 

[Voltron]

Each fee is a different column - that right there is the biggest most glaring example.

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?