Using subset criteria in query

[Guest]

I have a query that I would like to be able to set the criteria in two
separate fields (say the first is Division and the second is Conference). The
conference field is actually a subset of the Division field (meaning if the
Conference is "A" it is by defintion a subset of Division "A" ). The problem
I have is that I can only set one of these fields or my query will freeze up.
Ultimately, I want to be able to use a form with a combo box of the various
Divisions and Conferences to set the query criteria but I can not get past
this first hurdle - is there something fundementally wrong with having both
the “parent & daughter†criteria in a query? Is this why my query freezes?
fyi - if I only use one or the other criteria the query works fine, it is
only when I use both that I have problems.

 

[Ken Snell [MVP]]

Can you post more details about the queries? Such as the SQL statements for
them? And the table structures that you're using.

 

[Guest]

The whole query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));


Boiled down to items in question:

SELECT [104 Adjusted Home Scores].Season, [104 Adjusted Home Scores]![Home
Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted Margin] AS
[Adjusted Margin], [Season Division Conference School].Division, [Season
Division Conference School_1].Division
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));


Hope you can follow this.

The [Filter Parameters] are from a table that gets input from a form.
Having the two AND statements are what seems to get me in trouble.

 

[Ken Snell [MVP]]

When you say "the query freezes", am I correct in understanding that to mean
that the query runs very slowly and you "end" it by Ctrl+Break before it
finishes? And that the two AND statements that you reference are these:

(([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));

Please clarify which "added" criterion is causing the slowdown. You've
posted two separate query statements, and I am not sure which of the two is
the one on which we need to focus. Are you running both?

Also, I note that you're using Date as the name of a field. Date and many
other words are reserved words in ACCESS, and you are well advised to avoid
their use as names of fields or controls. See this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
--

Ken Snell
<MS ACCESS MVP>

The whole query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));


Boiled down to items in question:

SELECT [104 Adjusted Home Scores].Season, [104 Adjusted Home Scores]![Home
Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted Margin] AS
[Adjusted Margin], [Season Division Conference School].Division, [Season
Division Conference School_1].Division
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));


Hope you can follow this.

The [Filter Parameters] are from a table that gets input from a form.
Having the two AND statements are what seems to get me in trouble.

Can you post more details about the queries? Such as the SQL statements for
them? And the table structures that you're using.

--

Ken Snell
<MS ACCESS MVP>

Conference).
The if
the freeze
up.

 

[Guest]

Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one posted is
my complete query. The second, I removed the stuff I though was non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that maks any
difference) you mention and I will review the posting you listed but I would
be suprised if it is causing a problem since it works in this query if I drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query has two sets
of conferences and divisions. The away(visitor) team has a conference and a
division it belongs to, as does the home team, each is represented by a
column in the query (total of 4 columns). I can run the query with either the
conference or the division of the home and the away team with a criteria, but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND (([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));

When you say "the query freezes", am I correct in understanding that to mean
that the query runs very slowly and you "end" it by Ctrl+Break before it
finishes? And that the two AND statements that you reference are these:

(([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));

Please clarify which "added" criterion is causing the slowdown. You've
posted two separate query statements, and I am not sure which of the two is
the one on which we need to focus. Are you running both?

Also, I note that you're using Date as the name of a field. Date and many
other words are reserved words in ACCESS, and you are well advised to avoid
their use as names of fields or controls. See this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
--

Ken Snell
<MS ACCESS MVP>

The whole query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));


Boiled down to items in question:

SELECT [104 Adjusted Home Scores].Season, [104 Adjusted Home Scores]![Home
Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted Margin] AS
[Adjusted Margin], [Season Division Conference School].Division, [Season
Division Conference School_1].Division
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));


Hope you can follow this.

The [Filter Parameters] are from a table that gets input from a form.
Having the two AND statements are what seems to get me in trouble.

Can you post more details about the queries? Such as the SQL statements for
them? And the table structures that you're using.

--

Ken Snell
<MS ACCESS MVP>


I have a query that I would like to be able to set the criteria in two
separate fields (say the first is Division and the second is Conference).
The
conference field is actually a subset of the Division field (meaning if
the
Conference is "A" it is by defintion a subset of Division "A" ). The
problem
I have is that I can only set one of these fields or my query will freeze
up.
Ultimately, I want to be able to use a form with a combo box of the
various
Divisions and Conferences to set the query criteria but I can not get past
this first hurdle - is there something fundementally wrong with having
both
the "parent & daughter" criteria in a query? Is this why my query freezes?
fyi - if I only use one or the other criteria the query works fine, it is
only when I use both that I have problems.

 

