Any way of speeding up a very large query?

[Guest]

I am not sure if there is a way of speeding this up, or if I am just trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run the
report as many times as I like, but as soon as I run the report then go into
the rpt design view, then try and run the report again it gives me the error
"cannot open any more databases", at which point I have to close access and
re open it.

Query Description: Basically if the svc departments section is 1 and the
diagnostic test run is one of the codes in the test codes table who is in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the sum.
The NZ function is there to ensure that there is a 0 put in the field if the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic etc...

** Please note that there is no specific car description in qry01_Base, or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it runs
the following code 60 times(Each in a different column in query design view)
in one query for each different car.
“nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004â€

In addition to that I have three more queries that do the exact same thing
but for a different year. Then I use one more query who references all the
other queries to group them together so that I can bind them to the report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.

 

[Allen Browne]

It's a little difficult to know where to start here, and also difficult to
know why you are doing what you are doing, but here's a start.

1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#

2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt() too.

3. Structure
Not sure I followed this, but field names like [t10-section] and [t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and so
on.) If so, these tables need to be broken up into related tables with many
records instead of having a table with lots of fields. This is a fundamental
change, but it will probably be the thing you need to sort out all the
issues.

4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.

If I have not understood what you are doing, please feel free to ignore this
reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am not sure if there is a way of speeding this up, or if I am just trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.

Query Description: Basically if the svc departments section is 1 and the
diagnostic test run is one of the codes in the test codes table who is in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field if
the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...

** Please note that there is no specific car description in qry01_Base, or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"

In addition to that I have three more queries that do the exact same thing
but for a different year. Then I use one more query who references all the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.

 

[Guest]

Awesome, I did not know that calling VBA would slow things down. I will get
on that immediatly.

Thanks you for the Cint() tip as that will totally work.

Unfortunaly the table has been normalized to the best of the existing data's
ability.
There are two tables thus far being:

T02_TestCodes consisting of Group, Name, and Description all which are never
null and is normalized

T10-TestResults cosisting of: T10-Invoice, T10-clientID,T10-Datein,
T10-Dateout, T10-rptdate,T10-Test,T10-Result,T10-Description,T10-section

Unfortunatly the goal is to count which types of cars had work done by year
and the only way to derive the car is to interogate the section and the
result, because they are unfortunatly missing a "cardescription/type" field
which would make my life easier.

Would it be of any benifit to create a query that would look at the existing
table and just identify which type of car it is, then build a cross tab from
there? or is that pretty much the same issue?

Is there any benifit to creating an MDE once I am done to improve
performance? All my projects are always created in two databases; a datastore
for just the tables, and a front end for all the querys, forms, reports etc.
Will that be an issue?

Well I can't thank you enough for your quick response and all of your
assistance as you have been a great help.

Have a great night.

James.

It's a little difficult to know where to start here, and also difficult to
know why you are doing what you are doing, but here's a start.

1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#

2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt() too.

3. Structure
Not sure I followed this, but field names like [t10-section] and [t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and so
on.) If so, these tables need to be broken up into related tables with many
records instead of having a table with lots of fields. This is a fundamental
change, but it will probably be the thing you need to sort out all the
issues.

4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.

If I have not understood what you are doing, please feel free to ignore this
reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am not sure if there is a way of speeding this up, or if I am just trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.

Query Description: Basically if the svc departments section is 1 and the
diagnostic test run is one of the codes in the test codes table who is in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field if
the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...

** Please note that there is no specific car description in qry01_Base, or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"

In addition to that I have three more queries that do the exact same thing
but for a different year. Then I use one more query who references all the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.

 

[John Spencer]

One added suggestion:
Create a table that relates the TestCodes.Group to the CarType and use that
in your query.
Table: CarTestGroup
TestCode: Number; Integer
CarType: Text

If CarType is also dependent on Section, then you may also need a field in
CarTestGroup that holds that value.

Awesome, I did not know that calling VBA would slow things down. I will
get
on that immediatly.

Thanks you for the Cint() tip as that will totally work.

Unfortunaly the table has been normalized to the best of the existing
data's
ability.
There are two tables thus far being:

T02_TestCodes consisting of Group, Name, and Description all which are
never
null and is normalized

T10-TestResults cosisting of: T10-Invoice, T10-clientID,T10-Datein,
T10-Dateout, T10-rptdate,T10-Test,T10-Result,T10-Description,T10-section

