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]

(Don't know which of your posts you may have "subscribed" to, so here it is
on this one as well...)

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 )
);

 

[Jeff Freilich]

HI NKTowers

Thanks for the response - a couple of questions on the proposed
solution:

1. When I do the joins on the subqueries for "Query
Current_and_Previous" it only returns rows where both queries are
equal - it does not display a row where there was a value in year 1
but not year 2 (or vice versa) I would need it to return a row for
every entry in either year with the null value displaying as zero in
the other year (so a Code could have a an Amount of 20 in year 1 and 0
in year 2 or an amount of 0 in year 1 and 10 in year 2 - and I would
need a returned result for either of thoe combinations)

2. Even with this solution wouldnt I need a separate "master" query
for each province that I would then need to join into a "National
Master" that I could return a result for every province in the same
query - I would still have a large number of queries though only run
off the "virtual table"

Ultimately the task is for me to write a query that will return
results that will have the codes in the rows and columns for every
province and territory (so 11 data columns) for every row returned.
As I mentiond in the original post it works for the amounts for each
year - just getting the problems when trying to return the YOY %.

Thanks again for your inputs

Jeff

(Don't know which of your posts you may have "subscribed" to, so here it is
on this one as well...)

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 )
);

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

Jeff- Hide quoted text -

- Show quoted text -

 

[NKTower]

I still think it can be done with a lot fewer and generalized queries.

A RIGHT JOIN or LEFT HIIN will give you one side (or the other) where there
is a missing record, but I don't think you can get them both in one shot,
such as

LEFT EXISTS & no RIGHT
no LEFT & RIGHT EXISTS
LEFT EXISTS and RIGHT EXISTS

BUT you do a UNION QUERY which has 3 components, one for each of the states
above. Since a particular combination of your identifiers can only be in
one of those states at a time, you would only get one row for any particular
combination of your key items.

An alternative would be to have a row for each combination of your key
items, with an explicit NULL in the AMOUNT column if there is no measurement.
The your EQUI-JOIN would work as expected, just returning NULL if there was
no explicit measurement (as opposed to 0 for an explicitly seen value of 0

If you can get the UNION query to work, it would be a cleaner way to do it
since you wouldn't have to manufacture place-holder records. Access can do
UNION queries, you just can't design them in the query design grid. You
instead open the query designer, and select VIEW SQL and key it in. You can
also specify that you want a UNION query in the QUERY drop down just as you
change to an UPDATE or APPEND or DELETE query. The good news is that you can
write the 3 individual queries in the design grid - subqry_BOTH_EXIST,
subqry_PREVIOUS_ONLY, subqry_CURRENT_ONLY and make sure that they work
correctly. Get record counts - the total of the 3 should total your possible
combinations. Then in the query designer you tie them together with a SQL
statement. I think it would be like this

SELECT * FROM subqry_BOTH_EXIST
UNION
SELECT * FROM subqry_PREVIOUS_ONLY
UNION
SELECT * FROM subqry_CURRENT_ONLY

If you run that, you should get one row per possible combination.

Remember that the 3 queries must return EXACTLY the same number of columns
in EXACTLY the same order and that the data types must be EXACTLY the same.
(I believe that the names may be different - the column names are picked up
from the first query.)

For the NATIONAL you would use the same union query as row source, but toss
in a TOTAL(Amount) as NationalTotal, and a GROUP BY where you list everything
except for ProvID.

If you don't need to differentiate between NULL and the value 0, then in
your subqueries use NZ(Amount,0) As Amt - it will make life easier
downstream.

 

[NKTower]

I've been re-thinking my suggestions. You have a "sparse data" problem. You
could have any of these conditions

Year 1 data exists, Year 2 data exists
Year 1 data exists, Year 2 data NULL
Year 1 data NULL, Year 2 data exists
Year 1 data NULL, Year 2 data NULL

You want to see the values for all combinations of CodeID, DataSourceID,
ProvID for the year pairs.

While from an academic standpoint it can probably be done in pure SQL with
various subqueries, joins of various types, and UNION queries as in my
previous posting, on second thought I think you'd be better off building a
temporary table. The following should be a lot easier to follow and
maintain. It uses a temporary table and some VBA code to populate it.

Not having all of your table definitions, I've made some assumptions:

a) The table that you collect your data points is

tableataTable
ID, autonumber, PK
CodeID, number/long, not null
YearID, number, long, not null
DataSourceID, number, long, not null
ProvID, number, long, not null
AMount, Currency, not null