[Ken Snell [MVP]]

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with WHERE
clauses. My experience has been that cartesian queries can take a while to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined correctly so
that you don't need the WHERE clauses that match the records between the
joining tables? Meaning that your query would contain a double join between
104 Adjusted Home Scores and Filter Parameters tables on the [104 Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104 Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and a join
between Season Division Conference School and Filter Parameters tables on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division] join; and a
join between 105 Adjusted Away Scores and Filter Parameters tables on the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>

Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one posted is
my complete query. The second, I removed the stuff I though was non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that maks any
difference) you mention and I will review the posting you listed but I would
be suprised if it is causing a problem since it works in this query if I drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query has two sets
of conferences and divisions. The away(visitor) team has a conference and a
division it belongs to, as does the home team, each is represented by a
column in the query (total of 4 columns). I can run the query with either the
conference or the division of the home and the away team with a criteria, but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND (([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));

When you say "the query freezes", am I correct in understanding that to mean
that the query runs very slowly and you "end" it by Ctrl+Break before it
finishes? And that the two AND statements that you reference are these:

(([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));

Please clarify which "added" criterion is causing the slowdown. You've
posted two separate query statements, and I am not sure which of the two is
the one on which we need to focus. Are you running both?

Also, I note that you're using Date as the name of a field. Date and many
other words are reserved words in ACCESS, and you are well advised to avoid
their use as names of fields or controls. See this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
--

Ken Snell
<MS ACCESS MVP>

The whole query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference

School].School
=

[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104

Adjusted
Home

Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));


Boiled down to items in question:

SELECT [104 Adjusted Home Scores].Season, [104 Adjusted Home Scores]![Home
Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted Margin] AS
[Adjusted Margin], [Season Division Conference School].Division, [Season
Division Conference School_1].Division
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference

School].School
=

[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));


Hope you can follow this.

The [Filter Parameters] are from a table that gets input from a form.
Having the two AND statements are what seems to get me in trouble.


:

Can you post more details about the queries? Such as the SQL

statements
for

them? And the table structures that you're using.

--

Ken Snell
<MS ACCESS MVP>


I have a query that I would like to be able to set the criteria in two
separate fields (say the first is Division and the second is Conference).
The
conference field is actually a subset of the Division field

(meaning
if

the
Conference is "A" it is by defintion a subset of Division "A" ). The
problem
I have is that I can only set one of these fields or my query will freeze
up.
Ultimately, I want to be able to use a form with a combo box of the
various
Divisions and Conferences to set the query criteria but I can not

get
past

this first hurdle - is there something fundementally wrong with having
both
the "parent & daughter" criteria in a query? Is this why my query freezes?
fyi - if I only use one or the other criteria the query works

fine, it
is

only when I use both that I have problems.

 

[Guest]

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop down
option in the [filter parameter] table but it does work - I assume the query
reads it as text and not as a "is not blank" statement.

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with WHERE
clauses. My experience has been that cartesian queries can take a while to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined correctly so
that you don't need the WHERE clauses that match the records between the
joining tables? Meaning that your query would contain a double join between
104 Adjusted Home Scores and Filter Parameters tables on the [104 Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104 Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and a join
between Season Division Conference School and Filter Parameters tables on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division] join; and a
join between 105 Adjusted Away Scores and Filter Parameters tables on the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>

Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one posted is
my complete query. The second, I removed the stuff I though was non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that maks any
difference) you mention and I will review the posting you listed but I would
be suprised if it is causing a problem since it works in this query if I drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query has two sets
of conferences and divisions. The away(visitor) team has a conference and a
division it belongs to, as does the home team, each is represented by a
column in the query (total of 4 columns). I can run the query with either the
conference or the division of the home and the away team with a criteria, but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School =
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND (([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));

When you say "the query freezes", am I correct in understanding that to mean
that the query runs very slowly and you "end" it by Ctrl+Break before it
finishes? And that the two AND statements that you reference are these:

(([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));

Please clarify which "added" criterion is causing the slowdown. You've
posted two separate query statements, and I am not sure which of the two is
the one on which we need to focus. Are you running both?

Also, I note that you're using Date as the name of a field. Date and many
other words are reserved words in ACCESS, and you are well advised to avoid
their use as names of fields or controls. See this Knowledge Base article:
ACC2002: Reserved Words in Microsoft Access
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
--

Ken Snell
<MS ACCESS MVP>


The whole query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted
Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));


Boiled down to items in question:

SELECT [104 Adjusted Home Scores].Season, [104 Adjusted Home Scores]![Home
Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted Margin] AS
[Adjusted Margin], [Season Division Conference School].Division, [Season
Division Conference School_1].Division
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]));


Hope you can follow this.

The [Filter Parameters] are from a table that gets input from a form.
Having the two AND statements are what seems to get me in trouble.


:

Can you post more details about the queries? Such as the SQL statements
for
them? And the table structures that you're using.

--

Ken Snell
<MS ACCESS MVP>


I have a query that I would like to be able to set the criteria in two
separate fields (say the first is Division and the second is
Conference).
The
conference field is actually a subset of the Division field (meaning
if
the
Conference is "A" it is by defintion a subset of Division "A" ). The
problem
I have is that I can only set one of these fields or my query will
freeze
up.
Ultimately, I want to be able to use a form with a combo box of the
various
Divisions and Conferences to set the query criteria but I can not get
past
this first hurdle - is there something fundementally wrong with having
both
the "parent & daughter" criteria in a query? Is this why my query
freezes?
fyi - if I only use one or the other criteria the query works fine, it
is
only when I use both that I have problems.

 

[Ken Snell [MVP]]

Post the SQL that you now have. Filter Parameters is the name of a form? And
Division and Conference aret the names of controls on that form?

--