Unfortunatly the goal is to count which types of cars had work done by
year
and the only way to derive the car is to interogate the section and the
result, because they are unfortunatly missing a "cardescription/type"
field
which would make my life easier.

Would it be of any benifit to create a query that would look at the
existing
table and just identify which type of car it is, then build a cross tab
from
there? or is that pretty much the same issue?

Is there any benifit to creating an MDE once I am done to improve
performance? All my projects are always created in two databases; a
datastore
for just the tables, and a front end for all the querys, forms, reports
etc.
Will that be an issue?

Well I can't thank you enough for your quick response and all of your
assistance as you have been a great help.

Have a great night.

James.

It's a little difficult to know where to start here, and also difficult
to
know why you are doing what you are doing, but here's a start.

1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have
the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#

2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt()
too.

3. Structure
Not sure I followed this, but field names like [t10-section] and
[t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and
so
on.) If so, these tables need to be broken up into related tables with
many
records instead of having a table with lots of fields. This is a
fundamental
change, but it will probably be the thing you need to sort out all the
issues.

4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.

If I have not understood what you are doing, please feel free to ignore
this
reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am not sure if there is a way of speeding this up, or if I am just
trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run
the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.

Query Description: Basically if the svc departments section is 1 and
the
diagnostic test run is one of the codes in the test codes table who is
in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field
if
the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for
a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...

** Please note that there is no specific car description in qry01_Base,
or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"

In addition to that I have three more queries that do the exact same
thing
but for a different year. Then I use one more query who references all
the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.

 

[Guest]

Any Ideas why I would be getting the following Error:
"Cannot open any more databases. (Error 3048)"
You have reached the limit on the number of databases that can be opened at
one time. Close one or more databases and try the operation again.

This only occurs when I run the report, go into design view, run the report,
go into design view, then run the report again I get the message.

I noticed That this may be due to the amount of fields in the query that the
report is based on. When the query only has 80 fields it works fine, but
when I get close to around 90-100 it starts to occur.

Any info would be really great.

Thx.
James.

It's a little difficult to know where to start here, and also difficult to
know why you are doing what you are doing, but here's a start.

1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#

2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt() too.

3. Structure
Not sure I followed this, but field names like [t10-section] and [t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and so
on.) If so, these tables need to be broken up into related tables with many
records instead of having a table with lots of fields. This is a fundamental
change, but it will probably be the thing you need to sort out all the
issues.

4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.

If I have not understood what you are doing, please feel free to ignore this
reply.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I am not sure if there is a way of speeding this up, or if I am just trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.

Query Description: Basically if the svc departments section is 1 and the
diagnostic test run is one of the codes in the test codes table who is in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field if
the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...

** Please note that there is no specific car description in qry01_Base, or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"

In addition to that I have three more queries that do the exact same thing
but for a different year. Then I use one more query who references all the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.

 

[Allen Browne]

With your 240 fields delivered through queries stacked on top of queries,
you have too many connections to the data. Access does not release them all
instantly, which is why it works the first time and not the second.

James, I'm still not convinced the data is sotred correctly. Without knowing
what you are doing, it would seem that you are testing vehicles. Each test
might have several subtests or perhaps related tests, or might need
re-testing on another date -- who knows what you need. So I can't suggest a
better structure for you, but I still suspect that is at the root of the
issue here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Any Ideas why I would be getting the following Error:
"Cannot open any more databases. (Error 3048)"
You have reached the limit on the number of databases that can be opened
at
one time. Close one or more databases and try the operation again.

This only occurs when I run the report, go into design view, run the
report,
go into design view, then run the report again I get the message.

I noticed That this may be due to the amount of fields in the query that
the
report is based on. When the query only has 80 fields it works fine, but
when I get close to around 90-100 it starts to occur.

Any info would be really great.

Thx.
James.

It's a little difficult to know where to start here, and also difficult
to
know why you are doing what you are doing, but here's a start.

1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have
the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#

2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt()
too.

3. Structure
Not sure I followed this, but field names like [t10-section] and
[t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and
so
on.) If so, these tables need to be broken up into related tables with
many
records instead of having a table with lots of fields. This is a
fundamental
change, but it will probably be the thing you need to sort out all the
issues.

4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.

If I have not understood what you are doing, please feel free to ignore
this
reply.

I am not sure if there is a way of speeding this up, or if I am just
trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run
the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.

Query Description: Basically if the svc departments section is 1 and
the
diagnostic test run is one of the codes in the test codes table who is
in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field
if
the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for
a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...

