Need Help with filtering in queries

[karen scheu via AccessMonster.com]

I am going crazy with this.

I have a table of 80,000 rows. It contains orders information. Some
orders are shipped and will have a shipped date filled in. I need to allow
the user to select a date range for the shipped field and also filter by
division and customer. What is the most efficient way to do this?

I have a print dialog screen the allows selection of begin date and end
date. I then want to list in a combo box only the distinct divisions for
the orders within the selected date range. The division combo box has a
rowsource = to a query that returns distinct divisions from my main shipped
query. HOw can I pass the date range in from the print dialog form so that
the combo box only displays the divisions that had shipped orders within a
date range? Also, I have noticed that performance of the print dialog
form opening is slow when there is no record source for the form. Why is
that?

Thanks for any help.
Karen

 

[Guest]

How are you filtering on Division?

 

[karen via AccessMonster.com]

I have a query called Qrpt201Summary which totals on division and order.
This query can return thousands of rows without a date range criteria added
to it. My main filter screen prompts user to enter the begin date and
end date and using code I will open the report and set the date range in
the query as follows:
strWhere = "[firstofgl] is not null and [firstofgl] Between #" & frm!
BeginningDate & "# AND #" & frm!EndingDate & "#"
DoCmd.OpenReport txtReportName, PrintMode, , strWhere

The above works fine. I also have code that checks if a division was
selected or if a customer was selected and adds SQL to the strWhere before
opening the report. That also works great. The problem I have is in the
building of the division and customer combo boxes. I only want the
division list and customer list to be the distinct values for the given
date range. Currently I am using the queries below to build the combo
boxes and the form takes a few seconds to open and that is using local
tables. I will be working with tables on a Share network drive. I really
need to get the queries below to be more efficient.

I have a division combo box with a recordsource as follows:

SELECT "00" as div_code," ALL" AS div_name from Qrpt201
UNION SELECT distinct Qrpt201.div_code, Qrpt201.div_name
FROM Qrpt201
ORDER BY div_name;
The default value of the combo box is "00"

Query Qrpt201 simply selects fields division, customer, shipdate, order
date, etc. with no totaling.

I then have a customer combo box which should display only the customers
for the selected division. If all is selected for division, then distinct
customers for all divisions will be displayed in the combo box. Here is
the query:

SELECT "00" as SDDAN8," ALL" AS ABALPH from Qrpt201
UNION SELECT Distinct Qrpt201.SDAN8 ,Qrpt201.ABALPH
FROM Qrpt201
WHERE
Qrpt201.div_code=IIf([cboDivision] ="00",[div_code],[cboDivision])
ORDER BY ABALPH;
I think what I need to do is include in the above two queries where date
between form!begindate and form!enddate.

If this makes sense and there is a better way to do this, please let me
know. Thanks.
Karen

 

[Guest]

I have a query called Qrpt201Summary which totals on division and order.
This query can return thousands of rows without a date range criteria added
to it. My main filter screen prompts user to enter the begin date and
end date and using code I will open the report and set the date range in
the query as follows:
strWhere = "[firstofgl] is not null and [firstofgl] Between #" & frm!
BeginningDate & "# AND #" & frm!EndingDate & "#"
DoCmd.OpenReport txtReportName, PrintMode, , strWhere

The above works fine. I also have code that checks if a division was
selected or if a customer was selected and adds SQL to the strWhere before
opening the report. That also works great. The problem I have is in the
building of the division and customer combo boxes. I only want the
division list and customer list to be the distinct values for the given
date range. Currently I am using the queries below to build the combo
boxes and the form takes a few seconds to open and that is using local
tables. I will be working with tables on a Share network drive. I really
need to get the queries below to be more efficient.

I have a division combo box with a recordsource as follows:

SELECT "00" as div_code," ALL" AS div_name from Qrpt201
UNION SELECT distinct Qrpt201.div_code, Qrpt201.div_name
FROM Qrpt201
ORDER BY div_name;
The default value of the combo box is "00"

Query Qrpt201 simply selects fields division, customer, shipdate, order
date, etc. with no totaling.

I then have a customer combo box which should display only the customers
for the selected division. If all is selected for division, then distinct
customers for all divisions will be displayed in the combo box. Here is
the query:

SELECT "00" as SDDAN8," ALL" AS ABALPH from Qrpt201
UNION SELECT Distinct Qrpt201.SDAN8 ,Qrpt201.ABALPH
FROM Qrpt201
WHERE
Qrpt201.div_code=IIf([cboDivision] ="00",[div_code],[cboDivision])
ORDER BY ABALPH;
I think what I need to do is include in the above two queries where date
between form!begindate and form!enddate.

If this makes sense and there is a better way to do this, please let me
know. Thanks.
Karen

 

[Guest]

Opps! sorry about that last blank post, I hit the Post button by mistake.

I think you are correct about filtering by date in the last queries, but
without being able to test, I can't be sure. Two things to consider that may
help performance a little:

strWhere = "[firstofgl] is not null and [firstofgl] Between #" & frm!
BeginningDate & "# AND #" & frm!EndingDate & "#"

WHERE
Qrpt201.div_code=IIf([cboDivision] ="00",[div_code],[cboDivision])

How about buiding a Where string here to eliminate the IIf statement?
Calculations in queries slow it down. perhaps you could make the desicion on
whether to use "00" or the [div_code] and put that in a string to use.

Sometimes things take a little time no matter how efficient. i usually have
on my forms a text box that is "Status". basically, I turn on the hour glass
and present a message saying something in a user perspective about what is
going on.

Is there are reason to check for is not null? A null date would not be
included in the date range. One less check per row would buy a little,
depending on how many rows in the table.

I have a query called Qrpt201Summary which totals on division and order.
This query can return thousands of rows without a date range criteria added
to it. My main filter screen prompts user to enter the begin date and
end date and using code I will open the report and set the date range in
the query as follows:
strWhere = "[firstofgl] is not null and [firstofgl] Between #" & frm!
BeginningDate & "# AND #" & frm!EndingDate & "#"
DoCmd.OpenReport txtReportName, PrintMode, , strWhere

The above works fine. I also have code that checks if a division was
selected or if a customer was selected and adds SQL to the strWhere before
opening the report. That also works great. The problem I have is in the
building of the division and customer combo boxes. I only want the
division list and customer list to be the distinct values for the given
date range. Currently I am using the queries below to build the combo
boxes and the form takes a few seconds to open and that is using local
tables. I will be working with tables on a Share network drive. I really
need to get the queries below to be more efficient.

I have a division combo box with a recordsource as follows:

SELECT "00" as div_code," ALL" AS div_name from Qrpt201
UNION SELECT distinct Qrpt201.div_code, Qrpt201.div_name
FROM Qrpt201
ORDER BY div_name;
The default value of the combo box is "00"

Query Qrpt201 simply selects fields division, customer, shipdate, order
date, etc. with no totaling.

I then have a customer combo box which should display only the customers
for the selected division. If all is selected for division, then distinct
customers for all divisions will be displayed in the combo box. Here is
the query:

SELECT "00" as SDDAN8," ALL" AS ABALPH from Qrpt201
UNION SELECT Distinct Qrpt201.SDAN8 ,Qrpt201.ABALPH
FROM Qrpt201
WHERE
Qrpt201.div_code=IIf([cboDivision] ="00",[div_code],[cboDivision])
ORDER BY ABALPH;
I think what I need to do is include in the above two queries where date
between form!begindate and form!enddate.

If this makes sense and there is a better way to do this, please let me
know. Thanks.
Karen