Month to date and Year to date sales figures

B

Bon

Hi All,

I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value

Where "Category" has the domain BudgetTurnover and ActualTurnover
and "Month" is of type Date/Time.

The budget figures have been entered for the year.

I get the actual monthly figures at the end of each month.

What I want to do is get a table which looks like this for a given month:

Customer_Code Product_Code Value(ActualMTD) Value(BudgetMTD) Variance(MTD)
Value(ActualYTD) Value(BudgetYTD) Variance(YTD)

It could also be that I want to change the columns slightly and get:
Sale_Representative Product_Code Value(ActualMTD) Value(BudgetMTD)
Variance(MTD) Value(ActualYTD) Value(BudgetYTD) Variance(YTD)

I have working out (with help of David Seeto on this site), how to get the
MTD figures but have a mental block how to get the YTD figures as well.

Can someone help me out say with the appropriate SQL perhaps?

Thanks
Bon
 
D

Duane Hookom

A few sample records and desired display in your query would be invaluable
in helping you with this question. Is this something you could take the time
to include in a reply?
 
T

Tricia Young

Duane, here is what I am working on. I have been tracking leased assets in
Access. I created the Table "tblAssetTracking" that documents chrCustomer,
chrAsset, curLeasefee, curStandardfee, dtmStartDate and dtmEndDate.

I created a Query to create a table that archives transactions monthly. I
would like to use the table to create transaction queries that would by
customer and asset give me MTD and YTD running sums.

Example:
Customer Asset Month Lease Fee Running Sum
Ace Pump1 Jan/05 $100 $100
Ace Pump1 Feb/05 $100 $200
Ace Pump1 Mar/05 $75 $275
Ace Pump2 Mar/05 $100 $100
Ace Mixer Jan/05 $210 $210
PaintTec Pump1 Jan/05 $75 $75
PaintTec Pump2 Jan/05 $100 $100

I have created the following:

#1 Monthly Transaction Records by Customer
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].[Customer Name], [Transaction
Records].Description, Sum([Transaction Records].[Lease Fee]) AS [Total Of
Lease Fee]
FROM [Transaction Records]
WHERE ((([Transaction Records].Description) Is Not Null))
GROUP BY [Transaction Records].[Customer Name], [Transaction
Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


#2 Monthly Transaction Records by Asset
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].Description, Sum([Transaction Records].[Lease
Fee]) AS [Total Of Lease Fee]
FROM [Transaction Records]
WHERE ((Year([transaction month])=2005))
GROUP BY [Transaction Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



Can you help me!
 
D

Duane Hookom

I still don't understand how you want your final display to look... You
mention MTD and YTD running sums but show a "Running Sum" column and then a
couple crosstab sqls.

--
Duane Hookom
MS Access MVP
--

Tricia Young said:
Duane, here is what I am working on. I have been tracking leased assets in
Access. I created the Table "tblAssetTracking" that documents chrCustomer,
chrAsset, curLeasefee, curStandardfee, dtmStartDate and dtmEndDate.

I created a Query to create a table that archives transactions monthly. I
would like to use the table to create transaction queries that would by
customer and asset give me MTD and YTD running sums.

Example:
Customer Asset Month Lease Fee Running Sum
Ace Pump1 Jan/05 $100 $100
Ace Pump1 Feb/05 $100 $200
Ace Pump1 Mar/05 $75 $275
Ace Pump2 Mar/05 $100 $100
Ace Mixer Jan/05 $210 $210
PaintTec Pump1 Jan/05 $75 $75
PaintTec Pump2 Jan/05 $100 $100

I have created the following:

#1 Monthly Transaction Records by Customer
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].[Customer Name], [Transaction
Records].Description, Sum([Transaction Records].[Lease Fee]) AS [Total Of
Lease Fee]
FROM [Transaction Records]
WHERE ((([Transaction Records].Description) Is Not Null))
GROUP BY [Transaction Records].[Customer Name], [Transaction
Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


#2 Monthly Transaction Records by Asset
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].Description, Sum([Transaction Records].[Lease
Fee]) AS [Total Of Lease Fee]
FROM [Transaction Records]
WHERE ((Year([transaction month])=2005))
GROUP BY [Transaction Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



Can you help me!


Duane Hookom said:
A few sample records and desired display in your query would be
invaluable
in helping you with this question. Is this something you could take the
time
to include in a reply?
Click to expand...
Click to expand...
 
T

Tricia Young

Sorry. I know I said a lot. In the end I need a report to tell me what the
customer, the asset they are leasing, the month, the lease fee amount for
that month, and column that keeps an accumalative total (this month's fee
plus any fees paid in the past).

Thanks, for your time.

Tricia

Duane Hookom said:
I still don't understand how you want your final display to look... You
mention MTD and YTD running sums but show a "Running Sum" column and then a
couple crosstab sqls.

--
Duane Hookom
MS Access MVP
--

Tricia Young said:
Duane, here is what I am working on. I have been tracking leased assets in
Access. I created the Table "tblAssetTracking" that documents chrCustomer,
chrAsset, curLeasefee, curStandardfee, dtmStartDate and dtmEndDate.

I created a Query to create a table that archives transactions monthly. I
would like to use the table to create transaction queries that would by
customer and asset give me MTD and YTD running sums.

Example:
Customer Asset Month Lease Fee Running Sum
Ace Pump1 Jan/05 $100 $100
Ace Pump1 Feb/05 $100 $200
Ace Pump1 Mar/05 $75 $275
Ace Pump2 Mar/05 $100 $100
Ace Mixer Jan/05 $210 $210
PaintTec Pump1 Jan/05 $75 $75
PaintTec Pump2 Jan/05 $100 $100

I have created the following:

#1 Monthly Transaction Records by Customer
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].[Customer Name], [Transaction
Records].Description, Sum([Transaction Records].[Lease Fee]) AS [Total Of
Lease Fee]
FROM [Transaction Records]
WHERE ((([Transaction Records].Description) Is Not Null))
GROUP BY [Transaction Records].[Customer Name], [Transaction
Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


#2 Monthly Transaction Records by Asset
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].Description, Sum([Transaction Records].[Lease
Fee]) AS [Total Of Lease Fee]
FROM [Transaction Records]
WHERE ((Year([transaction month])=2005))
GROUP BY [Transaction Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



Can you help me!


Duane Hookom said:
A few sample records and desired display in your query would be
invaluable
in helping you with this question. Is this something you could take the
time
to include in a reply?

--
Duane Hookom
MS Access MVP
--

Hi All,

I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value

Where "Category" has the domain BudgetTurnover and ActualTurnover
and "Month" is of type Date/Time.

The budget figures have been entered for the year.

I get the actual monthly figures at the end of each month.

What I want to do is get a table which looks like this for a given
month:

Customer_Code Product_Code Value(ActualMTD) Value(BudgetMTD)
Variance(MTD)
Value(ActualYTD) Value(BudgetYTD) Variance(YTD)

It could also be that I want to change the columns slightly and get:
Sale_Representative Product_Code Value(ActualMTD) Value(BudgetMTD)
Variance(MTD) Value(ActualYTD) Value(BudgetYTD) Variance(YTD)

I have working out (with help of David Seeto on this site), how to get
the
MTD figures but have a mental block how to get the YTD figures as well.

Can someone help me out say with the appropriate SQL perhaps?

Thanks
Bon
Click to expand...
Click to expand...
Click to expand...
 
D

Duane Hookom

This query should get you the customer, product, current month and MTD &
YTD.

Select Customer_Code, Product_Code, Category,
Sum([Value] * Abs(Format(Date(),"yyyymm") = Format([Month],"yyyymm"))) as
MTD,
Sum([Value] * Abs(Year(Date()) = Year([Month]))) as YTD
FROM tblYourTableOrQuery
GROUP BY Customer_Code, Product_Code, Category;

--
Duane Hookom
MS Access MVP


Tricia Young said:
Sorry. I know I said a lot. In the end I need a report to tell me what
the
customer, the asset they are leasing, the month, the lease fee amount for
that month, and column that keeps an accumalative total (this month's fee
plus any fees paid in the past).

Thanks, for your time.

Tricia

Duane Hookom said:
I still don't understand how you want your final display to look... You
mention MTD and YTD running sums but show a "Running Sum" column and then
a
couple crosstab sqls.

--
Duane Hookom
MS Access MVP
--

Tricia Young said:
Duane, here is what I am working on. I have been tracking leased assets
in
Access. I created the Table "tblAssetTracking" that documents
chrCustomer,
chrAsset, curLeasefee, curStandardfee, dtmStartDate and dtmEndDate.

I created a Query to create a table that archives transactions monthly.
I
would like to use the table to create transaction queries that would by
customer and asset give me MTD and YTD running sums.

Example:
Customer Asset Month Lease Fee Running Sum
Ace Pump1 Jan/05 $100 $100
Ace Pump1 Feb/05 $100 $200
Ace Pump1 Mar/05 $75 $275
Ace Pump2 Mar/05 $100 $100
Ace Mixer Jan/05 $210 $210
PaintTec Pump1 Jan/05 $75 $75
PaintTec Pump2 Jan/05 $100 $100

I have created the following:

#1 Monthly Transaction Records by Customer
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].[Customer Name], [Transaction
Records].Description, Sum([Transaction Records].[Lease Fee]) AS [Total
Of
Lease Fee]
FROM [Transaction Records]
WHERE ((([Transaction Records].Description) Is Not Null))
GROUP BY [Transaction Records].[Customer Name], [Transaction
Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");


#2 Monthly Transaction Records by Asset
TRANSFORM Sum([Transaction Records].[Lease Fee]) AS [SumOfLease Fee]
SELECT [Transaction Records].Description, Sum([Transaction
Records].[Lease
Fee]) AS [Total Of Lease Fee]
FROM [Transaction Records]
WHERE ((Year([transaction month])=2005))
GROUP BY [Transaction Records].Description
PIVOT Format([Transaction Month],"mmm") In
("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");



Can you help me!


:

A few sample records and desired display in your query would be
invaluable
in helping you with this question. Is this something you could take
the
time
to include in a reply?

--
Duane Hookom
MS Access MVP
--

Hi All,

I have a table with the following attributes:
Customer_Code, Product_Code, Category, Month, Value

Where "Category" has the domain BudgetTurnover and ActualTurnover
and "Month" is of type Date/Time.

The budget figures have been entered for the year.

I get the actual monthly figures at the end of each month.

What I want to do is get a table which looks like this for a given
month:

Customer_Code Product_Code Value(ActualMTD) Value(BudgetMTD)
Variance(MTD)
Value(ActualYTD) Value(BudgetYTD) Variance(YTD)

It could also be that I want to change the columns slightly and get:
Sale_Representative Product_Code Value(ActualMTD) Value(BudgetMTD)
Variance(MTD) Value(ActualYTD) Value(BudgetYTD) Variance(YTD)

I have working out (with help of David Seeto on this site), how to
get
the
MTD figures but have a mental block how to get the YTD figures as
well.

Can someone help me out say with the appropriate SQL perhaps?

Thanks
Bon
Click to expand...
Click to expand...
Click to expand...
 
T

Tricia Young

Duane, You have been very helpful. Can you help me further?

I have a database that I would like to have the following happen.

1. Automatically Run an Append Query on the first Monday of every month.
Once it is done then I don't want it to run again.

I already have it set up to where when the DB opens the Switchboard
opens first. When the Switchboard opens I get a prompt "Today is the 5th.
Would you like to archive transactions?" This is set to run on the 5th of
every month; but everytime the Switchboard opens (which I close the database
and open several times a day) it asks the question. And also if it's the
weekend or I don't open the database on the 5th after that date I will not
get a prompt to run the query and perform the archive.

2. I would like for Access to also send via e-mail reports to my co-workers
on the first Tuesday of every month. I don't want to edit the message. I
just want it to done in the background without any interaction from me. Is
that possible? If not can it be done with minimal interaction from me. I
have the reports name and e-mail addresses. I use Lotus Notes as my e-mail
software.

Any help is greatly appreciated. Will be glad to provide more info if needed.

Tricia
 
D

Duane Hookom

Please start a new thread so your chances of getting a better answer are
greater.
 
T

Tricia Young

I did that first. I used this thread second in order to get advice from you.
Can you help me? I posted this question under the subject "Automation". I
recently added the code I am currently using.

Thanks,
Tricia
 
D

Duane Hookom

What news group did you start the thread? I don't see another in this NG
with your name.
 

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

SQL query showing diff between actual and budget 3
Calculate MTD and YTD in a query 8
if function (if more that 7 nesting) 2
Year to Date subquery 7
Year Beginning Date 2
converting year to date data to month to date 3
Another MTD/YTD Running Totals Question 2
delete selection in pivotcolumn 3

Top