** Please note that there is no specific car description in qry01_Base,
or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"

In addition to that I have three more queries that do the exact same
thing
but for a different year. Then I use one more query who references all
the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.

 

[Guest]

good, I will try and normalize the data more in the table by creating a new
one that will have the specific car in it as you suggested to then be able to
do a cross tab.

Thanks again for all of your help as it has got me thinking in the right
direction.

Have a good one.

With your 240 fields delivered through queries stacked on top of queries,
you have too many connections to the data. Access does not release them all
instantly, which is why it works the first time and not the second.

James, I'm still not convinced the data is sotred correctly. Without knowing
what you are doing, it would seem that you are testing vehicles. Each test
might have several subtests or perhaps related tests, or might need
re-testing on another date -- who knows what you need. So I can't suggest a
better structure for you, but I still suspect that is at the root of the
issue here.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Any Ideas why I would be getting the following Error:
"Cannot open any more databases. (Error 3048)"
You have reached the limit on the number of databases that can be opened
at
one time. Close one or more databases and try the operation again.

This only occurs when I run the report, go into design view, run the
report,
go into design view, then run the report again I get the message.

I noticed That this may be due to the amount of fields in the query that
the
report is based on. When the query only has 80 fields it works fine, but
when I get close to around 90-100 it starts to occur.

Any info would be really great.

Thx.
James.

It's a little difficult to know where to start here, and also difficult
to
know why you are doing what you are doing, but here's a start.

1. Function call in WHERE clause
The Year() function call is slowing the main query. Presumably you have
the
FormattedRptDate field indexed, but the VBA function call means JET can't
use the index. Replace:
Year([FormattedRptdDate])=2004
with:
FormattedRptDate Between #1/1/2004# And #12/31/2004#

2. Function call on parameter
This will make almost no difference to the speed, but declare parameter
(Parameters on Query menu) as an integer, and you can drop the CInt()
too.

3. Structure
Not sure I followed this, but field names like [t10-section] and
[t10-Test]
suggest that you have repeating field names (perhaps a t-11-section and
so
on.) If so, these tables need to be broken up into related tables with
many
records instead of having a table with lots of fields. This is a
fundamental
change, but it will probably be the thing you need to sort out all the
issues.

4. Crosstab
Once you have a normalized structure, you will be probably be able to
generate a crosstab query to give you the results you need, instead of
having to write 60 subqueries to get all the various columns.

If I have not understood what you are doing, please feel free to ignore
this
reply.

I am not sure if there is a way of speeding this up, or if I am just
trying
to do too much.

Problem: The problem is that it runs pretty slow. As well I can run
the
report as many times as I like, but as soon as I run the report then go
into
the rpt design view, then try and run the report again it gives me the
error
"cannot open any more databases", at which point I have to close access
and
re open it.

Query Description: Basically if the svc departments section is 1 and
the
diagnostic test run is one of the codes in the test codes table who is
in
group 10 put a 1 in the field, otherwise, put a zero. Then provide the
sum.
The NZ function is there to ensure that there is a 0 put in the field
if
the
sum turn out to be 0. This one is for a specific week.

**Please note the test codes table is all the test that can be run for
a
specific vehicle and are grouped accordingly eg 5 = Celecia, 3 = Civic
etc...

** Please note that there is no specific car description in qry01_Base,
or
anywhere for that matter.

Query code: SELECT nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name FROM T02_TestCodes WHERE
(((T02_TestCodes.Group)=10))),1,0)),0) AS MalibuRepairs2004
FROM qry01_Base
WHERE (((qry01_Base.Week) Between 1 And CInt([Please enter Week])) AND
((Year([FormattedRptdDate]))=2004))
WITH OWNERACCESS OPTION;

Now the reason that this is slow is that this query is slow is that it
runs
the following code 60 times(Each in a different column in query design
view)
in one query for each different car.
"nz(Sum(IIf([t10-section]=1 And [t10-Test] In (SELECT
T02_TestCodes.Name
FROM T02_TestCodes WHERE (((T02_TestCodes.Group)=11))),1,0)),0) AS
CeleicaRepairs2004"

In addition to that I have three more queries that do the exact same
thing
but for a different year. Then I use one more query who references all
the
other queries to group them together so that I can bind them to the
report.
So I really have 240 fields.

Is there a better way to do this? Is there anyway to speed this up?

Any assistance would be great.