query Qtrly info for non-traditional quater period

T

Toni

I would like to print a summary of quarterly information and am having
problems with the select statement

The table has a RebateAmount and RebateDate which is defined as a date/time
field.

First quarter is November - January and the 4th quarter is Aug - Oct.

Example data

01-Nov-07 500
01-Dec-07 200
01-Feb-07 350
01-Feb-08 450
01-Mar-08 100
01-Apr-08 200
01-May-08 600
 
J

John Spencer

Adjust the date by adding months or subtracting months and then use
format to reutrn the desired string.

Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")

SELECT Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q") as Quarter
, Sum(Amount) as TotalAmount
FROM SomeTable
GROUP BY Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
 
T

Toni

Thanks for the quick response. This is real close, the problem I am having
is the years are off. For example my year 2000 quarter 2 data shows year
1999 with the correct amount. Their are also a few othter total amounts that
I am not sure where they are coming from, I am guessing because the year is
off. I did try modifying the example with + 10... Please let me know if you
need additional information.
Below is my select statement

SELECT Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount, RebateDetails.StoreNumber,
RebateDetails.RebateTypeID
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q"),
RebateDetails.StoreNumber, RebateDetails.RebateTypeID
HAVING (((RebateDetails.StoreNumber)=33) AND ((RebateDetails.RebateTypeID)=1
Or (RebateDetails.RebateTypeID)=2 Or (RebateDetails.RebateTypeID)=3));
 
J

John Spencer

Is RebateDate a date field? If it is a text field containing a date
string, then what is the format of the string -
dd mm yy, yy mm dd, mm dd yy or something else?

This works for me with a dateTime field. Try just running a query with
the expression and the RebateDate and compare what you get with what you
expect.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the quick response. This is real close, the problem I am having
is the years are off. For example my year 2000 quarter 2 data shows year
1999 with the correct amount. Their are also a few othter total amounts that
I am not sure where they are coming from, I am guessing because the year is
off. I did try modifying the example with + 10... Please let me know if you
need additional information.
Below is my select statement

SELECT Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount, RebateDetails.StoreNumber,
RebateDetails.RebateTypeID
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q"),
RebateDetails.StoreNumber, RebateDetails.RebateTypeID
HAVING (((RebateDetails.StoreNumber)=33) AND ((RebateDetails.RebateTypeID)=1
Or (RebateDetails.RebateTypeID)=2 Or (RebateDetails.RebateTypeID)=3));




John Spencer said:
Adjust the date by adding months or subtracting months and then use
format to reutrn the desired string.

Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")

SELECT Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q") as Quarter
, Sum(Amount) as TotalAmount
FROM SomeTable
GROUP BY Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================
Click to expand...
Click to expand...
 
T

Toni

RebateDate is defined as a date/time field. The format is set to General Date
When I view the date field in the table it looks like 11/1/2001.
I have also tried the below query with the date format in the table not set
to anything, I still get the same results

When I do a simple query

SELECT Format(DateAdd("m",-10,RebateDate),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,RebateDate),"yyyy \Quarter q");

Some of the results has a Quarter of '1999 Quarter 1'. The table has a
total of
36 records and none of them have a year 1999. The earliest date is 1/1/2000.

Tsharp


John Spencer said:
Is RebateDate a date field? If it is a text field containing a date
string, then what is the format of the string -
dd mm yy, yy mm dd, mm dd yy or something else?

This works for me with a dateTime field. Try just running a query with
the expression and the RebateDate and compare what you get with what you
expect.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the quick response. This is real close, the problem I am having
is the years are off. For example my year 2000 quarter 2 data shows year
1999 with the correct amount. Their are also a few othter total amounts that
I am not sure where they are coming from, I am guessing because the year is
off. I did try modifying the example with + 10... Please let me know if you
need additional information.
Below is my select statement

