Date Ranges as Columns

M

michael c

Hi. I'm stuck...I have several hundred thousand records
that are in a table like so:

InvoiceDate CustomerNumber Product Units
1/12/03 0000056789 Acme Gloves 10

I'm trying to create a query and, ultimately, a report
that will group the orders so that I can compare how much
each customer ordered:

Current Year To Date
Current Quarter To Date
Current Month To Date
Prior Year To Date
Prior Quarter To Date
Prior Month To Date

The problem is getting my date filters in the same query.
Any thoughts? Thanks!!
 
D

Duane Hookom

You can create a totals query like:
SELECT CustomerNumber,
Abs(Sum( (Year(InvoiceDate) = Year(Date())) * [Units])) as CurYTD,
Abs(Sum( (Format(InvoiceDate,"yyyyq") = Format(Date(),"yyyyq")) * [Units]))
as CurQTD,
etc...
FROM tableA
GROUP BY CustomerNumber;

You need to add the expressions that evaluate to either true or false for
the other date periods.
 
M

michael c

Duane, That's awesome!! THANK YOU!!!!
-----Original Message-----
You can create a totals query like:
SELECT CustomerNumber,
Abs(Sum( (Year(InvoiceDate) = Year(Date())) * [Units])) as CurYTD,
Abs(Sum( (Format(InvoiceDate,"yyyyq") = Format(Date (),"yyyyq")) * [Units]))
as CurQTD,
etc...
FROM tableA
GROUP BY CustomerNumber;

You need to add the expressions that evaluate to either true or false for
the other date periods.
--
Duane Hookom
MS Access MVP


Hi. I'm stuck...I have several hundred thousand records
that are in a table like so:

InvoiceDate CustomerNumber Product Units
1/12/03 0000056789 Acme Gloves 10

I'm trying to create a query and, ultimately, a report
that will group the orders so that I can compare how much
each customer ordered:

Current Year To Date
Current Quarter To Date
Current Month To Date
Prior Year To Date
Prior Quarter To Date
Prior Month To Date

The problem is getting my date filters in the same query.
Any thoughts? Thanks!!
Click to expand...


.
Click to expand...
 
P

Peter Surcouf

Michael

I think Daune was incorrect in using abs() there may be some (depressing)
periods when goods were only credited thus one should use "-" instead.
Also if comparing the sales all in one query then one needs to add a WHERE
clause which will only select the dates of the same period for the previous
year. I enclose the whole query, using Daune's logic, below *which is
untested*

