Question Month1 minus Month2

[HelpNeeded]

I have a database which holds sales data.

Date of Sale
Sale Price

I want to do is create a report that allows meto enter a select a month and the results would show the a sum of the month selected minue a sum of the previous month

so for example it go like this...I select Oct-09
The report shows

Sum of sales in Oct-09 =£30,000
Sum of sales in Sep-09 =£29,000
Difference = £1,000

I am really struggling though to know how to do this, can anyone help

 

[Galaxiom]

Use the DatePart function to create derived fields and group by month and year.
http://office.microsoft.com/en-au/access/HA012288121033.aspx

Sum the value of the sales.

Then build a query to put the month in one field and the previous month in another.
Calculate the difference in the third field.

 

[HelpNeeded]

Hi thanks for the prompt response.
I can do the first part of what you say so I have

Sum of Sales Month
£30,000 10
£29,000 9

But for the second part, how do I put month 9 in one field and month 10 in another field?

 

[Galaxiom]

Couple of possibilities.

Select Month, [Sum of Sales], DLookUp("[Sum of Sales]", "queryname", "queryname.[Month]=[Month]-1") AS PrevMonth FROM queryname

Another would be to make another query with a PrevMonth field derived by Month -1 then join on the firstquery.month = secondquery.PrevMonth

The second way is probably technically better because it uses only sql rather than repeatedly using the function.

 

[HelpNeeded]

Hi, I did that using the first way you suggested

MOnth sum of sales PrevMonth
9 100
10 75
11 50


BUt the results give this with nothing in the prevmonth

what am I doing wrong

to two bits of sql are as follows

SELECT Table1.date_of_sale, Sum(Table1.Sale_price) AS [Sum of Sales], Month([date_of_sale]) AS [month]
FROM Table1
GROUP BY Table1.date_of_sale, Month([date_of_sale]);


then

Select Month, [Sum of Sales], DLookUp("[Sum of Sales]", "query", "query.[Month]=[Month]-1") AS PrevMonth FROM query

 

[Galaxiom]

Maybe Access is gettng confused by trying to do a DLookUp on the same domain as the query. Or maybe I really should be asleep.

Try the other way.

SELECT [Sum of Sales], ([Month]-1) AS PrevMonth FROM [queryname]

Then join this to the original query and calculate the difference.
Later you can assemble this into a subquery structure to keep it tidy.

By the way it isn't really good to use Month as a field name as it is a reserved word. Be sure to put it inside [] but better to use a different name. I assume your query is not really called query.

 

[HelpNeeded]

Hi the only problem is that the results for the prevmonth doesnt give me the sum of the sale for the previous month

 

[datAdrenaline]

You could do a CrossTab query using the summation query that gives you the vertical results. A CrossTab will take this:

Sum of Sales Month
£30,000 10
£29,000 9

And transpose it to (the | is the column separator):

10|9
£30,000|£29,000

... Or ... you could get fancier on your query if you are only doing 2 months ...

SELECT "Sale for " & Format([Date Of Month],"mm-yyyy") As Desc,
    (SELECT Sum([COLOR=blue]Amount[/COLOR])
    FROM [COLOR=blue]tblSales[/COLOR]
    WHERE [COLOR=blue]SalesDate[/COLOR] 
          BETWEEN DateSerial(Year([Date Of A Month]), [Date Of A Month], 1)
          And DateSerial(Year([Date Of A Month]), [Date Of A Month] + 1, 0)) As MyMonth,
    (SELECT Sum([COLOR=blue]Amount[/COLOR])
    FROM [COLOR=blue]tblSales[/COLOR]
    WHERE [COLOR=blue]SalesDate[/COLOR] 
          BETWEEN DateSerial(Year([Date Of A Month]), [Date Of A Month]-1, 1)
          And DateSerial(Year([Date Of A Month]), [Date Of A Month], 0)) As PrevMonth
FROM ([COLOR=purple]SELECT TOP 1 * FROM MSysObjects[/COLOR])

The blue words you will have to substitute for the proper names in your tables. The SELECT FROM MSysObjects is there merely to allow the wrapping SELECT to work since this SELECT will needs a FROM and in Access, I know MSysObjects is always present.

.....

Another, possibly simpler, though I personally like to avoid the Access Domain functions in a query is ...

SELECT "Sales for " & Format([Date Of Month],"mm-yyyy") As Desc,
       DSum("Amount","tblSales","SalesDate BETWEEN DateSerial(Year([Date Of A Month]), [Date Of A Month], 1)
          And DateSerial(Year([Date Of A Month]), [Date Of A Month]+1, 0)") As MyMonth,
       DSum("Amount","tblSales","SalesDate BETWEEN DateSerial(Year([Date Of A Month]), [Date Of A Month]-1, 1)
          And DateSerial(Year([Date Of A Month]), [Date Of A Month], 0)") As PrevMonth
FROM (SELECT TOP 1 * FROM MSysObjects)

But ... stacking queries:

Summation Query (Vertical Data)
CrossTab Query of Summation Query (Horizontal Data)
Select Query of the CrossTab Query in order to subtract the two columns, or you can simply add an unbound control on the form/report with a Control Source expression to do the subtraction.

Is often a choice that is made, but I personally like a "fancy" single query

{note: all of this is AIR CODE, so look at the concept, since a copy/paste will likely not work}

 

[HelpNeeded]

thanks for all the advice, I got it to do what I needed in the end. BUt had to have two seperate queries, one to give me current month total sales. the other two give me previous month sales

and then in the form i just minus one from the other. it works fine, and gives me a little more flexiiblity in that i can now compare can two periods, day, month, week, year, etc. as the queries are just based on a simple between start and end date

thanks for the help really helped in getting me to the final solution.