Need advice on speeding query up

[Jon]

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[Jerry Whittle]

Index tblPurchasesSince_200511 status_id, customer_short_name, and
purchase_dte.

Speaking of tblPurchasesSince_200511.purchase_dte, your use of the Year and
Month functions actually make an index on that column worthless plus have to
search the table twice. You would be better off having one field on the form
where the month and date is entered together like June 2006. Then the
criteria would look like:

"1 " & Forms!frmReportGeneration!txtStartDate

Index tblCustomer.[Short Name]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[Jeff Boyce]

Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any
field on which your queries sort provides a potential slow down. Do you
have indexes on all of those?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[John Spencer]

Change your first query so it can use the index on the date field

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name


WHERE Purchase_Dte

=DateSerial(Forms!frmReportGeneration!txtStartYear,Forms!frmReportGeneration!txtStartMonth,1) and
Purchase_Dte
=DateSerial(Forms!frmReportGeneration!txtStartYear,Forms!frmReportGeneration!txtStartMonth+1,0)

and tblPurchasesSince_200511.status_id<>3

GROUP BY tblCustomer.[Full Name];

Query 2. Not In is slow. You might be able to improve your performance
with the following.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC

UNION ALL

SELECT "Other", Sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate as Q1 INNER JOIN
(SELECT TOP 25 Q2.*
FROM qryCustomerAggregate as Q2
ORDER BY Q2.Total_Cost DESC) as Q3
ON Q1.Name = Q3.Name
WHERE Q3.Name is Null

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[Jon]

Yup. Said so in the original post.

Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any
field on which your queries sort provides a potential slow down. Do you
have indexes on all of those?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[Jon]

Woops. Me bad. Full Name in tblCustomer was *not* indexed, and since
that has a GROUP BY on it...

Yup. Said so in the original post.

Any field your queries use in ORDER BY, GROUP BY or WHERE clauses, and any
field on which your queries sort provides a potential slow down. Do you
have indexes on all of those?

Regards

Jeff Boyce
Microsoft Office/Access MVP

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[Jon]

I'm looking at your comment and also John Spenser's comment about the
date fields (txtStartMonth & txtStartYear) and I'm wondering if I can't
change that to something more efficient...

Those two fields are actually unbound text fields which are pointing to
specific columns of a combo box, which in turn points to a query. I
structured it like that so I could readily see what values were being
passed along to the query called qryCustomerAggregate - as you can
guess, I'm not too experienced at this so I need some reassurance as
I'm coding that I'm not making a misstep. The combo box is so the user
can readily choose which month he wishes. Here's the query that
populates the combo box, called qryMonthID:

SELECT Year([purchase_dte]) AS YearID, MonthName(Month([purchase_dte]))
AS MonthNameID, MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2) AS FullID, Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]) AS SortID,
Month([purchase_dte]) AS MonthID
FROM tblPurchasesSince_200511
GROUP BY Year([purchase_dte]), MonthName(Month([purchase_dte])),
MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2), Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]),
Month([purchase_dte])
ORDER BY Year([purchase_dte]) & IIf(Month([purchase_dte])<10,"0","") &
Month([purchase_dte]);

Which ends up generating something like this for each line:

YearID 2005
MonthNameID November
FullID Nov/05
SortID 200511
MonthID 11

There's only one visible field on the combo box, which is the FullID
field in the query. All the other fields in qryMonthID are present in
the combo box - among other things, the report all these querys end up
in makes use of the MonthNameID field in a couple of unbound text
boxes.

Also, just so you understand, the purchase_dte field is the a date
value, right down to the millisecond, and the earliest date in it is
late 2003 (the table name is a bit of a misnomer).

Is there a more efficient way of passing along a month & year
constraint to a query?

Index tblPurchasesSince_200511 status_id, customer_short_name, and
purchase_dte.

Speaking of tblPurchasesSince_200511.purchase_dte, your use of the Year and
Month functions actually make an index on that column worthless plus have to
search the table twice. You would be better off having one field on the form
where the month and date is entered together like June 2006. Then the
criteria would look like:

"1 " & Forms!frmReportGeneration!txtStartDate

Index tblCustomer.[Short Name]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[John Spencer]

Are you sure that txtStartMonth points to the MonthNameId column? If it
does I would expect to see an error in your first query since the Month
function in the following should be returning an integer value 1 to 12 and
then comparing that to a text value (January, February, etc). I guessed
that txtStartMonth was numeric, since if it wasn't I would have expected an
error.

