Trying to get the last 12mths sum of sales

R

Roger

I am trying to get via crosstab query (any query will do)
the sum of sales per month for each product, below is the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may to
june 2003 etc....

I think I am going wrong with creating the date field as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()-12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again
 
J

John Viescas

Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
M

MGFoster

Roger said:
I am trying to get via crosstab query (any query will do)
the sum of sales per month for each product, below is the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may to
june 2003 etc....

I think I am going wrong with creating the date field as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()-12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Date()-12 is 12 days prior to current date. You can use the DateAdd()
and DateSerial() functions to accomplish what you want:

WHERE InvoiceHdr.[Date] Between
DateSerial(Year(DateAdd("m", -12, Date())),
Month(DateAdd("m", -12, Date())),
1)
And DateSerial(Year(Date()), Month(Date())+1, 1) - 1

The DateSerial() functions return the 1st of the month, 12 months
before current date, and last day of current month.

====

Note:

Do not use Date as a column name 'cuz Date() is a built-in function &
the two can become confused.

The correct separation punctuation mark between table names and column
names is a period (.) not an exclamation point (!).

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQFZAHIechKqOuFEgEQL/DACg/bXsHxRU13j+8hbvnobg4iXPXYkAoNzD
C1mmF1FNsrzEwjRG4wc5TjMq
=NetQ
-----END PGP SIGNATURE-----
--
 
G

Guest

I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.

-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month (Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I am trying to get via crosstab query (any query will do)
the sum of sales per month for each product, below is the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may to
june 2003 etc....

I think I am going wrong with creating the date field as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()- 12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again
Click to expand...


.
Click to expand...
 
J

John Viescas

Tell me what you see when you run this query:

SELECT Format(InvoiceHdr.[Date], "yyyy mmm") As InvMonth,
InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour, Sum(InvoiceDet.Quantity) As
SumOfQuantity
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE InvoiceHdr.[Date] >= #01 APR 2003#
AND InvoiceHdr.[Date] < #15 MAR 2004#
GROUP BY Format(InvoiceHdr.[Date], "yyyy mmm"), InvoiceHdr.Customer_Name,
InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour;

Do you see rows for all the months?

To explain my two WHERE clauses:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to one year prior to
tomorrow (16 MAR 2003) and less than tomorrow.

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to the first day in the
next month of the prior year and less than tomorrow.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.

-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month (Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I am trying to get via crosstab query (any query will do)
the sum of sales per month for each product, below is the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may to
june 2003 etc....

I think I am going wrong with creating the date field as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()- 12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again
Click to expand...


.
Click to expand...
Click to expand...
 
G

Guest

Thanks john for your help,
With a little more research i have been looking at the
date manipulation.
Your code does it great, I have made a couple of changes
(see below), cross tab with rolling dates. If you see
any problems with the changes please yell.....
BUT I HAVE 1 MORE QUESTION, please
How do I get it to sort by month order not by
alpha/numerical,

Thankyou very much

Roger

TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Section = Stock.Section) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Colour = Stock.Colour)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((InvoiceHdr.Date) Between Date() And DateAdd("m",-
12,Date())))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
ORDER BY Format(InvoiceHdr.[Date],"yyyy mmm") DESC
PIVOT Format(InvoiceHdr.[Date],"yyyy mmm");
-----Original Message-----
Tell me what you see when you run this query:

SELECT Format(InvoiceHdr.[Date], "yyyy mmm") As InvMonth,
InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour, Sum (InvoiceDet.Quantity) As
SumOfQuantity
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE InvoiceHdr.[Date] >= #01 APR 2003#
AND InvoiceHdr.[Date] < #15 MAR 2004#
GROUP BY Format(InvoiceHdr.[Date], "yyyy mmm"), InvoiceHdr.Customer_Name,
InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour;

Do you see rows for all the months?

To explain my two WHERE clauses:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to one year prior to
tomorrow (16 MAR 2003) and less than tomorrow.

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to the first day in the
next month of the prior year and less than tomorrow.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.