Ken Snell
<MS ACCESS MVP>

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop down
option in the [filter parameter] table but it does work - I assume the query
reads it as text and not as a "is not blank" statement.

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with WHERE
clauses. My experience has been that cartesian queries can take a while to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined correctly so
that you don't need the WHERE clauses that match the records between the
joining tables? Meaning that your query would contain a double join between
104 Adjusted Home Scores and Filter Parameters tables on the [104 Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104 Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and a join
between Season Division Conference School and Filter Parameters tables on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division] join; and a
join between 105 Adjusted Away Scores and Filter Parameters tables on the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>

Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one posted is
my complete query. The second, I removed the stuff I though was non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that maks any
difference) you mention and I will review the posting you listed but

I
would

be suprised if it is causing a problem since it works in this query if

I
drop

eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query has

two
sets

of conferences and divisions. The away(visitor) team has a conference

and
a

division it belongs to, as does the home team, each is represented by a
column in the query (total of 4 columns). I can run the query with

either
the

conference or the division of the home and the away team with a

criteria,
but

it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference

School].School
=

[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104

Adjusted
Home

Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference

School].School
=

[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104

Adjusted
Home

Scores].[Home Conference])=[Filter parameters]![Conference]) AND (([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));

< snipped rest of threaded message >

 

[Guest]

Filter Parameters is a table that I use to store input from a form. Division
and Conference are fields in the Filter parameter table and have seperate
controls on the input form.

Current query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters] INNER JOIN (([Season Division Conference School]
INNER JOIN [104 Adjusted Home Scores] ON ([Season Division Conference
School].School = [104 Adjusted Home Scores].Home) AND ([Season Division
Conference School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season Division Conference School] AS [Season Division Conference School_1]
INNER JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID) ON ([Filter
parameters].Conference = [104 Adjusted Home Scores].[Home Conference]) AND
([Filter parameters].Division = [104 Adjusted Home Scores].[Home Division])
AND ([Filter parameters].Season = [104 Adjusted Home Scores].Season) AND
([Filter parameters].Conference = [105 Adjusted Away Scores].[Visitors
Conference]) AND ([Filter parameters].Division = [105 Adjusted Away
Scores].[Visitors Division]) AND ([Filter parameters].Season = [105 Adjusted
Away Scores].Season)
WHERE ((([104 Adjusted Home Scores].Nuetral)=False) AND (([105 Adjusted Away
Scores].Nuetral)=False));

Post the SQL that you now have. Filter Parameters is the name of a form? And
Division and Conference aret the names of controls on that form?

--

Ken Snell
<MS ACCESS MVP>

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop down
option in the [filter parameter] table but it does work - I assume the query
reads it as text and not as a "is not blank" statement.

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with WHERE
clauses. My experience has been that cartesian queries can take a while to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined correctly so
that you don't need the WHERE clauses that match the records between the
joining tables? Meaning that your query would contain a double join between
104 Adjusted Home Scores and Filter Parameters tables on the [104 Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104 Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and a join
between Season Division Conference School and Filter Parameters tables on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division] join; and a
join between 105 Adjusted Away Scores and Filter Parameters tables on the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>



Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one posted is
my complete query. The second, I removed the stuff I though was
non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that maks any
difference) you mention and I will review the posting you listed but I
would
be suprised if it is causing a problem since it works in this query if I
drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query has two
sets
of conferences and divisions. The away(visitor) team has a conference and
a
division it belongs to, as does the home team, each is represented by a
column in the query (total of 4 columns). I can run the query with either
the
conference or the division of the home and the away team with a criteria,
but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted
Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor,
[105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference School_1] INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted
Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND (([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));

< snipped rest of threaded message >

 

[Ken Snell [MVP]]

Because you're using the Division and Conference fields as linking fields,
those fields need to have a value for each record in Filter Parameters
table. Otherwise, Jet database engine will not find matches in the other
tables.

You say you're using a form to put the values into Filter Parameters table.
I assume that the form is bound to this table, and you're selecting a single
value each for the Division and Conference fields? Thus, your query would
run using a single record in Filter Parameters table?

If you want the option to have the query run for all values of Division and
Conference when you leave the combo box blank, you'll need to put some code
in the form (likely on the Click event of a command button) that will create
records in the Filter Parameters table for each value that exist for
Division and Conference. This will give multiple records in that table,
thereby allowing your query to find all the desired matching records.

However, if your Filter Parameters table is set up with the Division field
and the Conference field in the record, then your query won't work correctly
because, with multiple records in the Filter Parameters table, you'll need
every possible combination of Division and Conference values (if you leave
both empty on your form). In this case (you leave both empty), you'll get
multiple, duplicative records in the query's result because of the many
existences of the same value for, say, Division in the table.

Or, if you leave just one of the options (Division or Conference) empty,
then your form's code would need to create multiple records where each
record has the same value of whichever one you select on the form and each
record will have one of the possible values for the one you didn't select.
In this case, you'd get the desired result, but only by creating the records
in the table by code before you run the query.

I can help you with some code that will create records in the Filter
Parameters table, but would need to know the situation that will be used --
will just one (Division or Conference) be left empty (or have as its
selection some "all" option in your combo box's dropdown list)? or will both
potentially be left unselected? Also, how are you currently running this
query -- by a command button on a form? by manually opening it? by code?
--

Ken Snell
<MS ACCESS MVP>

Filter Parameters is a table that I use to store input from a form. Division
and Conference are fields in the Filter parameter table and have seperate
controls on the input form.

Current query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters] INNER JOIN (([Season Division Conference School]
INNER JOIN [104 Adjusted Home Scores] ON ([Season Division Conference
School].School = [104 Adjusted Home Scores].Home) AND ([Season Division
Conference School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season Division Conference School] AS [Season Division Conference School_1]
INNER JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID) ON ([Filter
parameters].Conference = [104 Adjusted Home Scores].[Home Conference]) AND
([Filter parameters].Division = [104 Adjusted Home Scores].[Home Division])
AND ([Filter parameters].Season = [104 Adjusted Home Scores].Season) AND
([Filter parameters].Conference = [105 Adjusted Away Scores].[Visitors
Conference]) AND ([Filter parameters].Division = [105 Adjusted Away
Scores].[Visitors Division]) AND ([Filter parameters].Season = [105 Adjusted
Away Scores].Season)
WHERE ((([104 Adjusted Home Scores].Nuetral)=False) AND (([105 Adjusted Away
Scores].Nuetral)=False));

Post the SQL that you now have. Filter Parameters is the name of a form? And
Division and Conference aret the names of controls on that form?

--

Ken Snell
<MS ACCESS MVP>

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop down
option in the [filter parameter] table but it does work - I assume the query
reads it as text and not as a "is not blank" statement.


:

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with WHERE
clauses. My experience has been that cartesian queries can take a

while
to

run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined

correctly
so

that you don't need the WHERE clauses that match the records between the
joining tables? Meaning that your query would contain a double join between
104 Adjusted Home Scores and Filter Parameters tables on the [104 Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104 Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and

a
join

between Season Division Conference School and Filter Parameters

tables
on

the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division] join; and a
join between 105 Adjusted Away Scores and Filter Parameters tables

on
the

[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>



Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one posted is
my complete query. The second, I removed the stuff I though was
non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that

maks
any

difference) you mention and I will review the posting you listed

but
I

would
be suprised if it is causing a problem since it works in this

query if
I

drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query

has
two

sets
of conferences and divisions. The away(visitor) team has a

conference
and

a
division it belongs to, as does the home team, each is represented

by
a

column in the query (total of 4 columns). I can run the query with either
the
conference or the division of the home and the away team with a criteria,
but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School]

INNER
JOIN

[104 Adjusted Home Scores] ON ([Season Division Conference School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference

School_1]
INNER

JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season))

ON
[104

Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted
Home
Scores].Nuetral)=False) AND (([105 Adjusted Away Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor,
[105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School]

INNER
JOIN

[104 Adjusted Home Scores] ON ([Season Division Conference School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN ([Season
Division Conference School] AS [Season Division Conference

School_1]
INNER

JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season))

ON
[104

Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104 Adjusted
Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND (([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));

< snipped rest of threaded message >

 

[Guest]

Wow, this is getting complicated but I do appreciate the help.

Answers:

Yes, I have a form bound to the table [Filter Parameters].
Yes, the the query will run on one input recordset (row) from the Filter
Parameters table.

There are six senerios that would be used for the query. 1) A particular
Division with the all Conferences in that Division. 2) A particular
Conference (as a subset of its Division) 3) All Divisions (and thus all
Conferences) 4,5 & 6) Same as 1,2 & 3 except instead of a particular seasons
these would include all the seasons.

The query is activated by a control button on a form, the form has combo
boxes to enter the Season, Division & Conference of choice, these are stored
in the [Filter Parameter] .

Because you're using the Division and Conference fields as linking fields,
those fields need to have a value for each record in Filter Parameters
table. Otherwise, Jet database engine will not find matches in the other
tables.

You say you're using a form to put the values into Filter Parameters table.
I assume that the form is bound to this table, and you're selecting a single
value each for the Division and Conference fields? Thus, your query would
run using a single record in Filter Parameters table?

If you want the option to have the query run for all values of Division and
Conference when you leave the combo box blank, you'll need to put some code
in the form (likely on the Click event of a command button) that will create
records in the Filter Parameters table for each value that exist for
Division and Conference. This will give multiple records in that table,
thereby allowing your query to find all the desired matching records.

However, if your Filter Parameters table is set up with the Division field
and the Conference field in the record, then your query won't work correctly
because, with multiple records in the Filter Parameters table, you'll need
every possible combination of Division and Conference values (if you leave
both empty on your form). In this case (you leave both empty), you'll get
multiple, duplicative records in the query's result because of the many
existences of the same value for, say, Division in the table.

Or, if you leave just one of the options (Division or Conference) empty,
then your form's code would need to create multiple records where each
record has the same value of whichever one you select on the form and each
record will have one of the possible values for the one you didn't select.
In this case, you'd get the desired result, but only by creating the records
in the table by code before you run the query.

I can help you with some code that will create records in the Filter
Parameters table, but would need to know the situation that will be used --
will just one (Division or Conference) be left empty (or have as its
selection some "all" option in your combo box's dropdown list)? or will both
potentially be left unselected? Also, how are you currently running this
query -- by a command button on a form? by manually opening it? by code?
--

Ken Snell
<MS ACCESS MVP>

Filter Parameters is a table that I use to store input from a form. Division
and Conference are fields in the Filter parameter table and have seperate
controls on the input form.

Current query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters] INNER JOIN (([Season Division Conference School]
INNER JOIN [104 Adjusted Home Scores] ON ([Season Division Conference
School].School = [104 Adjusted Home Scores].Home) AND ([Season Division
Conference School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season Division Conference School] AS [Season Division Conference School_1]
INNER JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID) ON ([Filter
parameters].Conference = [104 Adjusted Home Scores].[Home Conference]) AND
([Filter parameters].Division = [104 Adjusted Home Scores].[Home Division])
AND ([Filter parameters].Season = [104 Adjusted Home Scores].Season) AND
([Filter parameters].Conference = [105 Adjusted Away Scores].[Visitors
Conference]) AND ([Filter parameters].Division = [105 Adjusted Away
Scores].[Visitors Division]) AND ([Filter parameters].Season = [105 Adjusted
Away Scores].Season)
WHERE ((([104 Adjusted Home Scores].Nuetral)=False) AND (([105 Adjusted Away
Scores].Nuetral)=False));

Post the SQL that you now have. Filter Parameters is the name of a form? And
Division and Conference aret the names of controls on that form?

--

Ken Snell
<MS ACCESS MVP>

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop down
option in the [filter parameter] table but it does work - I assume the
query
reads it as text and not as a "is not blank" statement.


:

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with WHERE
clauses. My experience has been that cartesian queries can take a while
to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined correctly
so
that you don't need the WHERE clauses that match the records between the
joining tables? Meaning that your query would contain a double join
between
104 Adjusted Home Scores and Filter Parameters tables on the [104
Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104
Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and a
join
between Season Division Conference School and Filter Parameters tables
on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division] join; and a
join between 105 Adjusted Away Scores and Filter Parameters tables on
the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>



Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one
posted is
my complete query. The second, I removed the stuff I though was
non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that maks
any
difference) you mention and I will review the posting you listed but
I
would
be suprised if it is causing a problem since it works in this query if
I
drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query has
two
sets
of conferences and divisions. The away(visitor) team has a conference
and
a
division it belongs to, as does the home team, each is represented by
a
column in the query (total of 4 columns). I can run the query with
either
the
conference or the division of the home and the away team with a
criteria,
but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER
JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference
School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season
Division Conference School] AS [Season Division Conference School_1]
INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON
[104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter
parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104
Adjusted
Home
Scores].Nuetral)=False) AND (([105 Adjusted Away
Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home
Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor,
[105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER
JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference
School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season
Division Conference School] AS [Season Division Conference School_1]
INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON
[104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter
parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104
Adjusted
Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND
(([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home
Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));


< snipped rest of threaded message >

 

[Ken Snell [MVP]]

Yes, this is a fairly complex setup that you require. It will require a lot
of code to handle all of the scenarios. I don't have the time available to
provide you with code for all the possible scenarios, so I'll use the one
where you want to choose a Division and get all the Conferences associated
to that Division. You then can work with that to write all the additional
code that you'll need for the other scenarios.

In the code that runs when you click the command button (which eventually
opens the query), you'll need to add steps similar to the following (for
this one scenerio that I'm using as the example):

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngC As Long
Dim strSQL As String
Set dbs = CurrentDb

' Delete any records currently in the Filter Parameters table
strSQL = "DELETE * FROM [Filter Parameters];"
dbs.Execute strSQL, dbFailOnError

' Test if a division has been selected without a conference selection
If Len(Me.Division.Value & "") > 0 And _
Len(Me.Conference.Value & "") = 0 Then

' Open a recordset to Filter Parameters table
Set rst = dbs.OpenRecordset("Filter Parameters", _
dbOpenDynaset, dbAppendOnly)

' Need to get the division name and all the conference names, and write
' them to the Filter Parameters table. Code assumes that the Conference
' control is a combo box and that it holds the conferences for the
' selected division.
' ... loop through all rows in Conference dropdown list
For lngC = 1 To Me.Conference.ListCount
' ...code assumes that the Bound Column of the combo box holds
' the name of the conference
rst.AddNew
rst.Fields("Division").Value = Me.Division.Value
rst.Fields("Conference").Value = _
Me.Conference.Column(Me.Conference.BoundColumn, _
lngC - 1)
rst.Update
Next lngC

' Close the recordset to the Filter Parameters table
rst.Close
Set rst = Nothing
End If
dbs.Close
Set dbs = Nothing
DoCmd.OpenQuery "NameOfQueryThatYou'reRunning"


--

Ken Snell
<MS ACCESS MVP>

Wow, this is getting complicated but I do appreciate the help.

Answers:

Yes, I have a form bound to the table [Filter Parameters].
Yes, the the query will run on one input recordset (row) from the Filter
Parameters table.

There are six senerios that would be used for the query. 1) A particular
Division with the all Conferences in that Division. 2) A particular
Conference (as a subset of its Division) 3) All Divisions (and thus all
Conferences) 4,5 & 6) Same as 1,2 & 3 except instead of a particular seasons
these would include all the seasons.

The query is activated by a control button on a form, the form has combo
boxes to enter the Season, Division & Conference of choice, these are stored
in the [Filter Parameter] .

Because you're using the Division and Conference fields as linking fields,
those fields need to have a value for each record in Filter Parameters
table. Otherwise, Jet database engine will not find matches in the other
tables.

You say you're using a form to put the values into Filter Parameters table.
I assume that the form is bound to this table, and you're selecting a single
value each for the Division and Conference fields? Thus, your query would
run using a single record in Filter Parameters table?

If you want the option to have the query run for all values of Division and
Conference when you leave the combo box blank, you'll need to put some code
in the form (likely on the Click event of a command button) that will create
records in the Filter Parameters table for each value that exist for
Division and Conference. This will give multiple records in that table,
thereby allowing your query to find all the desired matching records.

However, if your Filter Parameters table is set up with the Division field
and the Conference field in the record, then your query won't work correctly
because, with multiple records in the Filter Parameters table, you'll need
every possible combination of Division and Conference values (if you leave
both empty on your form). In this case (you leave both empty), you'll get
multiple, duplicative records in the query's result because of the many
existences of the same value for, say, Division in the table.

Or, if you leave just one of the options (Division or Conference) empty,
then your form's code would need to create multiple records where each
record has the same value of whichever one you select on the form and each
record will have one of the possible values for the one you didn't select.
In this case, you'd get the desired result, but only by creating the records
in the table by code before you run the query.

I can help you with some code that will create records in the Filter
Parameters table, but would need to know the situation that will be used --
will just one (Division or Conference) be left empty (or have as its
selection some "all" option in your combo box's dropdown list)? or will both
potentially be left unselected? Also, how are you currently running this
query -- by a command button on a form? by manually opening it? by code?
--

Ken Snell
<MS ACCESS MVP>

Filter Parameters is a table that I use to store input from a form. Division
and Conference are fields in the Filter parameter table and have seperate
controls on the input form.

Current query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date, [104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104 Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters] INNER JOIN (([Season Division Conference School]
INNER JOIN [104 Adjusted Home Scores] ON ([Season Division Conference
School].School = [104 Adjusted Home Scores].Home) AND ([Season Division
Conference School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season Division Conference School] AS [Season Division Conference School_1]
INNER JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID) ON ([Filter
parameters].Conference = [104 Adjusted Home Scores].[Home Conference]) AND
([Filter parameters].Division = [104 Adjusted Home Scores].[Home Division])
AND ([Filter parameters].Season = [104 Adjusted Home Scores].Season) AND
([Filter parameters].Conference = [105 Adjusted Away Scores].[Visitors
Conference]) AND ([Filter parameters].Division = [105 Adjusted Away
Scores].[Visitors Division]) AND ([Filter parameters].Season = [105 Adjusted
Away Scores].Season)
WHERE ((([104 Adjusted Home Scores].Nuetral)=False) AND (([105

Adjusted
Away

Scores].Nuetral)=False));



:

Post the SQL that you now have. Filter Parameters is the name of a

form?
And

Division and Conference aret the names of controls on that form?

--

Ken Snell
<MS ACCESS MVP>

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop down
option in the [filter parameter] table but it does work - I assume the
query
reads it as text and not as a "is not blank" statement.


:

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results

with
WHERE

clauses. My experience has been that cartesian queries can take

a
while

to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined correctly
so
that you don't need the WHERE clauses that match the records

between
the

joining tables? Meaning that your query would contain a double join
between
104 Adjusted Home Scores and Filter Parameters tables on the [104
Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104
Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join;

and
a

join
between Season Division Conference School and Filter Parameters tables
on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season Division
Conference School_1].Division=[Filter parameters].[Division]

join;
and a

join between 105 Adjusted Away Scores and Filter Parameters

tables
on

the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>



Yes, freezes meaning run very slowly if at all. Ctrl alt del = program
(access) not responding.

The two query statements are essentially the same, the first one
posted is
my complete query. The second, I removed the stuff I though was
non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if

that
maks

any
difference) you mention and I will review the posting you

listed
but

I
would
be suprised if it is causing a problem since it works in this query if
I
drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The

query
has

two
sets
of conferences and divisions. The away(visitor) team has a conference
and
a
division it belongs to, as does the home team, each is

represented
by

a
column in the query (total of 4 columns). I can run the query with
either
the
conference or the division of the home and the away team with a
criteria,
but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away

Scores].Visitor,
[105

Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted

Score],
[104

Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER
JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference
School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season
Division Conference School] AS [Season Division Conference School_1]
INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away

Scores].Season))
ON

