SQL Question - Cant Open Any More Databases?

[Jeff Freilich]

Hi All

I have a qustion that I am stumped with - I have a table with the
following:

ID (Primary)
CodeID
YearID
DataSourceID
ProvID
Amount

I have to write a bunch of different queriest and pass them to list
boxes throughout the application.

I need to display a list of codes with the value for each of the
provinces (11 including Territories) this is no problem - I wrote the
query dynamic so that when the user selects a different Year or Data
source that the query will be updated. (I did this as well for Data
Source and Year and it works fine)

The client also wants to see YOY results for each of the possible
selections (Data Source, Year, Province) So what I did was:
OntY1 Query (to give me the total for Ontario in the first Year)
OntY2 Query (to give me the total for Ontario in the 2nd Year)
OntYOY - was a query that included Ont1 and Ont2 - I ran the YOY% in
this query and saved it - works perfectly
Then I go to do it for all provinces so I do the same thing for each
one and individually they work

As soon as I try to create the National one (adding 11 YOY queries
with each one having added 2 sub queries themselves) it tells me "Cant
Open Any More Databases" and does not run - it will work if I chop of
a couple of provinces and I seem to be able to get 10 to load no
problem.

So after that long winded description I have 2 questions:

1. Has anyone seen that cant open any more databases message before
and what does it mean
2, What am I doing wrong and/or is there a better design for what I
am trying to do

I can post SQL if need be to show what I am attempting or provide more
info if needed

Any and all hepl and comments are appreciated

Thanks,

Jeff

 

[NKTower]

Instead of all of those queries, why don't you build a virtual table (or
"view")

Here's "the works" with two subqueries and a query to tie them together.
The key thing is that the subqry_Current calculates a column: PreviousYear.


subqry_Current
---------------------------------------------
SELECT DataTable.ID,
DataTable.CodeID,
DataTable.YearID,
[YearID]-1 AS PreviousYear,
DataTable.DataSourceID,
DataTable.ProvID,
DataTable.Amount
FROM DataTable;


-----

subqry_Previous
---------------------------------------
SELECT DataTable.ID,
DataTable.CodeID,
DataTable.YearID,
DataTable.DataSourceID,
DataTable.ProvID,
DataTable.Amount
FROM DataTable);

-------------

Query Current_and_Previous
---------------------------------------------
SELECT sC.ID,
sC.CodeID,
sC.YearID,
sC.PreviousYear,
sC.DataSourceID,
sC.ProvID,
sC.Amount,
sP.ID,
sP.CodeID,
sP.YearID,
sP.DataSourceID,
sP.ProvID,
sP.Amount
FROM subqry_Current sC INNER JOIN
subqry_Previous sP ON
( sC.ProvID = sP.ProvID)
AND (sC.DataSourceID = sP.DataSourceID)
AND (sC.PreviousYear = sP.YearID)
AND (sC.CodeID = sP.CodeID) ;

---------------
Notes:
1) sC is an alias for subqry_Current
2) sP is an alias for subqry_Previous
3) The subqueries do NOT have any conditions (WHERE clause), all subsetting
will be done by the master query.
4) The join makes use of the calculated column.
5) Query Current_and_Revious may now be thought of as a virtual table
(a.k.a. a "viewe") where for a given CodeID, CURRENT YearID, DataSourceID,
and ProvID you have both year amounts available in one row.
6) Base your various combo box/list box row sources on it, with appropriate
filters and sorts etc.

Using your virtual table, you can now do things like this trivally...

SELECT *, (sC.Amount - sP.Amount) As Growth
FROM Current_and_Previous
WHERE ( ( sC.YearID = 2008 )
AND ( sC.CodeID = 0 )
AND ( sC.ProvID = 2 )
);