SELECT Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount, RebateDetails.StoreNumber,
RebateDetails.RebateTypeID
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q"),
RebateDetails.StoreNumber, RebateDetails.RebateTypeID
HAVING (((RebateDetails.StoreNumber)=33) AND ((RebateDetails.RebateTypeID)=1
Or (RebateDetails.RebateTypeID)=2 Or (RebateDetails.RebateTypeID)=3));




John Spencer said:
Adjust the date by adding months or subtracting months and then use
format to reutrn the desired string.

Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")

SELECT Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q") as Quarter
, Sum(Amount) as TotalAmount
FROM SomeTable
GROUP BY Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Toni wrote:
I would like to print a summary of quarterly information and am having
problems with the select statement

The table has a RebateAmount and RebateDate which is defined as a date/time
field.

First quarter is November - January and the 4th quarter is Aug - Oct.

Example data

01-Nov-07 500
01-Dec-07 200
01-Feb-07 350
01-Feb-08 450
01-Mar-08 100
01-Apr-08 200
01-May-08 600
.
.

the result I would like is

2007 Quarter 1 = 700
2007 Quarter 2 = 350
2008 Quarter 2 = 750
2008 Quarter 3 = 600

Any suggestions would be greatly appreciated

Thanks
Tsharp
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Well unless I misunderstood what you want
1/1/2000 would be in Fiscal Year 1999.

Perhaps I got things backward. IF 1/1/2000 is supposed to be in the
first quarter of 2000 try changing the expression to ADD 2 months
instead of subtracting 10.

That is why I told you to run a query with Just the date and the
expression. Looking at it you should have been able to figure out what
is wrong.

Your example translated Nov 07 and Dec 07 into 2007 Q 1 which lead me
astray.Hopefully the revised expression will work for you.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

RebateDate is defined as a date/time field. The format is set to General Date
When I view the date field in the table it looks like 11/1/2001.
I have also tried the below query with the date format in the table not set
to anything, I still get the same results

When I do a simple query

SELECT Format(DateAdd("m",-10,RebateDate),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,RebateDate),"yyyy \Quarter q");

Some of the results has a Quarter of '1999 Quarter 1'. The table has a
total of
36 records and none of them have a year 1999. The earliest date is 1/1/2000.

Tsharp


John Spencer said:
Is RebateDate a date field? If it is a text field containing a date
string, then what is the format of the string -
dd mm yy, yy mm dd, mm dd yy or something else?

This works for me with a dateTime field. Try just running a query with
the expression and the RebateDate and compare what you get with what you
expect.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

Thanks for the quick response. This is real close, the problem I am having
is the years are off. For example my year 2000 quarter 2 data shows year
1999 with the correct amount. Their are also a few othter total amounts that
I am not sure where they are coming from, I am guessing because the year is
off. I did try modifying the example with + 10... Please let me know if you
need additional information.
Below is my select statement

SELECT Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount, RebateDetails.StoreNumber,
RebateDetails.RebateTypeID
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q"),
RebateDetails.StoreNumber, RebateDetails.RebateTypeID
HAVING (((RebateDetails.StoreNumber)=33) AND ((RebateDetails.RebateTypeID)=1
Or (RebateDetails.RebateTypeID)=2 Or (RebateDetails.RebateTypeID)=3));




:

Adjust the date by adding months or subtracting months and then use
format to reutrn the desired string.

Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")

SELECT Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q") as Quarter
, Sum(Amount) as TotalAmount
FROM SomeTable
GROUP BY Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Toni wrote:
I would like to print a summary of quarterly information and am having
problems with the select statement

The table has a RebateAmount and RebateDate which is defined as a date/time
field.

First quarter is November - January and the 4th quarter is Aug - Oct.

Example data

01-Nov-07 500
01-Dec-07 200
01-Feb-07 350
01-Feb-08 450
01-Mar-08 100
01-Apr-08 200
01-May-08 600
.
.

the result I would like is

2007 Quarter 1 = 700
2007 Quarter 2 = 350
2008 Quarter 2 = 750
2008 Quarter 3 = 600