[104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter
parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104
Adjusted
Home
Scores].Nuetral)=False) AND (([105 Adjusted Away
Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home
Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor,
[105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted

Score],
[104

Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School] INNER
JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference
School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season
Division Conference School] AS [Season Division Conference School_1]
INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away

Scores].Season))
ON

[104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter
parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104
Adjusted
Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND
(([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home
Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));


< snipped rest of threaded message >

 

[Guest]

Thanks - I'll let you know when I've got it completed.

Yes, this is a fairly complex setup that you require. It will require a lot
of code to handle all of the scenarios. I don't have the time available to
provide you with code for all the possible scenarios, so I'll use the one
where you want to choose a Division and get all the Conferences associated
to that Division. You then can work with that to write all the additional
code that you'll need for the other scenarios.

In the code that runs when you click the command button (which eventually
opens the query), you'll need to add steps similar to the following (for
this one scenerio that I'm using as the example):

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim lngC As Long
Dim strSQL As String
Set dbs = CurrentDb

' Delete any records currently in the Filter Parameters table
strSQL = "DELETE * FROM [Filter Parameters];"
dbs.Execute strSQL, dbFailOnError

' Test if a division has been selected without a conference selection
If Len(Me.Division.Value & "") > 0 And _
Len(Me.Conference.Value & "") = 0 Then

' Open a recordset to Filter Parameters table
Set rst = dbs.OpenRecordset("Filter Parameters", _
dbOpenDynaset, dbAppendOnly)

' Need to get the division name and all the conference names, and write
' them to the Filter Parameters table. Code assumes that the Conference
' control is a combo box and that it holds the conferences for the
' selected division.
' ... loop through all rows in Conference dropdown list
For lngC = 1 To Me.Conference.ListCount
' ...code assumes that the Bound Column of the combo box holds
' the name of the conference
rst.AddNew
rst.Fields("Division").Value = Me.Division.Value
rst.Fields("Conference").Value = _
Me.Conference.Column(Me.Conference.BoundColumn, _
lngC - 1)
rst.Update
Next lngC

' Close the recordset to the Filter Parameters table
rst.Close
Set rst = Nothing
End If
dbs.Close
Set dbs = Nothing
DoCmd.OpenQuery "NameOfQueryThatYou'reRunning"


--

Ken Snell
<MS ACCESS MVP>

Wow, this is getting complicated but I do appreciate the help.

Answers:

Yes, I have a form bound to the table [Filter Parameters].
Yes, the the query will run on one input recordset (row) from the Filter
Parameters table.

There are six senerios that would be used for the query. 1) A particular
Division with the all Conferences in that Division. 2) A particular
Conference (as a subset of its Division) 3) All Divisions (and thus all
Conferences) 4,5 & 6) Same as 1,2 & 3 except instead of a particular seasons
these would include all the seasons.