-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()),
Click to expand...
Month
(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in
Click to expand...
case
your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I am trying to get via crosstab query (any query
Click to expand...
will
do)
the sum of sales per month for each product, below
Click to expand...
is
the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after
Click to expand...
may
to
june 2003 etc....

I think I am going wrong with creating the date
Click to expand...
field
as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()- 12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again


.
Click to expand...
Click to expand...


.
Click to expand...
 
J

John Spencer (MVP)

John,
You're getting trapped by one of my favorite errors - "yyyy" not "y" in the
DateAdd clause.

John said:
Tell me what you see when you run this query:

SELECT Format(InvoiceHdr.[Date], "yyyy mmm") As InvMonth,
InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour, Sum(InvoiceDet.Quantity) As
SumOfQuantity
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE InvoiceHdr.[Date] >= #01 APR 2003#
AND InvoiceHdr.[Date] < #15 MAR 2004#
GROUP BY Format(InvoiceHdr.[Date], "yyyy mmm"), InvoiceHdr.Customer_Name,
InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour;

Do you see rows for all the months?

To explain my two WHERE clauses:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to one year prior to
tomorrow (16 MAR 2003) and less than tomorrow.

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to the first day in the
next month of the prior year and less than tomorrow.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.

-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month (Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I am trying to get via crosstab query (any query will do)
the sum of sales per month for each product, below is the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may to
june 2003 etc....

I think I am going wrong with creating the date field as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()- 12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again


.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

Thanks for that - that code works now, but do you know
how to get it to sort (being a crosstab table) from this
month backwards, eg: march 2004, feb 2004, jan 2004 .....
april 2003

Thanks for your assistance
-----Original Message-----
John,
You're getting trapped by one of my favorite errors - "yyyy" not "y" in the
DateAdd clause.

John said:
Tell me what you see when you run this query:

SELECT Format(InvoiceHdr.[Date], "yyyy mmm") As InvMonth,
InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour, Sum (InvoiceDet.Quantity) As
SumOfQuantity
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE InvoiceHdr.[Date] >= #01 APR 2003#
AND InvoiceHdr.[Date] < #15 MAR 2004#
GROUP BY Format(InvoiceHdr.[Date], "yyyy mmm"), InvoiceHdr.Customer_Name,
InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour;

Do you see rows for all the months?

To explain my two WHERE clauses:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to one year prior to
tomorrow (16 MAR 2003) and less than tomorrow.

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to the first day in the
next month of the prior year and less than tomorrow.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.


-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until
today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month
(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case
your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
I am trying to get via crosstab query (any query will
do)
the sum of sales per month for each product, below is
the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12
months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may
to
june 2003 etc....

I think I am going wrong with creating the date field
as
I have done, but I need these for column headings in
the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date ()-
12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again


.
Click to expand...
Click to expand...
.
Click to expand...
 
J

John Viescas

Ah. The Pivot asks for Format(InvoiceHdr.[Date], "yyyy mmm") - which yields
values like "2003 Apr", "2003 May", 2003 Jun" ... etc. As you have
discovered, it sorts the month names in alpha sequence. One solution is to
use the IN clause (Column Headings in Query/Properties) to force the sort
sequence, but then that will make your query not "dynamic" any more. If you
can live with month numbers instead of month abbreviations, use

Format(InvoiceHdr.[Date], "yyyy mm")

That will return the 2-digit month number, and that will sort correctly.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks john for your help,
With a little more research i have been looking at the
date manipulation.
Your code does it great, I have made a couple of changes
(see below), cross tab with rolling dates. If you see
any problems with the changes please yell.....
BUT I HAVE 1 MORE QUESTION, please
How do I get it to sort by month order not by
alpha/numerical,

Thankyou very much

Roger

TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Section = Stock.Section) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Colour = Stock.Colour)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((InvoiceHdr.Date) Between Date() And DateAdd("m",-
12,Date())))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
ORDER BY Format(InvoiceHdr.[Date],"yyyy mmm") DESC
PIVOT Format(InvoiceHdr.[Date],"yyyy mmm");
-----Original Message-----
Tell me what you see when you run this query:

SELECT Format(InvoiceHdr.[Date], "yyyy mmm") As InvMonth,
InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour, Sum (InvoiceDet.Quantity) As
SumOfQuantity
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE InvoiceHdr.[Date] >= #01 APR 2003#
AND InvoiceHdr.[Date] < #15 MAR 2004#
GROUP BY Format(InvoiceHdr.[Date], "yyyy mmm"), InvoiceHdr.Customer_Name,
InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour;

Do you see rows for all the months?

To explain my two WHERE clauses:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to one year prior to
tomorrow (16 MAR 2003) and less than tomorrow.

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal to the first day in the
next month of the prior year and less than tomorrow.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.


-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until
today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month
(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case
your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
I am trying to get via crosstab query (any query will
do)
the sum of sales per month for each product, below is
the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12
months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may
to
june 2003 etc....

I think I am going wrong with creating the date field
as
I have done, but I need these for column headings in
the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()-
12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again


.
Click to expand...


.
Click to expand...
Click to expand...
 
D

Duane Hookom

TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()-12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT "Mth" & DateDiff("m",[InvoiceHdr]![Date]),Date());

Set the Column Headings property to:
Mth0,Mth1,...Mth11
Mth0 is the current month and Mth11 is 11 months previous. You can also get
rid of the WHERE condition since the column headings replace it.
 
R

Roger

** Thankyou very much- I believe this will have to do **
Rgds

Roger
-----Original Message-----
Ah. The Pivot asks for Format(InvoiceHdr.[Date], "yyyy mmm") - which yields
values like "2003 Apr", "2003 May", 2003 Jun" ... etc. As you have
discovered, it sorts the month names in alpha sequence. One solution is to
use the IN clause (Column Headings in Query/Properties) to force the sort
sequence, but then that will make your query
Click to expand...
not "dynamic" any more. If you
can live with month numbers instead of month abbreviations, use

Format(InvoiceHdr.[Date], "yyyy mm")