b) you have lookup tables for the codes...

table:lookup_CODE
CodeID, number (autonumber?) long, PK
Code, text

table: lookup_DataSource
DataSourceID, number (autonumber?)long, PK
DataSource, text

table: lookup_Province
ProvID, (autonumber?)long, PK
Province, text

For the lookup_xxx tables, I will assume that there may be gaps in the
sequence of keys. This is handled in the code below by way of loading some
arrays with only existing values.


c) Create this table which will contain your temporary working results
table:temp_MATRIX
link_CodeID, number, long, not null, PK, FK to lookup_CodeID
link_DataSourceID, number, long, not null, PK, FK to lookup_DataSource
link_Province, number, long, not null, PK, FK to lookup_Province
Year_1, number, int, null allowed
Year_2, number, int, null allowed
Amount_1, currency, null allowed
Amount_2, currency, null allowed

This table will contain your de-normalized data, and expand the missing data
to NULLs associated with your key values as needed. It will even contain
rows for the tripliets (CodeID, DataSourceID, ProvID) where you had no
amounts for either year.

d) Then create this module "mod_Matrix". You should be able to
cut-and-paste. Change table names/field names as needed. Make sure that you
have a check in TOOLS/REFERENCES for the Microsoft DAO library.

Option Compare Database
Option Explicit

' be sure that TOOLS/REFERNCES has a check
' opposite Microsoft DAO 3.6 (or similar)
'
Dim longarray_CodeID() As Long
Dim longarray_DataSource() As Long
Dim longarray_ProvID() As Long
Dim int_N_CodeID As Integer
Dim int_N_DataSourceID As Integer
Dim int_N_ProvID As Integer


Private Function load_CodeID_array() As Integer
Dim RS As DAO.Recordset
Dim I As Integer
Dim N As Integer

I = 0
N = 0
Set RS = CurrentDb.OpenRecordset("lookup_CodeID")
If Not RS.EOF Then
RS.MoveLast
N = RS.RecordCount
If N > 0 Then ReDim ongarray_CodeID(1 To N)
RS.MoveFirst
End If
While Not RS.EOF
I = I + 1
longarray_CodeID(I) = RS("CodeID")
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
load_CodeID_array = I
End Function

Private Function load_DataSourceID_array() As Integer
Dim RS As DAO.Recordset
Dim I As Integer
Dim N As Integer

I = 0
N = 0
Set RS = CurrentDb.OpenRecordset("lookup_DataSourceID")
If Not RS.EOF Then
RS.MoveLast
N = RS.RecordCount
If N > 0 Then ReDim longarray_DataSourceID(1 To N)
RS.MoveFirst
End If
While Not RS.EOF
I = I + 1
longarray_DataSourceID(I) = RS("DataSourceID")
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
load_DataSourceID_array = I
End Function

Private Function load_ProvID_array() As Integer
Dim RS As DAO.Recordset
Dim I As Integer
Dim N As Integer

I = 0
N = 0
Set RS = CurrentDb.OpenRecordset("lookup_ProvID")
If Not RS.EOF Then
RS.MoveLast
N = RS.RecordCount
If N > 0 Then ReDim longarray_ProvID(1 To N)
RS.MoveFirst
End If
While Not RS.EOF
I = I + 1
longarray_ProvID(I) = RS("ProvID")
RS.MoveNext
Wend
RS.Close
Set RS = Nothing
load_ProvID_array = I
End Function

Private Sub Clear_temp_Matrix()
Dim SQL As String

SQL = "DELETE * FROM temp_MATRIX"
CurrentDb.Execute SQL

End Sub

Private Function load_ID_arrays() As Integer

load_ID_arrays = 0 ' until otherwise
int_N_CodeID = load_CodeID_array()
If (int_N_CodeID = 0) Then
MsgBox "CodeID table is empty.", vbOKOnly, "Error"
load_ID_arrays = -1
End If
int_N_DataSourceID = load_DataSourceID_array()
If (int_N_DataSourceID = 0) Then
MsgBox "DataSource table is empty.", vbOKOnly, "Error"
load_ID_arrays = -1
End If
int_N_ProvID = load_ProvID_array()
If (int_N_ProvID = 0) Then
MsgBox "ProvID table is empty.", vbOKOnly, "Error"
load_ID_arrays = -1
End If
End Function

Public Function build_temp_Matrix() As Long