The query is activated by a control button on a form, the form has combo
boxes to enter the Season, Division & Conference of choice, these are stored
in the [Filter Parameter] .

Because you're using the Division and Conference fields as linking fields,
those fields need to have a value for each record in Filter Parameters
table. Otherwise, Jet database engine will not find matches in the other
tables.

You say you're using a form to put the values into Filter Parameters table.
I assume that the form is bound to this table, and you're selecting a single
value each for the Division and Conference fields? Thus, your query would
run using a single record in Filter Parameters table?

If you want the option to have the query run for all values of Division and
Conference when you leave the combo box blank, you'll need to put some code
in the form (likely on the Click event of a command button) that will create
records in the Filter Parameters table for each value that exist for
Division and Conference. This will give multiple records in that table,
thereby allowing your query to find all the desired matching records.

However, if your Filter Parameters table is set up with the Division field
and the Conference field in the record, then your query won't work correctly
because, with multiple records in the Filter Parameters table, you'll need
every possible combination of Division and Conference values (if you leave
both empty on your form). In this case (you leave both empty), you'll get
multiple, duplicative records in the query's result because of the many
existences of the same value for, say, Division in the table.

Or, if you leave just one of the options (Division or Conference) empty,
then your form's code would need to create multiple records where each
record has the same value of whichever one you select on the form and each
record will have one of the possible values for the one you didn't select.
In this case, you'd get the desired result, but only by creating the records
in the table by code before you run the query.

