System Resources Exceeded Error

[Reese]

Okay, so I found a starting point for doing what I want with a crosstab and I'm starting to get a hang of it. This query is written to count the number of programs in each month, broken down into program categories.

Here's the sql:

TRANSFORM NZ(Count([Event Information].Event_ID),0) AS [Number of Programs]
SELECT [Event Information].Program_Code
FROM [Event Information]
WHERE ( ( (Year([Date_of_Event]))=Year(Date()) )
                  AND ( ([Event Information].Date_of_Event)<=Date())
              )
GROUP BY [Event Information].Program_Code
PIVOT Format([Date_of_Event],"mmmm") In (January,February,March,April,May,June,July,August,September,October,November,December);

The program categories are the row headings of the pivot and the months are the column headings. The program category values are: ZM, GT, BD, SC, SP, WE and ZS.

This works great but I have two aspects I can't work out.

1) If there's no programs in a category (say SP), that category doesn't appear in the row headings. How can I force them all to appear? I found ways to force all of the months, but not the categories.

2) Is there a way that I can include an "Annual" column that totals all of the months' values?

 

[namliam]

1) Union with some dummy data
2) Union to get the year data

2) probably solves 1) for the most part as well.

For 2)
Make (normal) select for your months
Make (normal) select for your year
Make union query something like so:
Select * from YourMonth
Union
Select * from YourYear
Make your pivot on this..

 

[Reese]

Make (normal) select for your months
Make (normal) select for your year
Make union query something like so:
Select * from YourMonth
Union
Select * from YourYear
Make your pivot on this..

Namlaim, I'm not quite sure what you mean. Are you suggesting that I create one select query for each month, then one for the year, then a union query to join them?

Then would I be doing something like this in the crosstab?

TRANSFORM NZ(Count([Event Information].Event_ID),0) AS [Number of Programs] SELECT [Event Information].Program_Code
FROM [Event Information], INNER JOIN [Insert Name of Union Here]
WHERE ( ( (Year([Event Information].Date_of_Event))=Year(Date()) )                   AND ( ([Event Information].Date_of_Event)<=Date())               )
PIVOT Format([Inser Name of Union Here].Date_of_Event,"mmmm") In (January,February,March,April,May,June,July,August,September,October,November,December);

I'll try experimenting in this in the mean time. Thanks.

 

[namliam]

Dont make a query for each month... The idea is that you need 2 different aggrigation levels.
Which if you want it in records makes for seperate rows, which means you make a single query to make the month values (the one agregation level) an done for the year values (the second)

Alternatively, you could add a value, Count(Event_id) and set it as "Row Header" this should then show as the total of the detailed months displayed to the right of it.

 

[Reese]

I'm sorry, namliam, I'm still confused. How should the year and month select queries be different?

I know how to create a select query that pulls the events from the current year. But without using a select query for each specific month how is the YourMonth query different from the YourYear query?

 

[namliam]

Alternatively, you could add a value, Count(Event_id) and set it as "Row Header" this should then show as the total of the detailed months displayed to the right of it.

Did you try this?

YourMonth
Select format(yourdate,"YYYY MMM") as PivotColumn, count(*) from yourtable ....

Youryear
Select Format(yourdate, "YYYY") As PivotColumn, count(*) from yourtable

UnionQuery
Select * from Yourmonth
Union
Select * from YourYear

Use the UnionQuery as input for your Pivot query

 

[Reese]

Ok, why is this so hard? I created the select queries as you directed and each one properly pulled the data. Here's the code for the month:

SELECT [Event Information].Program_Code, Format([Date_of_Event],"yyyy mmm") AS PivotColumn, Count(*) AS CountColumn
FROM [Event Information]
WHERE ((([Event Information].Canceled) Is Null Or ([Event Information].Canceled)="0") AND (Year([Date_of_Event])=Year(Date())) AND ([Date_of_Event]<=Date()) AND (([Event Information].Sample_Lock) Is Null Or ([Event Information].Sample_Lock)="0"))
GROUP BY [Event Information].Program_Code, [Event Information].Date_of_Event;

And for the year:

SELECT [Event Information].Program_Code, Format([Date_of_Event],"yyyy") AS PivotColumn, Count(*) AS CountColumn
FROM [Event Information]
WHERE ((([Event Information].Canceled) Is Null Or ([Event Information].Canceled)="0") AND (Year([Date_of_Event])=Year(Date())) AND ([Date_of_Event]<=Date()) AND (([Event Information].Sample_Lock) Is Null Or ([Event Information].Sample_Lock)="0"))
GROUP BY [Event Information].Program_Code, [Event Information].Date_of_Event;

The number of programs listed are accurate. However, when I created the union query it messes everything up and the numbers wrong, mostly missing but some are placed where there were none.

Here's the union code:

SELECT *
FROM [Annual Program Number Month Base]
UNION SELECT *
FROM [Annual Program Number Year Base];