Any suggestions would be greatly appreciated

Thanks
Tsharp
Click to expand...
Click to expand...
Click to expand...
 
T

Toni

I actually did try ADD 2 months and my results were not correct. I then
looked at the select statement and found my mistake. I had a selection
criteria that should have been a Where clause not group by, as soon as I
modified that, I go the results I was looking for. Thank you for taking the
time to help.

Toni


John Spencer said:
Well unless I misunderstood what you want
1/1/2000 would be in Fiscal Year 1999.

Perhaps I got things backward. IF 1/1/2000 is supposed to be in the
first quarter of 2000 try changing the expression to ADD 2 months
instead of subtracting 10.

That is why I told you to run a query with Just the date and the
expression. Looking at it you should have been able to figure out what
is wrong.

Your example translated Nov 07 and Dec 07 into 2007 Q 1 which lead me
astray.Hopefully the revised expression will work for you.


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

RebateDate is defined as a date/time field. The format is set to General Date
When I view the date field in the table it looks like 11/1/2001.
I have also tried the below query with the date format in the table not set
to anything, I still get the same results

When I do a simple query

SELECT Format(DateAdd("m",-10,RebateDate),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,RebateDate),"yyyy \Quarter q");

Some of the results has a Quarter of '1999 Quarter 1'. The table has a
total of
36 records and none of them have a year 1999. The earliest date is 1/1/2000.

Tsharp


John Spencer said:
Is RebateDate a date field? If it is a text field containing a date
string, then what is the format of the string -
dd mm yy, yy mm dd, mm dd yy or something else?

This works for me with a dateTime field. Try just running a query with
the expression and the RebateDate and compare what you get with what you
expect.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Toni wrote:
Thanks for the quick response. This is real close, the problem I am having
is the years are off. For example my year 2000 quarter 2 data shows year
1999 with the correct amount. Their are also a few othter total amounts that
I am not sure where they are coming from, I am guessing because the year is
off. I did try modifying the example with + 10... Please let me know if you
need additional information.
Below is my select statement

SELECT Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q") AS Quarter,
Sum(RebateDetails.RebateAmount) AS totalAmount, RebateDetails.StoreNumber,
RebateDetails.RebateTypeID
FROM RebateDetails
GROUP BY Format(DateAdd("m",-10,[RebateDate]),"yyyy \Quarter q"),
RebateDetails.StoreNumber, RebateDetails.RebateTypeID
HAVING (((RebateDetails.StoreNumber)=33) AND ((RebateDetails.RebateTypeID)=1
Or (RebateDetails.RebateTypeID)=2 Or (RebateDetails.RebateTypeID)=3));




:

Adjust the date by adding months or subtracting months and then use
format to reutrn the desired string.

Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")

SELECT Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q") as Quarter
, Sum(Amount) as TotalAmount
FROM SomeTable
GROUP BY Format(DateAdd("m",-10,TheDate),"yyyy \Quarter q")


'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================


Toni wrote:
I would like to print a summary of quarterly information and am having
problems with the select statement

The table has a RebateAmount and RebateDate which is defined as a date/time
field.

First quarter is November - January and the 4th quarter is Aug - Oct.

Example data

01-Nov-07 500
01-Dec-07 200
01-Feb-07 350
01-Feb-08 450
01-Mar-08 100
01-Apr-08 200
01-May-08 600
.
.

the result I would like is

2007 Quarter 1 = 700
2007 Quarter 2 = 350
2008 Quarter 2 = 750
2008 Quarter 3 = 600

Any suggestions would be greatly appreciated

Thanks
Tsharp
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Crosstab Query based on Ordinal Values 6
project progress 1
When building a chart Dates are been included that are not in thedate range 1
Fiscal year 2
Combine 2 Charts 2007 Excel 1
Using the dateadd function within DSUM 2
Calculating Headcount by Month 9
Pivot table 3

Top