SELECT CustomerNumber, -Sum(((Year(InvoiceDate)=Year(Date()))*Units)) AS
CurYTD, -Sum(((Year(InvoiceDate)=Year(Date())-1)*Units)) AS
PrevYTD, -Sum((Format(InvoiceDate,"yyyyq")=Format(Date(),"yyyyq"))*Units) AS
CurQTD, -Sum((Format(InvoiceDate,"yyyyq")=Format(DateAdd("yyyy",-1,Date()),"
yyyyq"))*Units) AS
PrevQTD, -Sum((Format(InvoiceDate,"yyyymm")=Format(Date(),"yyyymm"))*Units)
AS
CurMTH, -Sum((Format(InvoiceDate,"yyyymm")=Format(DateAdd("yyyy",-1,Date()),
"yyyymm"))*Units) AS PrevMTH

FROM YourTable

WHERE ((InvoiceDate Between DateSerial(Year(Date()),1,1) And Date() Or
InvoiceDate Between DateSerial(Year(Date())-1,1,1) And
DateAdd("yyyy",-1,Date())))

GROUP BY CustomerNumber;



Peter



michael c said:
Duane, That's awesome!! THANK YOU!!!!
-----Original Message-----
You can create a totals query like:
SELECT CustomerNumber,
Abs(Sum( (Year(InvoiceDate) = Year(Date())) * [Units])) as CurYTD,
Abs(Sum( (Format(InvoiceDate,"yyyyq") = Format(Date (),"yyyyq")) * [Units]))
as CurQTD,
etc...
FROM tableA
GROUP BY CustomerNumber;

You need to add the expressions that evaluate to either true or false for
the other date periods.
--
Duane Hookom
MS Access MVP


Hi. I'm stuck...I have several hundred thousand records
that are in a table like so:

InvoiceDate CustomerNumber Product Units
1/12/03 0000056789 Acme Gloves 10

I'm trying to create a query and, ultimately, a report
that will group the orders so that I can compare how much
each customer ordered:

Current Year To Date
Current Quarter To Date
Current Month To Date
Prior Year To Date
Prior Quarter To Date
Prior Month To Date

The problem is getting my date filters in the same query.
Any thoughts? Thanks!!
Click to expand...


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

Peter Surcouf

My sincere apologies to Duane for the incorrect spelling.

Peter Surcouf said:
Michael

I think Daune was incorrect in using abs() there may be some (depressing)
periods when goods were only credited thus one should use "-" instead.
Also if comparing the sales all in one query then one needs to add a WHERE
clause which will only select the dates of the same period for the previous
year. I enclose the whole query, using Daune's logic, below *which is
untested*

SELECT CustomerNumber, -Sum(((Year(InvoiceDate)=Year(Date()))*Units)) AS
CurYTD, -Sum(((Year(InvoiceDate)=Year(Date())-1)*Units)) AS
PrevYTD, -Sum((Format(InvoiceDate,"yyyyq")=Format(Date(),"yyyyq"))*Units) AS
Click to expand...
CurQTD, -Sum((Format(InvoiceDate,"yyyyq")=Format(DateAdd("yyyy",-1,Date()),"
yyyyq"))*Units) AS
Click to expand...
PrevQTD, -Sum((Format(InvoiceDate,"yyyymm")=Format(Date(),"yyyymm"))*Units)
AS
Click to expand...
CurMTH, -Sum((Format(InvoiceDate,"yyyymm")=Format(DateAdd("yyyy",-1,Date()),
"yyyymm"))*Units) AS PrevMTH

FROM YourTable

WHERE ((InvoiceDate Between DateSerial(Year(Date()),1,1) And Date() Or
InvoiceDate Between DateSerial(Year(Date())-1,1,1) And
DateAdd("yyyy",-1,Date())))

GROUP BY CustomerNumber;



Peter



michael c said:
Duane, That's awesome!! THANK YOU!!!!
-----Original Message-----
You can create a totals query like:
SELECT CustomerNumber,
Abs(Sum( (Year(InvoiceDate) = Year(Date())) * [Units])) as CurYTD,
Abs(Sum( (Format(InvoiceDate,"yyyyq") = Format(Date (),"yyyyq")) * [Units]))
as CurQTD,
etc...
FROM tableA
GROUP BY CustomerNumber;

You need to add the expressions that evaluate to either true or false for
the other date periods.
--
Duane Hookom
MS Access MVP


Hi. I'm stuck...I have several hundred thousand records
that are in a table like so:

InvoiceDate CustomerNumber Product Units
1/12/03 0000056789 Acme Gloves 10

I'm trying to create a query and, ultimately, a report
that will group the orders so that I can compare how much
each customer ordered:

Current Year To Date
Current Quarter To Date
Current Month To Date
Prior Year To Date
Prior Quarter To Date
Prior Month To Date

The problem is getting my date filters in the same query.
Any thoughts? Thanks!!


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

Duane Hookom

No problem on the name. Good catch on the abs(). I was think of positive
Units values only.

--
Duane Hookom
MS Access MVP


Peter Surcouf said:
My sincere apologies to Duane for the incorrect spelling.
Click to expand...
PrevYTD, -Sum((Format(InvoiceDate,"yyyyq")=Format(Date(),"yyyyq"))*Units)
AS
Click to expand...
CurQTD, -Sum((Format(InvoiceDate,"yyyyq")=Format(DateAdd("yyyy",-1,Date()),"PrevQTD, -Sum((Format(InvoiceDate,"yyyymm")=Format(Date(),"yyyymm"))*Units)CurMTH, -Sum((Format(InvoiceDate,"yyyymm")=Format(DateAdd("yyyy",-1,Date()),
"yyyymm"))*Units) AS PrevMTH

FROM YourTable

WHERE ((InvoiceDate Between DateSerial(Year(Date()),1,1) And Date() Or
InvoiceDate Between DateSerial(Year(Date())-1,1,1) And
DateAdd("yyyy",-1,Date())))

GROUP BY CustomerNumber;



Peter



michael c said:
Duane, That's awesome!! THANK YOU!!!!

-----Original Message-----
You can create a totals query like:
SELECT CustomerNumber,
Abs(Sum( (Year(InvoiceDate) = Year(Date())) * [Units]))
as CurYTD,
Abs(Sum( (Format(InvoiceDate,"yyyyq") = Format(Date
(),"yyyyq")) * [Units]))
as CurQTD,
etc...
FROM tableA
GROUP BY CustomerNumber;

You need to add the expressions that evaluate to either
true or false for
the other date periods.
--
Duane Hookom
MS Access MVP


in message
Hi. I'm stuck...I have several hundred thousand records
that are in a table like so:

InvoiceDate CustomerNumber Product Units
1/12/03 0000056789 Acme Gloves 10

I'm trying to create a query and, ultimately, a report
that will group the orders so that I can compare how
much
each customer ordered:

Current Year To Date
Current Quarter To Date
Current Month To Date
Prior Year To Date
Prior Quarter To Date
Prior Month To Date

The problem is getting my date filters in the same
query.
Any thoughts? Thanks!!


.
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

Show only this year 4
Return a date range based on today's date 6
Querry by Quarter 7
Date Labels 2
date function 3
Year to date month/year 1
Select Max and Next to Max Dates 2
Query Date Help W/Avg work days 7

Top