And then it's messed up even more with the crosstab query, adding things into the July month, for some reason.

Here's the crosstab code:

TRANSFORM NZ(Sum([Annual Program Number Base Union].CountColumn),0) AS [Number of Programs]
SELECT [Annual Program Number Base Union].Program_Code, Count([Annual Program Number Base Union].Program_Code) AS Annual
FROM [Annual Program Number Base Union]
GROUP BY [Annual Program Number Base Union].Program_Code
PIVOT Format([Annual Program Number Base Union].PivotColumn,"mmm") In (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec);

 

[Reese]

This also hasn't addressed the issue of showing all of the categories and I don't know how to create a union with "dummy data". I tried searching online but I've only found requests for people to post examples of their database populated by dummy data.

I thought about creating a dummy table but have no idea how to integrate it without messing up the count.

Thanks, namlaim, for putting up with me.

 

[Reese]

I thought about creating a dummy table but have no idea how to integrate it without messing up the count.

Never mind, I created the dummy table, integrated it into the union query. The new sql is:

SELECT *
FROM [Annual Program Number Month Base]
UNION SELECT *
FROM [Annual Program Number Year Base]
UNION SELECT [Program_Code], [PivotColumn], [CountColumn]
FROM [DummyTable];

That's taken care of one problem, but the bigger issue of inaccurate information is still there. The above solution also automatically makes each "Annual" entry in the crosstab have a minimum value of 1.

 

[namliam]

Alternatively, you could add a value, Count(Event_id) and set it as "Row Header" this should then show as the total of the detailed months displayed to the right of it.

I am going to stop confusing you and only ask this question, did you try this?

 

[Reese]

Yes, in fact, that's the second part of the SELECT line in the crosstab query that I posted above. Before I tried that an "annual" column wouldn't appear.

 

[Reese]

And, besides, the issue first starts in the union query, before the crosstab.

 

[Reese]

Okay, I just tried that technique straight up.

TRANSFORM NZ(Count([Event Information].Event_ID),0) AS [Number of Programs]
SELECT [Event Information].Program_Code, Count([Event Information].Event_ID) AS [Annual Total]
FROM [Event Information]
WHERE (((Year([Date_of_Event]))=Year(Date())) And ([Date_of_Event]<=Date()))
GROUP BY [Event Information].Program_Code
PIVOT Format([Date_of_Event],"mmmm") In (January,February,March,April,May,June,July,August,September,October,November,December);

That works.

There's still the problem that not all of the categories are visible and when I use the dummy table it adds 1 to the count of each category.

 

[Reese]

Okay, so I found a solution for the missing categories here: http://www.access-programmers.co.uk/forums/showthread.php?t=98429

This is the EASY set of queries. I now have to take this and apply it to income, which is calculated differently for each program category. I'll start experimenting, but do you have any suggestions, Namliam?

 

[Reese]

I actually found a way to do it by having a select query with a calculated income field that is then used as a source for the crosstab query.

 

[namliam]

You could try going the other way as I posted earlier:

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..

This way you can make sure all your columns always exist

Or in your "final" pivot instead of using count, since the count is already in the subqueries.... Try using SUM instead.... just like you are doing with the CountColumn in the values of the month.

TRANSFORM NZ(Sum([Annual Program Number Base Union].CountColumn),0) AS [Number of Programs]
SELECT [Annual Program Number Base Union].Program_Code, [U][I][B]SUM[/B][/I][/U]([Annual Program Number Base Union].[U][I]CountColumn[/I][/U]) AS Annual
FROM [Annual Program Number Base Union]
GROUP BY [Annual Program Number Base Union].Program_Code
PIVOT Format([Annual Program Number Base Union].PivotColumn,"mmm") In (Jan,Feb,Mar,Apr,May,Jun,Jul,Aug,Sep,Oct,Nov,Dec);

You should however either use the Year subquery/union structure or us this additional Annual column.... Not use both.

 

[Reese]

You should however either use the Year subquery/union structure or us this additional Annual column.... Not use both.

Yeah, I realized that belatedly. I was using the confusing union technique that you suggested, then integrated the Annual column. I then realized that I should scrap all of the union queries, go back to the basic crosstab and simply integrate the Annual column.

That works great. I would love to also add in a "Total" row for each column (the Annual and each month), but figured it would be easier to create a separate sum query that does that.

So I created one, it works fine, and I'll just have them as subreports in a report so that I can view both at the same time. Easier than continuing all this code.

Thanks for everything, Namliam!

 

[namliam]

If you want totals at the bottom of your queries, access has a beautifull function in store for you .... I know it didnt exist in like 97 but do know it exists in 2010... Not sure when it was added.

If you run your crosstab query and look at the ribbon it has the option to add TOTALS, per column you can set it to sum and you are done Beautifull little option that not many people know about.