Newbie question: Queries using quarters

[martin_watts]

I have a table containing a start date and an end date for training courses.
I need to extract quarterly statistical information. I have added two
columns to a query where I have used the datepart function to calculate the
quarter and year in which the start and end dates fall. So I have something
like this. (NB dates in dd/mm/yy format)

Start End Startqtr Endqtr
27/01/04 27/04/04 12004 22004
30/01/04 15/07/04 12004 32004
10/07/04 01/10/04 32004 42004

I want the user to be able to enter (as in a parameter query) the quarter
and year so the query will return records where the start or end date is in
the selected quarter and where the course runs through that quarter i.e. in
the above table entering 22004 would select the first and second records in
the above table, whereas entering 32004 would select the second and third
records. I am at a loss and have been unable to find a solution. Any ideas
where to look or can anyone suggest a solution? TIA and apologies from a
newbie if the solution is blindingly obvious.

 

[Tonín]

I simply would suggest to change your way to represent quarters. I think you
should put year first:

12004 --> 20041

That way will allow you to use ">=" and "<=" operators in your parameter
query and it will run, because quarters will be properly sorted (assuming
they are integer numbers). If don't, post your query, and I'll try to
suggest better. :-? :-D


Tonín

 

[Chris Nebinger]

Let's assume that you are on standard quarters, and not
fiscal quarters like the federal government. If your
quarters are off, the equation will have to be modified.

Start End
27/01/04 27/04/04
30/01/04 15/07/04
10/07/04 01/10/04

Select * from TrainingCourses
WHERE
[Start] <= DateSerial([Enter Year],([Enter Quarter]-1)
*3+1,1) AND [End] >= DateSerial([Enter Year],([Enter
Quarter]-1)*3+1,1)


Chris Nebinger

 

[martin_watts]

Cheers for that. Works brilliantly, but .... is there a way of writing the
query so the user only has to enter the year and quarter once instead of
twice. I just want to remove as much opportunity for user input (i.e.
possible error) as possible. If not then my 'customer' will have to go with
it as it is. TIA

Martin Watts

Let's assume that you are on standard quarters, and not
fiscal quarters like the federal government. If your
quarters are off, the equation will have to be modified.

Start End
27/01/04 27/04/04
30/01/04 15/07/04
10/07/04 01/10/04

Select * from TrainingCourses
WHERE
[Start] <= DateSerial([Enter Year],([Enter Quarter]-1)
*3+1,1) AND [End] >= DateSerial([Enter Year],([Enter
Quarter]-1)*3+1,1)


Chris Nebinger

-----Original Message-----
I have a table containing a start date and an end date for training courses.
I need to extract quarterly statistical information. I have added two
columns to a query where I have used the datepart function to calculate the
quarter and year in which the start and end dates fall. So I have something
like this. (NB dates in dd/mm/yy format)

Start End Startqtr Endqtr
27/01/04 27/04/04 12004 22004
30/01/04 15/07/04 12004 32004
10/07/04 01/10/04 32004 42004

I want the user to be able to enter (as in a parameter query) the quarter
and year so the query will return records where the start or end date is in
the selected quarter and where the course runs through that quarter i.e. in
the above table entering 22004 would select the first and second records in
the above table, whereas entering 32004 would select the second and third
records. I am at a loss and have been unable to find a solution. Any ideas
where to look or can anyone suggest a solution? TIA and apologies from a
newbie if the solution is blindingly obvious.


.

 

[martin_watts]

Thanks for that Tonin. Chris Nebinger's later post below, showed how to use
these operators. If you have a look at my reply to his post, I wonder if
you would have any ideas? TIA.

Martin Watts

 

[Tonín]

