Date Ranges as Columns

  • Thread starter Thread starter michael c
  • Start date Start date
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!!
 
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, 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...
 
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...
 
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...
 
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...
 
Back
Top