Month(tblPurchasesSince_200511.purchase_dte)=Forms!frmReportGeneration!txtStartMonth

Your query for the combobox could be rewritten as

SELECT DISTINCT Year([purchase_dte]) AS YearID
, FORMAT([purchase_dte],"mmmm") AS MonthNameID
, FORMAT([purchase_dte], "mmm\/yy") AS FullID
, FORMAT([purchase_dte],"yyyymm") AS SortID
, Month([purchase_dte]) AS MonthID
FROM tblPurchasesSince_200511
ORDER BY FORMAT([purchase_dte],"yyyymm")

Or you can change the order by to

ORDER BY Year(Purchase_dte), Month(Purchase_dte)

And then you could drop ", FORMAT([purchase_dte],"yyyymm") AS SortID" from
the query.

I'm looking at your comment and also John Spenser's comment about the
date fields (txtStartMonth & txtStartYear) and I'm wondering if I can't
change that to something more efficient...

Those two fields are actually unbound text fields which are pointing to
specific columns of a combo box, which in turn points to a query. I
structured it like that so I could readily see what values were being
passed along to the query called qryCustomerAggregate - as you can
guess, I'm not too experienced at this so I need some reassurance as
I'm coding that I'm not making a misstep. The combo box is so the user
can readily choose which month he wishes. Here's the query that
populates the combo box, called qryMonthID:

SELECT Year([purchase_dte]) AS YearID, MonthName(Month([purchase_dte]))
AS MonthNameID, MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2) AS FullID, Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]) AS SortID,
Month([purchase_dte]) AS MonthID
FROM tblPurchasesSince_200511
GROUP BY Year([purchase_dte]), MonthName(Month([purchase_dte])),
MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2), Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]),
Month([purchase_dte])
ORDER BY Year([purchase_dte]) & IIf(Month([purchase_dte])<10,"0","") &
Month([purchase_dte]);

Which ends up generating something like this for each line:

YearID 2005
MonthNameID November
FullID Nov/05
SortID 200511
MonthID 11

There's only one visible field on the combo box, which is the FullID
field in the query. All the other fields in qryMonthID are present in
the combo box - among other things, the report all these querys end up
in makes use of the MonthNameID field in a couple of unbound text
boxes.

Also, just so you understand, the purchase_dte field is the a date
value, right down to the millisecond, and the earliest date in it is
late 2003 (the table name is a bit of a misnomer).

Is there a more efficient way of passing along a month & year
constraint to a query?

Index tblPurchasesSince_200511 status_id, customer_short_name, and
purchase_dte.

Speaking of tblPurchasesSince_200511.purchase_dte, your use of the Year
and
Month functions actually make an index on that column worthless plus have
to
search the table twice. You would be better off having one field on the
form
where the month and date is entered together like June 2006. Then the
criteria would look like:

"1 " & Forms!frmReportGeneration!txtStartDate

Index tblCustomer.[Short Name]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?

 

[Jon]

I should have been clearer... txtStartMonth points to the MonthID
field, meaning it has the value as a numeric. The MonthNameID value
appears a part of the report's header and footer, and does not come
into the report's body at all.

And thanks again for the info & advise folks - it is much appreciated.

Are you sure that txtStartMonth points to the MonthNameId column? If it
does I would expect to see an error in your first query since the Month
function in the following should be returning an integer value 1 to 12 and
then comparing that to a text value (January, February, etc). I guessed
that txtStartMonth was numeric, since if it wasn't I would have expected an
error.

Month(tblPurchasesSince_200511.purchase_dte)=Forms!frmReportGeneration!txtStartMonth

Your query for the combobox could be rewritten as

SELECT DISTINCT Year([purchase_dte]) AS YearID
, FORMAT([purchase_dte],"mmmm") AS MonthNameID
, FORMAT([purchase_dte], "mmm\/yy") AS FullID
, FORMAT([purchase_dte],"yyyymm") AS SortID
, Month([purchase_dte]) AS MonthID
FROM tblPurchasesSince_200511
ORDER BY FORMAT([purchase_dte],"yyyymm")

Or you can change the order by to

ORDER BY Year(Purchase_dte), Month(Purchase_dte)

And then you could drop ", FORMAT([purchase_dte],"yyyymm") AS SortID" from
the query.