I read Chris Nebinger's post and I totally agreed with the query he proposed
(then I felt guilty 'cause my answer was kind of a little lazy, I should
work a little more [not sure about my English - I'm Spanish, but I think you
understand me ].

On your current question, Chris's SQL, as posted, will ask you once for
"Enter Year" and once for "Enter Quarter". If query ask twice for the Year
or the Quarter, it means that you mistyped [Enter Year] and/or [Enter
Quarter]. They both are parameters for the query, and "must" be written
exactly the same where they appear, and each one appear twice (mind the
spaces!), I mean: [Enter Year] is not equal to [Enter Year ]

If question was not that, then I would need a little more explanation about
the "twice" thing.


Hope being helpful

Tonín

 

[martin_watts]

I copied and pasted Chris Nebinger's sql into a new query. When I change to
design view it shows the 'Enter Year' and 'Enter Quarter' parameters in the
criteria field of both the 'start' and 'end' columns, hence when the query
is run the user is prompted to 'Enter Year' and 'Enter Quarter' twice, once
for the 'start' column and once for the 'end' column. Ideally I would like
the user to only enter the year and quarter once and it would then pull out
all records that have a start date and end date in the chosen quarter and
also those records where the start date is before the start of the quarter
and the end date is after the end date of the quarter i.e. the training
course runs throughout the chosen quarter. Hope my newbie explanation makes
sense.

BTW, don't feel guilty. Any pointers from those 'in the know' is greatly
appreciated. Thanks.

Martin Watts

I read Chris Nebinger's post and I totally agreed with the query he proposed
(then I felt guilty 'cause my answer was kind of a little lazy, I should
work a little more [not sure about my English - I'm Spanish, but I think you
understand me ].

On your current question, Chris's SQL, as posted, will ask you once for
"Enter Year" and once for "Enter Quarter". If query ask twice for the Year
or the Quarter, it means that you mistyped [Enter Year] and/or [Enter
Quarter]. They both are parameters for the query, and "must" be written
exactly the same where they appear, and each one appear twice (mind the
spaces!), I mean: [Enter Year] is not equal to [Enter Year ]

If question was not that, then I would need a little more explanation about
the "twice" thing.


Hope being helpful

Tonín

Thanks for that Tonin. Chris Nebinger's later post below, showed how to use
these operators. If you have a look at my reply to his post, I wonder if
you would have any ideas? TIA.

Martin Watts

think
you

calculat

e

date

is from

 

[Chris Nebinger]

If the names of the parameters are spelled the same, you
shouldn't get 2 prompts for each. Check to make sure they
are exact.

You could specify what paramaters you have, Query-

Parameters.

Or, the method I would use, use a form to capture the
parameters. Then you could do data validation, check for
no records, etc.

Create a new form. Name it frmSelectDates. Drop 2
unbound text boxes, named txtQuarter and txtYear. Add a
command button and on the OnClick Even:
DoCmd.OpenQuery "Your Query Name")

Now, change the parameters in your query to:

Forms!frmSelectDates!txtQuarter and
Forms!frmSelectDate!txtYear

Note: Now you can have combo boxes for each, limiting the
data the user can put in. No more entering quarter 5, for
instance.


Chris Nebinger

-----Original Message-----
I copied and pasted Chris Nebinger's sql into a new query. When I change to
design view it shows the 'Enter Year' and 'Enter Quarter' parameters in the
criteria field of both the 'start' and 'end' columns, hence when the query
is run the user is prompted to 'Enter Year' and 'Enter Quarter' twice, once
for the 'start' column and once for the 'end' column. Ideally I would like
the user to only enter the year and quarter once and it would then pull out
all records that have a start date and end date in the chosen quarter and
also those records where the start date is before the start of the quarter
and the end date is after the end date of the quarter i.e. the training
course runs throughout the chosen quarter. Hope my newbie explanation makes
sense.

BTW, don't feel guilty. Any pointers from those 'in the know' is greatly
appreciated. Thanks.

Martin Watts

I read Chris Nebinger's post and I totally agreed with

the query he
proposed

(then I felt guilty 'cause my answer was kind of a little lazy, I should
work a little more [not sure about my English - I'm

Spanish, but I think
you

understand me ].

On your current question, Chris's SQL, as posted, will ask you once for
"Enter Year" and once for "Enter Quarter". If query ask twice for the Year
or the Quarter, it means that you mistyped [Enter Year] and/or [Enter
Quarter]. They both are parameters for the query, and "must" be written
exactly the same where they appear, and each one appear twice (mind the
spaces!), I mean: [Enter Year] is not equal to [Enter Year ]

If question was not that, then I would need a little

more explanation
about

the "twice" thing.


Hope being helpful

Tonín


"martin_watts" <[email protected]> escribió en el mensaje
below, showed how to
use

his post, I wonder
if represent quarters. I
think operators in your
parameter

properly sorted
(assuming

information. I have added
two function to
calculat

e

the start or end
date

is through that quarter
i.e. first and second
records

find a solution.
Any

TIA and apologies
from

.

 

[Tonín]

I did the copy and paste process and I found that query ask for too much
parameters, as you told us. In my case, reason was there existed a carriage
return in the name of the second [Enter Quarter] parameter (that one under
the "End" Field). Then it was pasted as:

[Enter
Quarter]

not as

[Enter Quarter]

If you fix/delete this undesirable carriage returns appeared because of the
mail format, query runs perfect.



Tonín

I copied and pasted Chris Nebinger's sql into a new query. When I change to
design view it shows the 'Enter Year' and 'Enter Quarter' parameters in the
criteria field of both the 'start' and 'end' columns, hence when the query
is run the user is prompted to 'Enter Year' and 'Enter Quarter' twice, once
for the 'start' column and once for the 'end' column. Ideally I would like
the user to only enter the year and quarter once and it would then pull out
all records that have a start date and end date in the chosen quarter and
also those records where the start date is before the start of the quarter
and the end date is after the end date of the quarter i.e. the training
course runs throughout the chosen quarter. Hope my newbie explanation makes
sense.

BTW, don't feel guilty. Any pointers from those 'in the know' is greatly
appreciated. Thanks.

Martin Watts

I read Chris Nebinger's post and I totally agreed with the query he proposed
(then I felt guilty 'cause my answer was kind of a little lazy, I should
work a little more [not sure about my English - I'm Spanish, but I think you
understand me ].

On your current question, Chris's SQL, as posted, will ask you once for
"Enter Year" and once for "Enter Quarter". If query ask twice for the Year
or the Quarter, it means that you mistyped [Enter Year] and/or [Enter
Quarter]. They both are parameters for the query, and "must" be written
exactly the same where they appear, and each one appear twice (mind the
spaces!), I mean: [Enter Year] is not equal to [Enter Year ]

If question was not that, then I would need a little more explanation about
the "twice" thing.


Hope being helpful

Tonín

Thanks for that Tonin. Chris Nebinger's later post below, showed how

to
use

these operators. If you have a look at my reply to his post, I wonder if
you would have any ideas? TIA.

Martin Watts

I simply would suggest to change your way to represent quarters. I think
you
should put year first:

12004 --> 20041

That way will allow you to use ">=" and "<=" operators in your parameter
query and it will run, because quarters will be properly sorted (assuming
they are integer numbers). If don't, post your query, and I'll try to
suggest better. :-? :-D


Tonín


"martin_watts" <[email protected]> escribió en el mensaje
I have a table containing a start date and an end date for training
courses.
I need to extract quarterly statistical information. I have added two
columns to a query where I have used the datepart function to

calculat

e
the
quarter and year in which the start and end dates fall. So I have
something
like this. (NB dates in dd/mm/yy format)

Start End Startqtr Endqtr
27/01/04 27/04/04 12004 22004
30/01/04 15/07/04 12004 32004
10/07/04 01/10/04 32004 42004

I want the user to be able to enter (as in a parameter query) the
quarter
and year so the query will return records where the start or end

date

is
in
the selected quarter and where the course runs through that

quarter
i.e.

in
the above table entering 22004 would select the first and second records
in
the above table, whereas entering 32004 would select the second and
third
records. I am at a loss and have been unable to find a solution. Any
ideas
where to look or can anyone suggest a solution? TIA and apologies from
a
newbie if the solution is blindingly obvious.

 

[martin_watts]

Thanks for the help Chris and Tonin. Works like a dream now, workmates will
be very impresssed with my apparent Access prowess!

Martin

I did the copy and paste process and I found that query ask for too much
parameters, as you told us. In my case, reason was there existed a carriage
return in the name of the second [Enter Quarter] parameter (that one under
the "End" Field). Then it was pasted as:

[Enter
Quarter]

not as

[Enter Quarter]

If you fix/delete this undesirable carriage returns appeared because of the
mail format, query runs perfect.



Tonín

I copied and pasted Chris Nebinger's sql into a new query. When I

change

to
design view it shows the 'Enter Year' and 'Enter Quarter' parameters in the
criteria field of both the 'start' and 'end' columns, hence when the query
is run the user is prompted to 'Enter Year' and 'Enter Quarter' twice, once
for the 'start' column and once for the 'end' column. Ideally I would like
the user to only enter the year and quarter once and it would then pull out
all records that have a start date and end date in the chosen quarter and
also those records where the start date is before the start of the quarter
and the end date is after the end date of the quarter i.e. the training
course runs throughout the chosen quarter. Hope my newbie explanation makes
sense.

BTW, don't feel guilty. Any pointers from those 'in the know' is greatly
appreciated. Thanks.

Martin Watts

I read Chris Nebinger's post and I totally agreed with the query he proposed
(then I felt guilty 'cause my answer was kind of a little lazy, I should
work a little more [not sure about my English - I'm Spanish, but I

think
you

understand me ].

On your current question, Chris's SQL, as posted, will ask you once for
"Enter Year" and once for "Enter Quarter". If query ask twice for the Year
or the Quarter, it means that you mistyped [Enter Year] and/or [Enter
Quarter]. They both are parameters for the query, and "must" be written
exactly the same where they appear, and each one appear twice (mind the
spaces!), I mean: [Enter Year] is not equal to [Enter Year ]

If question was not that, then I would need a little more explanation about
the "twice" thing.


Hope being helpful

Tonín


"martin_watts" <[email protected]> escribió en el mensaje
Thanks for that Tonin. Chris Nebinger's later post below, showed

how

to wonder
if added
two solution.
Any