System Resources Exceeded Error

[Reese]

[Solved] System Resources Exceeded Error

I am in the process of writing a union query but have come upon a road block; when running it I get a warning stating that "System resources exceeded."

I read that this can happen in Access 2007 (which is what I'm using) if there are more than 16 joins. But, unless I miss-understand my situation, my query only has 13 joins.

The purpose is to pull together the results of previous queries--one for the total year and one for each month, for a total of 13 union joins. I tested the query by removing individual joins one at a time and found that the query works with 12 joins, but not 13.

Here's the SQL:

SELECT Sum([Annual Education Income Union].[Annual Education Income]) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [Annual Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum([January Education Income Union].[January Education Income]) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [January Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum([February Education Income Union].[February Education Income]) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [February Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum([March Education Income Union].[March Education Income]) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [March Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum([April Education Income Union].[April Education Income]) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [April Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum([May Education Income Union].[May Education Income]) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [May Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum([June Education Income Union].[June Education Income]) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [June Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum([July Education Income Union].[July Education Income]) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [July Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum([August Education Income Union].[August Education Income]) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [August Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum([September Education Income Union].[September Education Income]) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [September Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum([October Education Income Union].[October Education Income]) As [October Income],
      Sum(0) As [November Income],
      Sum(0) As [December Income]
FROM [October Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum([November Education Income Union].[November Education Income]) As [November Income],
      Sum(0) As [December Income]
FROM [November Education Income Union]
UNION SELECT Sum(0) AS [Annual Income],
      Sum(0) AS [January Income],
      Sum(0) As [February Income],
      Sum(0) As [March Income],
      Sum(0) As [April Income],
      Sum(0) As [May Income],
      Sum(0) As [June Income],
      Sum(0) As [July Income],
      Sum(0) As [August Income],
      Sum(0) As [September Income],
      Sum(0) As [October Income],
      Sum(0) As [November Income],
      Sum([December Education Income Union].[December Education Income]) As [December Income]
FROM [December Education Income Union];

Is there any reason why the limit is different on my system?

 

[jdraw]

Can you tell us about your database and tables?
Do you have separate tables for each Month?
If so, why?

 

[Reese]

No, all of the records are in one table. The reason why I've had to break it up is because, depending on the content of each record, there are 8 different ways of calculating the income (one of those conditions does require referencing information in a second table).

I therefore created a union query for the year's income and each month. Each of these union queries calculates the income for each record accordingly and lists the results in one shared income column. For example, here's the SQL for the Annual Education Income Union:

SELECT Sum(nz([Program_Cost])+nz([Millage_Fee])+nz([Auditorium_Cost])+nz([Cancel_Fee])-nz([Reimbursed_Amount])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="ZM" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )<>"BOCES" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="-1" )
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" )
UNION SELECT Sum(nz([Cost_Per_Person])*nz([BOCES_Number_of_Participants])) AS [Annual Education Income]
FROM [BOCES Invoice] INNER JOIN [Event Information] ON [BOCES Invoice].BOCES_PO = [Event Information].BOCES_PO
WHERE ( ( [BOCES Invoice].Paid="-1" )
             AND ( [Event Information].Program_Code="ZM"
                        Or [Event Information].Program_Code="GT" )
             AND ( Year([Event Information].Date_of_Event)=Year(Date()) )
             AND ( [Event Information].Sample_Lock Is Null
                        Or [Event Information].Sample_Lock="0" )
             AND ( [Event Information].Incomplete_Booking Is Null
                        Or [Event Information].Incomplete_Booking="0" )
             AND ( [Event Information].Cost_Category="BOCES" )
             )
UNION SELECT Sum(((nz([Actual_Youth_or_Scouts])+nz([Actual_Adults-Other]))*nz([Cost_Per_Person]))+nz([Cancel_Fee])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="GT" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )<>"BOCES" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="-1" )
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" )
UNION SELECT Sum(nz([BD_ZooMobile_Cost])+nz([Cancel_Fee])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="BD" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )<>"BOCES" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="-1" )
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" )
UNION SELECT Sum(((nz([Actual_Youth_or_Scouts])*nz([Cost_Per_Person]))+(nz([Actual_Adults-Other])*nz([Cost_Per_Person_Other])))+nz([Cancel_Fee])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="SC" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )<>"BOCES" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="-1" )
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" )
UNION SELECT Sum(nz([Program_Cost])+nz([Cancel_Fee])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="WE" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )<>"BOCES" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="-1" )
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" )
UNION SELECT Sum(nz([Program_Cost])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="SP" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )="In House" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="0" )
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" )
UNION SELECT Sum(nz([Program_Cost])) AS [Annual Education Income]
FROM [Event Information]
WHERE ( ( [Event Information].Program_Code="ZS" )
                AND ( Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Cost_Category )="Full Price" )
                AND ( [Event Information].Sample_Lock Is Null
                           Or [Event Information].Sample_Lock="0" )
                AND ( [Event Information].Paid="0")
                AND ( [Event Information].Incomplete_Booking Is Null
                           Or [Event Information].Incomplete_Booking="0" );

What I now need to do is generate the sum of these results into one number. The union query I'm currently writing would do that for all of the queries and display the results in one step.

 

[jdraw]

Could you post a jpg of your relationships window (tables and relationships)?

...there are 8 different ways of calculating the income (one of those conditions does require referencing information in a second table)

Is this some new condition requirement?

 

[Reese]

Here's a picture of the relationship page. The main table is Event Information and the second table that is being referenced in this case is the BOCES Invoice table.

I'm not sure what you meant by asking if it was a new condition requirement.

 

[Reese]

The only part where the BOCES Invoice table comes into play is the second SELECT statement in the Annual Education Income Union's SQL, which I posted above.

 

[jdraw]

Here's a picture of the relationship page. The main table is Event Information and the second table that is being referenced in this case is the BOCES Invoice table.

I'm not sure what you meant by asking if it was a new condition requirement.

The relationships don't show all fields in all tables.
The tables are not normalized as I see it -- based on the event table having so many BOCES fields. All you need is the PK from BOCES (as an FK) and you can access all other fields if and when necessary.

My comment on the new condition was to see if the data base was designed to require all the union queries, or if a new requirement arose and couldn't fit your established structure and required you to use union queries to get the info you need.

 

[Reese]

The relationships don't show all fields in all tables.

True, but unfortunately I won't be able to share that with a screen shot. The Event Information table has 143 fields (not all are used in each record, but each one still needs a lot). At most I'd be able to size things to include about 40 of those, and most of the ones that would be visible wouldn't be related to this particular situation.

The tables are not normalized as I see it -- based on the event table having so many BOCES fields. All you need is the PK from BOCES (as an FK) and you can access all other fields if and when necessary.

True, there are a lot of BOCES fields, but the only ones that are shared between the two tables are BOCES_ID and BOCES_PO.

Even though the PK key in the BOCES table is BOCES_ID, I had linked the two tables using BOCES_PO, because that is a unique Purchase Order number that is sent to us by the local school district and is the number that is shared between the records in the Events Information and BOCES Invoice tables.

For kicks I removed the BOCES section from all 13 source queries. Then the final query (the first one I posted, which is called Annual Income Report Union) worked--but it was missing the BOCES records, so it was incomplete.

I then re-linked the two tables using BOCES_ID instead of BOCES_PO (the relationship is still functional because those fields were in both tables) and replaced BOCES_PO with BOCES_ID in the Annual Education Income Union query and each of the month's queries. Each individual query again worked fine.

However, when I tried to run the Annual Income Report Union query, it now says "Query is too complex." Nothing has changed in that query.

 

[Reese]

And, yes, in hind sight (even before this issue), I realized that using BOCES_PO instead of BOCES_ID was not good practice and redundant, since when establishing the link I set both fields to match between the two tables.

I just didn't want to go back and change things because I was worried that would cause problems and everything seemed to work so far.

If keeping BOCES_ID is necessary then I'll still need to go back and make sure that all previous processes still work.

 

[Reese]

Oh, and again, if I remove one of the joins from the Annual Income Report Union it works fine. So still it's only 12 joins that work, when it should be 16.

 

[Reese]

Okay, so I tabled this query and started working on another that would also be used as an annual report. But now I have a similar issue with this new one. It tells me that the system resources are exceeded.

This is new query, called Annual Number of Programs Report, is just a select query. It selects from 13 previous queries, pulling 8 fields from each query for a total of 104 fields. Here's the SQL:

SELECT [Annual Number of Programs Sum].[Annual Education Programs],
      [Annual Number of Programs Sum].[Annual ZooMobiles],
      [Annual Number of Programs Sum].[Annual Guided Tours],
      [Annual Number of Programs Sum].[Annual Birthday Parties],
      [Annual Number of Programs Sum].[Annual Scout Programs],
      [Annual Number of Programs Sum].[Annual Wild Encounters],
      [Annual Number of Programs Sum].[Annual Special Events],
      [Annual Number of Programs Sum].[Annual Zoo School Programs],
      [January Number of Programs Sum].[January Education Programs],
      [January Number of Programs Sum].[January ZooMobiles],
      [January Number of Programs Sum].[January Guided Tours],
      [January Number of Programs Sum].[January Birthday Parties],
      [January Number of Programs Sum].[January Scout Programs],
      [January Number of Programs Sum].[January Wild Encounters],
      [January Number of Programs Sum].[January Special Events],
      [January Number of Programs Sum].[January Zoo School Programs],
      [February Number of Programs Sum].[February Education Programs],
      [February Number of Programs Sum].[February ZooMobiles],
      [February Number of Programs Sum].[February Guided Tours],
      [February Number of Programs Sum].[February Birthday Parties],
      [February Number of Programs Sum].[February Scout Programs],
      [February Number of Programs Sum].[February Wild Encounters],
      [February Number of Programs Sum].[February Special Events],
      [February Number of Programs Sum].[February Zoo School Programs],
      [March Number of Programs Sum].[March Education Programs],
      [March Number of Programs Sum].[March ZooMobiles],
      [March Number of Programs Sum].[March Guided Tours],
      [March Number of Programs Sum].[March Birthday Parties],
      [March Number of Programs Sum].[March Scout Programs],
      [March Number of Programs Sum].[March Wild Encounters],
      [March Number of Programs Sum].[March Special Events],
      [March Number of Programs Sum].[March Zoo School Programs],
      [April Number of Programs Sum].[April Education Programs],
      [April Number of Programs Sum].[April ZooMobiles],
      [April Number of Programs Sum].[April Guided Tours],
      [April Number of Programs Sum].[April Birthday Parties],
      [April Number of Programs Sum].[April Scout Programs],
      [April Number of Programs Sum].[April Wild Encounters],
      [April Number of Programs Sum].[April Special Events],
      [April Number of Programs Sum].[April Zoo School Programs],
      [May Number of Programs Sum].[May Education Programs],
      [May Number of Programs Sum].[May ZooMobiles],
      [May Number of Programs Sum].[May Guided Tours],
      [May Number of Programs Sum].[May Birthday Parties],
      [May Number of Programs Sum].[May Scout Programs],
      [May Number of Programs Sum].[May Wild Encounters],
      [May Number of Programs Sum].[May Special Events],
      [May Number of Programs Sum].[May Zoo School Programs],
      [June Number of Programs Sum].[June Education Programs],
      [June Number of Programs Sum].[June ZooMobiles],
      [June Number of Programs Sum].[June Guided Tours],
      [June Number of Programs Sum].[June Birthday Parties],
      [June Number of Programs Sum].[June Scout Programs],
      [June Number of Programs Sum].[June Wild Encounters],
      [June Number of Programs Sum].[June Special Events],
      [June Number of Programs Sum].[June Zoo School Programs],
      [July Number of Programs Sum].[July Education Programs],
      [July Number of Programs Sum].[July ZooMobiles],
      [July Number of Programs Sum].[July Guided Tours],
      [July Number of Programs Sum].[July Birthday Parties],
      [July Number of Programs Sum].[July Scout Programs],
      [July Number of Programs Sum].[July Wild Encounters],
      [July Number of Programs Sum].[July Special Events],
      [July Number of Programs Sum].[July Zoo School Programs],
      [August Number of Programs Sum].[August Education Programs],
      [August Number of Programs Sum].[August ZooMobiles],
      [August Number of Programs Sum].[August Guided Tours],
      [August Number of Programs Sum].[August Birthday Parties],
      [August Number of Programs Sum].[August Scout Programs],
      [August Number of Programs Sum].[August Wild Encounters],
      [August Number of Programs Sum].[August Special Events],
      [August Number of Programs Sum].[August Zoo School Programs],
      [September Number of Programs Sum].[September Education Programs],
      [September Number of Programs Sum].[September ZooMobiles],
      [September Number of Programs Sum].[September Guided Tours],
      [September Number of Programs Sum].[September Birthday Parties],
      [September Number of Programs Sum].[September Scout Programs],
      [September Number of Programs Sum].[September Wild Encounters],
      [September Number of Programs Sum].[September Special Events],
      [September Number of Programs Sum].[September Zoo School Programs],
      [October Number of Programs Sum].[October Education Programs],
      [October Number of Programs Sum].[October ZooMobiles],
      [October Number of Programs Sum].[October Guided Tours],
      [October Number of Programs Sum].[October Birthday Parties],
      [October Number of Programs Sum].[October Scout Programs],
      [October Number of Programs Sum].[October Wild Encounters],
      [October Number of Programs Sum].[October Special Events],
      [October Number of Programs Sum].[October Zoo School Programs],
      [November Number of Programs Sum].[November Education Programs],
      [November Number of Programs Sum].[November ZooMobiles],
      [November Number of Programs Sum].[November Guided Tours],
      [November Number of Programs Sum].[November Birthday Parties],
      [November Number of Programs Sum].[November Scout Programs],
      [November Number of Programs Sum].[November Wild Encounters],
      [November Number of Programs Sum].[November Special Events],
      [November Number of Programs Sum].[November Zoo School Programs],
      [December Number of Programs Sum].[December Education Programs],
      [December Number of Programs Sum].[December ZooMobiles],
      [December Number of Programs Sum].[December Guided Tours],
      [December Number of Programs Sum].[December Birthday Parties],
      [December Number of Programs Sum].[December Scout Programs],
      [December Number of Programs Sum].[December Wild Encounters],
      [December Number of Programs Sum].[December Special Events],
      [December Number of Programs Sum].[December Zoo School Programs]
FROM [Annual Number of Programs Sum], [January Number of Programs Sum],
      [February Number of Programs Sum], [March Number of Programs Sum],
      [April Number of Programs Sum], [May Number of Programs Sum],
      [June Number of Programs Sum], [July Number of Programs Sum],
      [August Number of Programs Sum], [September Number of Programs Sum],
      [October Number of Programs Sum], [November Number of Programs Sum],
      [December Number of Programs Sum];

I tried removing one of the source queries at a time and found that it works with 10 queries, but no more.

The 13 source queries each calculate the sums of 8 fields from a different union query. Here's the SQL from the Annual Number of Programs Sum query:

SELECT Sum([Annual Number of Programs Union].[Annual Education Programs]) AS [Annual Education Programs],
       Sum([Annual Number of Programs Union].[Annual ZooMobiles]) AS [Annual ZooMobiles],
       Sum([Annual Number of Programs Union].[Annual Guided Tours]) AS [Annual Guided Tours],
       Sum([Annual Number of Programs Union].[Annual Birthday Parties]) AS [Annual Birthday Parties],
       Sum([Annual Number of Programs Union].[Annual Scout Programs]) AS [Annual Scout Programs],
       Sum([Annual Number of Programs Union].[Annual Wild Encounters]) AS [Annual Wild Encounters],
       Sum([Annual Number of Programs Union].[Annual Special Events]) AS [Annual Special Events],
       Sum([Annual Number of Programs Union].[Annual Zoo School Programs]) AS [Annual Zoo School Programs]
FROM [Annual Number of Programs Union];

Each of the 13 union queries counts the number of records from one table that meet 8 different sets of requirements and puts them into 8 different fields. (All of the information in all of the queries come just from that one table.) Here's the SQL from the Annual Number of Programs Union query:

SELECT Count([Event Information].Event_ID) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Count([Event Information].Event_ID) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="ZM" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Count([Event Information].Event_ID) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="GT" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Count([Event Information].Event_ID) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="BD" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Count([Event Information].Event_ID) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="SC" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Count([Event Information].Event_ID) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="WE" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Count([Event Information].Event_ID) AS [Annual Special Events],
             Sum(0) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="SP" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )
UNION SELECT Sum(0) AS [Annual Education Programs],
             Sum(0) AS [Annual ZooMobiles],
             Sum(0) AS [Annual Guided Tours],
             Sum(0) AS [Annual Birthday Parties],
             Sum(0) AS [Annual Scout Programs],
             Sum(0) AS [Annual Wild Encounters],
             Sum(0) AS [Annual Special Events],
             Count([Event Information].Event_ID) AS [Annual Zoo School Programs]
FROM [Event Information]
WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="ZS" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              );

Does anyone know why I'm getting these system resource errors in the Annual Number of Programs Report and the Annual Income Report Union queries?

 

[jdraw]

Can you post a dumbed down(nothing confidential) copy of your db with enough data to show the problem? A table with 143 fields is suspect from the start.
Have you normalized your tables?
I really don't understand why all UNION queries are required if you have built a data model based on your business rules.

 

[namliam]

Just glancing on the query, the Union definately isnt needed at all, all have the same where clause either with or without the "ZM" in there...

WHERE ( (Year([Date_of_Event])=Year(Date()) )
                AND ( [Event Information].Date_of_Event<=Date() )
                AND ( [Event Information].Program_Code="ZM" )
                AND ( [Event Information].Incomplete_Booking="0" 
                           Or [Event Information].Incomplete_Booking Is Null )
                AND ( [Event Information].Canceled="0"
                           Or [Event Information].Canceled Is Null )
                AND ( [Event Information].Sample_Lock="0"
                           Or [Event Information].Sample_Lock Is Null )
              )

Have you considered using a crosstab or a "manual" version of it
manual version:

Select  Count([Event Information].Event_ID) AS [Annual Education Programs],
        Sum(Iif([Event Information].Program_Code="ZM",1,0)) AS [Annual ZooMobiles],
        Sum(Iif([Event Information].Program_Code="GT",1,0)) AS [Annual Guided Tours],
... etc..

For a "proper" crosstab, make sure to have a lookup table so you can translate ZM to Anual ZooMobiles, then something along the lines of:

TRANSFORM Count([Event Information].Event_ID) AS CountEventID
SELECT Year([Date_of_Event]) as YearEvent
FROM [Event Information]
GROUP BY tblCurrentStorno.CurrDate
PIVOT [Event Information].Program_Code;

I will forgo the usual, use a naming convention and avoid spaces or special characters in your table and column names, speech

 

[Reese]

Can you post a dumbed down(nothing confidential) copy of your db with enough data to show the problem? A table with 143 fields is suspect from the start.

Would you just need the table & relevant queries? I could probably post that without too much effort, so long as you don't need forms, etc.

As for the number of fields, I know it is a lot, but it's a lot of info that I need to keep track of. The Event table keeps track of 8 different kinds of events that require different categories of information, though also a lot of shared information. Just to book a ZooMobile (let alone follow up after the it has occurred) the user has up to 54 fields that are potentially utilized, plus another 8 or so that are "behind the scenes". I could have made a table for each type of program, but that would have meant duplication of the shared fields, plus would have made these summary reports more difficult.

Have you normalized your tables?

I don't quite know what you mean by "normalized". I know in general that means keeping things organized and removing duplicates. In this case there are few duplicates between tables and fields. Some I can & should delete because I found I never utilized them. A few others I only put in when I could find no other way of doing what I was aiming for (I could go into more detail in each case but that would be a much longer conversation).

I really don't understand why all UNION queries are required if you have built a data model based on your business rules.

Have you considered using a crosstab or a "manual" version of it
manual version:

Perhaps unions aren't the way to go, but when reading up on crosstabs I got completely confused and failed when I tried playing around with it. I found that unions allowed me to accomplish finding the same information using multiple different kinds of criteria in less queries. My goal was to do one union for each month instead of 8 for each month, then use a final query to pull it all together and merge it into one row.

I'll try crosstabs again from scratch, but if you guys know of a good place where I can find examples, in addition to the official Office website (where I tried first), I would appreciate it.

I will forgo the usual, use a naming convention and avoid spaces or special characters in your table and column names, speech

Thank you. Yes, I know this now and understand why it's a good idea. I've tried it and have actually made more mistakes with putting the wrong name into a code and/or loosing track of where I am. This comes down to how my mind works, otherwise I would agree with you 100%.

 

[namliam]

Well I gave you a "proper" start to a crosstab query in my previous post, either real crosstab or (semi)manual version of it.

The manual version is a little more flexible...

The real question though is, what do you use it for and is there a legitimate reason why you are crossing the data in the first place?
Why need all the data in one row instead of a column with 8 rows ?

 

[Reese]

The real question though is, what do you use it for and is there a legitimate reason why you are crossing the data in the first place?
Why need all the data in one row instead of a column with 8 rows ?

It would be better to have a column with 8 rows. That's actually why I tried crosstabs first and then just gave up. I was planning on putting it into a report that users would see. That's why I figured I'd still be able to use 1 row with the unions. Would I still be able to do that with the crosstab?

 

[Reese]

It may be a little bit before I have time to but I'll give cross tabs another shot.

 

[namliam]

I think you are mixing things up... a Crosstab puts things row values into columns, which means you may get one row with many columns.

A normal query has then got 2 columns, 1 with the "value" and one with the type/column

 

[Reese]

Sorry, what I meant to say was one column per time frame (Annual, January, February, etc.) with 8 rows in each, like a traditional spreadsheet. If done properly, that's what I'd be able to get, right?

 

[namliam]

if done right, you can do ANYTHING with just access and excel....

My exact wording: You can rule the world with just the two.