I'm looking at your comment and also John Spenser's comment about the
date fields (txtStartMonth & txtStartYear) and I'm wondering if I can't
change that to something more efficient...

Those two fields are actually unbound text fields which are pointing to
specific columns of a combo box, which in turn points to a query. I
structured it like that so I could readily see what values were being
passed along to the query called qryCustomerAggregate - as you can
guess, I'm not too experienced at this so I need some reassurance as
I'm coding that I'm not making a misstep. The combo box is so the user
can readily choose which month he wishes. Here's the query that
populates the combo box, called qryMonthID:

SELECT Year([purchase_dte]) AS YearID, MonthName(Month([purchase_dte]))
AS MonthNameID, MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2) AS FullID, Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]) AS SortID,
Month([purchase_dte]) AS MonthID
FROM tblPurchasesSince_200511
GROUP BY Year([purchase_dte]), MonthName(Month([purchase_dte])),
MonthName(Month([purchase_dte]),True) & "/" &
Right(Year([purchase_dte]),2), Year([purchase_dte]) &
IIf(Month([purchase_dte])<10,"0","") & Month([purchase_dte]),
Month([purchase_dte])
ORDER BY Year([purchase_dte]) & IIf(Month([purchase_dte])<10,"0","") &
Month([purchase_dte]);

Which ends up generating something like this for each line:

YearID 2005
MonthNameID November
FullID Nov/05
SortID 200511
MonthID 11

There's only one visible field on the combo box, which is the FullID
field in the query. All the other fields in qryMonthID are present in
the combo box - among other things, the report all these querys end up
in makes use of the MonthNameID field in a couple of unbound text
boxes.

Also, just so you understand, the purchase_dte field is the a date
value, right down to the millisecond, and the earliest date in it is
late 2003 (the table name is a bit of a misnomer).

Is there a more efficient way of passing along a month & year
constraint to a query?

Index tblPurchasesSince_200511 status_id, customer_short_name, and
purchase_dte.

Speaking of tblPurchasesSince_200511.purchase_dte, your use of the Year
and
Month functions actually make an index on that column worthless plus have
to
search the table twice. You would be better off having one field on the
form
where the month and date is entered together like June 2006. Then the
criteria would look like:

"1 " & Forms!frmReportGeneration!txtStartDate

Index tblCustomer.[Short Name]
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


:

I have a pair of queries, the one (a Select query) which feeds info
into the next (a Crosstab query), and then the second query is fed to a
chart. As the amount of data present slowly grows, the queries are
getting exponentially slower, and I'm looking to pick the collective
brains of the group for advice on how to speed it all up.

The first query is called qryCustomerAggregate, and is used to select
all the records for a specific month and a specific order status, then
summarize it for passing to the next query.

SELECT tblCustomer.[Full Name] AS Name,
Count(tblPurchasesSince_200511.total_cost) AS Total_Orders,
Sum(tblPurchasesSince_200511.total_cost) AS Total_Cost
FROM tblCustomer INNER JOIN tblPurchasesSince_200511 ON
tblCustomer.[Short Name]=tblPurchasesSince_200511.customer_short_name
WHERE (((tblPurchasesSince_200511.status_id)<>3) And
((Month(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartMonth)
And
((Year(tblPurchasesSince_200511.purchase_dte))=Forms!frmReportGeneration!txtStartYear))
GROUP BY tblCustomer.[Full Name];

Here's the second one (qryPOByCustomerDollar), which takes the first
query then gives me a Top 25 cross tab based on the records above, plus
last line with the total of everything else in the first query.

SELECT TOP 25 qryCustomerAggregate.Name,
qryCustomerAggregate.Total_Cost
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC
UNION ALL SELECT "Other", sum(qryCustomerAggregate.Total_Cost)
FROM qryCustomerAggregate
WHERE qryCustomerAggregate.Name NOT IN (
SELECT TOP 25 qryCustomerAggregate.Name
FROM qryCustomerAggregate
ORDER BY qryCustomerAggregate.Total_Cost DESC);

The first query does double duty as the Total_Orders info is fed into a
query called qryPOByCustomerOrders, which is otherise identical the
qryPOByCustomerDollar except where the one says Total_Cost, the other
says Total_Orders.

I thought about splitting the first query in two: first run one query
to select against the criteria, then a second to summarize it all, but
that came back with the error "Too Complex". And yes, the fields
purchase_dte, customer_short_name, and total_cost are all indexed.

Any suggestions from the peanut gallery?