That will return the 2-digit month number, and that will sort correctly.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Thanks john for your help,
With a little more research i have been looking at the
date manipulation.
Your code does it great, I have made a couple of changes
(see below), cross tab with rolling dates. If you see
any problems with the changes please yell.....
BUT I HAVE 1 MORE QUESTION, please
How do I get it to sort by month order not by
alpha/numerical,

Thankyou very much

Roger

TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Section = Stock.Section) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Colour = Stock.Colour)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((InvoiceHdr.Date) Between Date() And DateAdd ("m",-
12,Date())))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
ORDER BY Format(InvoiceHdr.[Date],"yyyy mmm") DESC
PIVOT Format(InvoiceHdr.[Date],"yyyy mmm");
-----Original Message-----
Tell me what you see when you run this query:

SELECT Format(InvoiceHdr.[Date], "yyyy mmm") As InvMonth,
InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour, Sum (InvoiceDet.Quantity) As
SumOfQuantity
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE InvoiceHdr.[Date] >= #01 APR 2003#
AND InvoiceHdr.[Date] < #15 MAR 2004#
GROUP BY Format(InvoiceHdr.[Date], "yyyy mmm"), InvoiceHdr.Customer_Name,
InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour;

Do you see rows for all the months?

To explain my two WHERE clauses:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date() + 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal
Click to expand...
to
one year prior to
tomorrow (16 MAR 2003) and less than tomorrow.

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()) -1, Month(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

In English: Where the date is greater than or equal
Click to expand...
to
the first day in the
next month of the prior year and less than tomorrow.


--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
I put this in and only get 1 column for "march 2004" not
the previous 11mths. Why use "y" in script.

May I impose for an explanation, DateAdd, how and where
would this be used?

Sorry to be dumb on this issue and thanks for your help.


-----Original Message-----
Try substituting one of these for your WHERE clause:

To do a "rolling" year from tomorrow a year ago until
today:

WHERE InvoiceHdr.[Date] >= DateAdd("y", -1, Date()
Click to expand...
+
1)
AND InvoiceHdr.[Date] < (Date() + 1)

To do from April 1, 2003 until today:

WHERE InvoiceHdr.[Date] >= DateSerial(Year(Date()), Month
(Date()) + 1, 1)
AND InvoiceHdr.[Date] < (Date() + 1)

By the way, I'm comparing to < (Date() + 1) just in case
your date/time
field has a time in it.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
message
I am trying to get via crosstab query (any query will
do)
the sum of sales per month for each product,
Click to expand...
below
is
the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12
months
to be selected on a rolling basis eg: next month
Click to expand...
i
want
it to show April 2004 - May 2003, the month after may
to
june 2003 etc....

I think I am going wrong with creating the date field
as
I have done, but I need these for column headings in
the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date ()-
12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())- 1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
G

Guest

Thanks for the help
Much appreciated
-----Original Message-----
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()-12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT "Mth" & DateDiff("m",[InvoiceHdr]![Date]),Date());

Set the Column Headings property to:
Mth0,Mth1,...Mth11
Mth0 is the current month and Mth11 is 11 months previous. You can also get
rid of the WHERE condition since the column headings replace it.

--
Duane Hookom
MS Access MVP


I am trying to get via crosstab query (any query will do)
the sum of sales per month for each product, below is the
sql for the current query.

I can not find a criteria that will give me the last 12
months march 2004 - april 2003. I want the last 12 months
to be selected on a rolling basis eg: next month i want
it to show April 2004 - May 2003, the month after may to
june 2003 etc....

I think I am going wrong with creating the date field as
I have done, but I need these for column headings in the
cross tab.

Please help, thanks for any assistance:
Sql as follows
TRANSFORM Sum(InvoiceDet.Quantity) AS SumOfQuantity
SELECT InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
FROM InvoiceHdr INNER JOIN (InvoiceDet INNER JOIN Stock
ON (InvoiceDet.Colour = Stock.Colour) AND
(InvoiceDet.PartNumber = Stock.PartNumber) AND
(InvoiceDet.Section = Stock.Section)) ON
InvoiceHdr.Invoice_Number = InvoiceDet.Invoice_Number
WHERE (((Month([InvoiceHdr]![Date]))<=(Month(Date()- 12)))
AND ((Year([InvoiceHdr]![Date]))>=Year(Date())-1))
GROUP BY InvoiceHdr.Customer_Name, InvoiceDet.Section,
InvoiceDet.PartNumber, InvoiceDet.Colour
PIVOT (MonthName(Month([InvoiceHdr]![Date]),3) & " " &
Year([InvoiceHdr]![Date]));
- - - - - - - - - - - - - - - -

Thanks again
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

Counts of dates and sum of sales by month 2
Getting only the last transaction for each customer 1
Multiple columns of data on the same report based on different dateparameters 9
Multiple columns of data on the same report based on different dateparameters 1
Using Crosstab query Wizard to sum the quantities in a month. 2
Help -- Is my there any way to not count duplicate values here? 5
Need date to appear when sum is zero 3
Trying to select the last entered mileage for a vin number 1

Top