slow queries

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I need to show records according to fiscal year the 4 last
years and for the 4 last months.

The fiscal year is from Aug 1 to Jul 31.

I'm using the following to choose years:

In the query design in the Field:
Year(DateAdd("m",+5,[Day]))
in the Criteria: Format$(Date(),"yyyy")-4 for the last 4th
year and in other queries -3, -2, -1.

To get a month I'm using two columns:
1st. In the Field: Format([Day],"yyyy") and in Criteria:
Format$(Date(),"yyyy") - to take the current year

2d. In the Field: Format([Day],"mm") and in the Criteria:
Format$(DateAdd("m",-4,Date()),"mm") to take the last 4th
month (the rest of months -3, -2, -1).

Everything is working except the queries is very slow.
Using straightforward >=#8/1/2002# And <=#7/31/2003#
makes it much more faster but I'd like to have it flexible.

Could anybody advise anything?

Thanks
 
For a month I'm using >=DateSerial(Year(Date()),Month(Date
())-1,1) And <DateSerial(Year(Date()),Month(Date()),1)
It's working good now.

What would you advise to use for the fiscal years?

Thanks
 
The difference in speed is because the SQL doesn't have to evaluate every field,
but can use the index. Try changing the criteria.

For records for last 4 months:
Field: YourDateField
Criteria: Between DateSerial(Year(Date()), Month(Date())-4, 1) AND
DateSerial(Year(Date()), Month(Date()),0)

For Today's date of June 23, 2004 that should give you records from Feb 1, 2004
to May 31, 2004

For Fiscal years

Field: YourDateField
Criteria: Between DateSerial(Year(Date())-4,8,1) AND
DateSerial(Year(Date())-3,7,31)

So that would get 2004 minus 4 = 2000 and minus 3 = 2001 which gets all records between
8/1/2000 and 7/31/2001.

You can even use parameters in place of the subtraction number.
Between DateSerial(Year(Date())-[Years Back],8,1) AND
DateSerial(Year(Date())-[Years Back]+1,7,31)

When prompted for Years Back enter 4 or 3 or 2 or 1 or even zero and you should
get a particular year's worth of data.
 
Thanks a lot, John.

Alex
-----Original Message-----
The difference in speed is because the SQL doesn't have to evaluate every field,
but can use the index. Try changing the criteria.

For records for last 4 months:
Field: YourDateField
Criteria: Between DateSerial(Year(Date()), Month(Date())- 4, 1) AND
DateSerial(Year(Date()), Month(Date()),0)

For Today's date of June 23, 2004 that should give you records from Feb 1, 2004
to May 31, 2004

For Fiscal years

Field: YourDateField
Criteria: Between DateSerial(Year(Date())-4,8,1) AND
DateSerial(Year(Date())-3,7,31)

So that would get 2004 minus 4 = 2000 and minus 3 = 2001 which gets all records between
8/1/2000 and 7/31/2001.

You can even use parameters in place of the subtraction number.
Between DateSerial(Year(Date())-[Years Back],8,1) AND
DateSerial(Year(Date())-[Years Back]+1,7,31)

When prompted for Years Back enter 4 or 3 or 2 or 1 or even zero and you should
get a particular year's worth of data.
Alex said:
I need to show records according to fiscal year the 4 last
years and for the 4 last months.

The fiscal year is from Aug 1 to Jul 31.

I'm using the following to choose years:

In the query design in the Field:
Year(DateAdd("m",+5,[Day]))
in the Criteria: Format$(Date(),"yyyy")-4 for the last 4th
year and in other queries -3, -2, -1.

To get a month I'm using two columns:
1st. In the Field: Format([Day],"yyyy") and in Criteria:
Format$(Date(),"yyyy") - to take the current year

2d. In the Field: Format([Day],"mm") and in the Criteria:
Format$(DateAdd("m",-4,Date()),"mm") to take the last 4th
month (the rest of months -3, -2, -1).

Everything is working except the queries is very slow.
Using straightforward >=#8/1/2002# And <=#7/31/2003#
makes it much more faster but I'd like to have it flexible.

Could anybody advise anything?

Thanks
Click to expand...
.
Click to expand...
 
Back
Top