I can help you with some code that will create records in the Filter
Parameters table, but would need to know the situation that will be used --
will just one (Division or Conference) be left empty (or have as its
selection some "all" option in your combo box's dropdown list)? or will both
potentially be left unselected? Also, how are you currently running this
query -- by a command button on a form? by manually opening it? by code?
--

Ken Snell
<MS ACCESS MVP>


Filter Parameters is a table that I use to store input from a form.
Division
and Conference are fields in the Filter parameter table and have seperate
controls on the input form.

Current query is:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor, [105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score], [104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away Scores].Nuetral
FROM [Filter parameters] INNER JOIN (([Season Division Conference School]
INNER JOIN [104 Adjusted Home Scores] ON ([Season Division Conference
School].School = [104 Adjusted Home Scores].Home) AND ([Season Division
Conference School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season Division Conference School] AS [Season Division Conference
School_1]
INNER JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND ([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season)) ON [104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID) ON ([Filter
parameters].Conference = [104 Adjusted Home Scores].[Home Conference]) AND
([Filter parameters].Division = [104 Adjusted Home Scores].[Home
Division])
AND ([Filter parameters].Season = [104 Adjusted Home Scores].Season) AND
([Filter parameters].Conference = [105 Adjusted Away Scores].[Visitors
Conference]) AND ([Filter parameters].Division = [105 Adjusted Away
Scores].[Visitors Division]) AND ([Filter parameters].Season = [105
Adjusted
Away Scores].Season)
WHERE ((([104 Adjusted Home Scores].Nuetral)=False) AND (([105 Adjusted
Away
Scores].Nuetral)=False));



:

Post the SQL that you now have. Filter Parameters is the name of a form?
And
Division and Conference aret the names of controls on that form?

--

Ken Snell
<MS ACCESS MVP>

Thanks - this works except, if I leave blank the [filter parameters]
conference or division field, which would be the case if I wanted all
conferences and/or divisions. I've tried adding a <>"" to the drop
down
option in the [filter parameter] table but it does work - I assume the
query
reads it as text and not as a "is not blank" statement.


:

OK - so the "offending" WHERE clause excerpt is

(([104 Adjusted Home Scores].[Home Conference])=[Filter
parameters]![Conference]) AND
(([105 Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND

You're using a cartesian query and then filtering the results with
WHERE
clauses. My experience has been that cartesian queries can take a
while
to
run, especially if you have multiple WHERE clauses.

Have you tried setting up a query where the tables are joined
correctly
so
that you don't need the WHERE clauses that match the records between
the
joining tables? Meaning that your query would contain a double join
between
104 Adjusted Home Scores and Filter Parameters tables on the [104
Adjusted
Home Scores].Season=[Filter parameters].[Season] join and the [104
Adjusted
Home
Scores].[Home Conference]=[Filter parameters].[Conference] join; and
a
join
between Season Division Conference School and Filter Parameters
tables
on
the [Season Division Conference School].Division)=[Filter
parameters].[Division] join; and a join between the Season Division
Conference School_1 and Filter Parameters tables on the [Season
Division
Conference School_1].Division=[Filter parameters].[Division] join;
and a
join between 105 Adjusted Away Scores and Filter Parameters tables
on
the
[105 Adjusted Away Scores].[Visitors Conference]=[Filter
parameters].[Conference] join.
--

Ken Snell
<MS ACCESS MVP>



Yes, freezes meaning run very slowly if at all. Ctrl alt del =
program
(access) not responding.

The two query statements are essentially the same, the first one
posted is
my complete query. The second, I removed the stuff I though was
non-essential
in understanding the problem, but I let you see both just in case.

The date thing, (my use is as an actual date in history if that
maks
any
difference) you mention and I will review the posting you listed
but
I
would
be suprised if it is causing a problem since it works in this
query if
I
drop
eithor the Conference or the Division "filter parameters".

Sorry, for the previous note(below) being misleading. The query
has
two
sets
of conferences and divisions. The away(visitor) team has a
conference
and
a
division it belongs to, as does the home team, each is represented
by
a
column in the query (total of 4 columns). I can run the query with
either
the
conference or the division of the home and the away team with a
criteria,
but
it freezes when I include both the conference and division "filter
parameters" in the criteria for both the away and the home team.

So this works:

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home
Scores].Date,
[104
Adjusted Home Scores].Season, [105 Adjusted Away Scores].Visitor,
[105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score],
[104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away
Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away
Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School]
INNER
JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference
School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season
Division Conference School] AS [Season Division Conference
School_1]
INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND
([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season))
ON
[104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter
parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104
Adjusted
Home
Scores].Nuetral)=False) AND (([105 Adjusted Away
Scores].Nuetral)=False));

this does not(freezes):

SELECT [104 Adjusted Home Scores].ID, [104 Adjusted Home
Scores].Date,
[104 Adjusted Home Scores].Season, [105 Adjusted Away
Scores].Visitor,
[105
Adjusted Away Scores].[Home Adjusted Margin], [104 Adjusted Home
Scores].Home, [104 Adjusted Home Scores].[Home Adjusted Score],
[104
Adjusted
Home Scores]![Home Adjusted Score]-[105 Adjusted Away
Scores]![Home
Adjusted
Margin] AS [Adjusted Margin], [Season Division Conference
School].Division,
[Season Division Conference School_1].Division, [104 Adjusted Home
Scores].[Home Conference], [105 Adjusted Away Scores].[Visitors
Conference],
[104 Adjusted Home Scores].Nuetral, [105 Adjusted Away
Scores].Nuetral
FROM [Filter parameters], ([Season Division Conference School]
INNER
JOIN
[104 Adjusted Home Scores] ON ([Season Division Conference
School].School
=
[104 Adjusted Home Scores].Home) AND ([Season Division Conference
School].Season = [104 Adjusted Home Scores].Season)) INNER JOIN
([Season
Division Conference School] AS [Season Division Conference
School_1]
INNER
JOIN [105 Adjusted Away Scores] ON ([Season Division Conference
School_1].School = [105 Adjusted Away Scores].Visitor) AND
([Season
Division
Conference School_1].Season = [105 Adjusted Away Scores].Season))
ON
[104
Adjusted Home Scores].ID = [105 Adjusted Away Scores].ID
WHERE ((([104 Adjusted Home Scores].Season)=[Filter
parameters]![Season])
AND (([Season Division Conference School].Division)=[Filter
parameters]![Division]) AND (([Season Division Conference
School_1].Division)=[Filter parameters]![Division]) AND (([104
Adjusted
Home
Scores].[Home Conference])=[Filter parameters]![Conference]) AND
(([105
Adjusted Away Scores].[Visitors Conference])=[Filter
parameters]![Conference]) AND (([104 Adjusted Home
Scores].Nuetral)=False)
AND (([105 Adjusted Away Scores].Nuetral)=False));


< snipped rest of threaded message >