Dim RS As DAO.Recordset
Dim fld_RS(0 To 6) As DAO.Field
Dim err As Integer
Dim i_Code As Integer
Dim i_DataSource As Integer
Dim i_Prov As Integer
Dim l_Records As Long
Dim I As Integer

err = load_ID_arrays()
If err Then
build_temp_Matrix = 0
Exit Function
End If

DoCmd.Hourglass True
DoEvents
Clear_temp_Matrix ' erase previous data

l_Records = 0

Set RS = CurrentDb.OpenRecordset("temp_Matrix")
' Since we will be in a tight loop, use field variables so
' VBA doesn't have to work as hard
Set fld_RS(0) = RS("link_CodeID")
Set fld_RS(1) = RS("link_DataSourceID")
Set fld_RS(2) = RS("link_ProvID")
Set fld_RS(3) = RS("Year_1")
Set fld_RS(4) = RS("Year_2")
Set fld_RS(5) = RS("Amount_1")
Set fld_RS(6) = RS("Amount_2")

For i_Code = 1 To int_N_CodeID
For i_DataSource = 1 To int_N_DataSourceID
For i_Prov = 1 To int_N_ProvID
RS.AddNew
fld_RS(0) = longarray_CodeID(i_Code)
fld_RS(1) = longarray_DataSource(i_DataSource)
fld_RS(2) = longarray_ProvID(i_Prov)
fld_RS(3) = Null ' Year 1
fld_RS(4) = Null ' Year 2
fld_RS(5) = Null ' Amount 1
fld_RS(6) = Null ' Amount 2
RS.Update
DoEvents
l_Records = l_Records + 1
Next i_Prov
Next i_DataSource
Next i_Code
For I = 1 to 6
Set fld_RS(I) = Nothing
Next I
RS.Close
Set RS = Nothing
build_temp_Matrix = l_Records
DoCmd.Hourglass False
DoEvents
End Function

Public Function Populate_temp_Matrix(int_Year_1 As Integer, _
int_Year_2
As Integer) _
As Long
Dim N_Records As Long
Dim SQL As String
Dim Y(2) As Integer
Dim I As Integer
Y(1) = int_Year_1
Y(2) = int_Year_2

Debug.Print "Populate_temp_Matrix(" & _
Str(int_Year_1) & "," & _
Str(int_Year_2) & ")"

N_Records = build_temp_Matrix
If N_Records = 0 Then
Populate_temp_Matrix = -1
Exit Function
End If

DoCmd.Hourglass True
DoEvents

For I = 1 To 2
SQL = "UPDATE DataTable INNER JOIN " & vbCrLf & _
" temp_MATRIX ON " & vbCrLf & _
" (DataTable.ProvID = temp_MATRIX.link_ProvinceID) " &
vbCrLf & _
" AND (DataTable.DataSourceID = temp_MATRIX.link_DataSourceID) "
& vbCrLf & _
" AND (DataTable.CodeID = temp_MATRIX.link_CodeID) " & vbCrLf & _
"SET temp_MATRIX.Year_" & Trim(str(I) & _
" = " & Str(Y(I)) & ", " & vbCrLf & _
" temp_MATRIX.Amount_" & Trim(Str(I)) &
" = [amount]" & vbCrLf & _
"WHERE ( DataTable.YearID = " & Str(Y(I)) & " ) "
Debug.Print SQL
CurrentDb.Execute SQL
Next I
Populate_temp_Matrix = N_Records
DoCmd.Hourglass False
DoEvents
Debug.Print "=" & Str(N_Records)
End Function

' ------------------ end mod_Matrix

e) Create a form with
two text boxes - txt_Year_1 and txt_Year_2
command button with this OnClick event

Dim N_Records As Long
N_Records = Populate_temp_Matrix(Me.txt_Year_1, Me.txt_Year_2)

If N_Records comes back > 0 you have a loaded table to work with.

 

[Jeff Freilich]

Hi NKTower

Thanks for your reply - I have been playing with your solution and
have a couple of questions:

1. When I join sC an sP it joins them where both fields are eual - so
I get resulsts when there is a value for both current year and
previous year - often there is a value for previous year and not
current year or vice versa - I would need to have it return all values
with a "0" if there is nothing in that year - that way I can get a
true YOY comparison - the current way it leaves out data unless there
is data for both of the years you are looking at.

2. With your solution wouldnt I also need a lot of queries again
anyway - unless I am missing how to use the master query - with my
understanding I would need a new "Master Query" for each province that
looks at the virtual table and then I would need a "National Master"
that has a column for every province. - I am trying to get a query
with 11 data columns for every row

Any other thoughts or comments are appreciated

Thanks,

Jeff

(Don't know which of your posts you may have "subscribed" to, so here it is
on this one as well...)

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 )
);

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

Jeff- Hide quoted text -

- Show quoted text -

 

[NKTower]

*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*

Skip the thing I wrote on Sunday or earlier, and look at the solution I put
up today. It starts with "I've been re-thinking" I think it is a much
better approach.

Note: - A few of the long source code "wrapped", the compiler will complain.
It compiles clean when unwrapped.

You have a sparse data problem. I don't recommend trying to resolve it with
combinations of queires.

*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*
*-*-*-*-*-*-*-*

 

[Jeff Freilich]

Hi NKTower

Thanks for your reply - I have been playing with your solution and
have a couple of questions:

1. When I join sC an sP it joins them where both fields are eual - so
I get resulsts when there is a value for both current year and
previous year - often there is a value for previous year and not
current year or vice versa - I would need to have it return all values
with a "0" if there is nothing in that year - that way I can get a
true YOY comparison - the current way it leaves out data unless there
is data for both of the years you are looking at.

2. With your solution wouldnt I also need a lot of queries again
anyway - unless I am missing how to use the master query - with my
understanding I would need a new "Master Query" for each province that
looks at the virtual table and then I would need a "National Master"
that has a column for every province. - I am trying to get a query
with 11 data columns for every row

Any other thoughts or comments are appreciated

Thanks,

Jeff

 

[NKTower]

JEFF ===
Abandon the solution with the sP and sQ subqueries. I have posted a
completely different method that will work much better. Look in the thread
for the string "re-think". I've built tables to simulate yours and wrote a
bit of VBA code that handles it all.

This is my third attempt to bring this to your attention today. Please
don't use the sP and sC process as it won't work with sparse data as you
need. The other technique will

Bruce

 

[JeffTO]

Hi NKTower

Thanks for your reply - I have been playing with your solution and
have a couple of questions:

1. When I join sC an sP it joins them where both fields are eual - so
I get resulsts when there is a value for both current year and
previous year - often there is a value for previous year and not
current year or vice versa - I would need to have it return all values
with a "0" if there is nothing in that year - that way I can get a
true YOY comparison - the current way it leaves out data unless there
is data for both of the years you are looking at.

2. With your solution wouldnt I also need a lot of queries again
anyway - unless I am missing how to use the master query - with my
understanding I would need a new "Master Query" for each province that
looks at the virtual table and then I would need a "National Master"
that has a column for every province. - I am trying to get a query
with 11 data columns for every row

Any other thoughts or comments are appreciated

Thanks,

Jeff

(Don't know which of your posts you may have "subscribed" to, so here it is
on this one as well...)

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 )
);

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

Jeff- Hide quoted text -

- Show quoted text -

 

[Jeff Freilich]

Hi NKTower

Thanks for your replies and your solutions - and sorry for the
multiple posts - I kept trying to post as I thought I was having
problems until I realized it was google that was having the problems -
so my appologies for the many posts.

I used a modified version of your solution.

I already had a master query that worked for a single year (using sub
queries for each province) - so I created 2 Make table queries from
that master table query - 1 for each year - and then I could run a
simply query on teh 2 temp tables to get what I was looking for. I
should have went to the make table solution much earlier

Thanks again for your input

Jeff

Hi NKTower

Thanks for your reply - I have been playing with your solution and
have a couple of questions:

1. When I join sC an sP it joins them where both fields are eual - so
I get resulsts when there is a value for both current year and
previous year - often there is a value for previous year and not
current year or vice versa - I would need to have it return all values
with a "0" if there is nothing in that year - that way I can get a
true YOY comparison - the current way it leaves out data unless there
is data for both of the years you are looking at.

2.  With your solution wouldnt I also need a lot of queries again
anyway - unless I am missing how to use the master query - with my
understanding I would need a new "Master Query" for each province that
looks at the virtual table and then I would need a "National Master"
that has a column for every province.  - I am trying to get a query
with 11 data columns for every row

Any other thoughts or comments are appreciated

Thanks,

Jeff

(Don't know which of your posts you may have "subscribed" to, so here it is
on this one as well...)

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 )
);

- Show quoted text -- Hide quoted text -

- Show quoted text -