Compare this month to last month

[acss]

I have a select query that i would like to group by month or quarter and
compare it to the previous month or quarter. Can this be done?

SELECT VendorInv.EnterDate, Vendor.VendorID, Vendor.VendorName,
VendorInv.InvoiceID, VendorInv.InvDesc, VendorInv.InvAmt
FROM (VendorExpCtr INNER JOIN Vendor ON VendorExpCtr.VendorExpCtrID =
Vendor.VendorExpCtrID) INNER JOIN VendorInv ON Vendor.VendorID =
VendorInv.VendorID
ORDER BY VendorInv.EnterDate;

 

[Michel Walsh]

If you already have a result 'by month', and by that, I assume the date is
the first of the month, such as with:

SELECT whatever,
DateSerial( DatePart("yyyy", date_time_field), DatePart("m",
date_time_field), 1 ) As FirstOfMonth
FROM somewhere
GROUP BY DateSerial( DatePart("yyyy", date_time_field), DatePart("m",
date_time_field), 1 )

(sure, that query should 'run' without problem)

then, to compare the actual month with the previous one, make a new query,
bring the previous query TWICE (one will get an _1 appended to its name).
Drag the required fields in the grid. Under xxx_1.FirstOfMonth, add the
criteria:

= DateAdd("m", -1, xxx.FirstOfMonth)


where xxx is the name of the saved query.

It should then be clear that data coming from xxx_1 will correspond to the
data of the month preceding the data taken from xxx.

xxx.Quantity - xxx_1.Quantity


as example, will supply the difference in quantity, between the two months.


Hoping it may help,
Vanderghast, Access MVP

 

[acss]

The VendorInv.EnterDate represents the record entry date of the invoice with
a format of 04/01/2008 and invoices are entered daily. I have not figured out
how to group them to give me a total for the month. In my query i am using 3
tables, would this process be easier using one table that contains the
invoice data to do the comparison to the previous month?

 

[Michel Walsh]

That would be preferable if data is in one table.



SELECT productID,
SUM(QtySold*UnitPrice) As totalSales,
DateSerial( DatePart("yyyy", date_time_field), DatePart("m",
date_time_field), 1 ) As FirstOfMonth
FROM yourUniqueTable
GROUP BY DateSerial( DatePart("yyyy", date_time_field), DatePart("m",
date_time_field), 1 )



Will group each product, by month, and give the totalSales. You can do the
query graphically, instead of doing it in SQL view, if you prefer. (but it
is harder to describe 'graphically what the query looks like, that is why we
often communicate the query through its SQL).


Vanderghast, Access MVP

 

[acss]

I am fairly new to this so is your last posting just to group the invoices
totals into one month in a query? Below is is the SQL for the VendorInv
table which contains invoice totals so do i apply your last posting to obtain
comparison between this month and last month invoice totals?

SELECT VendorInv.EnterDate, VendorInv.InvoiceID, VendorInv.InvDesc,
VendorInv.InvAmt
FROM VendorInv
ORDER BY VendorInv.EnterDate;

 

[Michel Walsh]

SELECT DateSerial( DatePart("yyyy", EnterDate), DatePart("m", EnterDate),
1) As FirstOfMonth,
SUM(InvAmt) AS totalForThisMonth
FROM VendorInv
GROUP BY DateSerial( DatePart("yyyy", EnterDate), DatePart("m", EnterDate),
1)



Should give the total, by month. Save that query under the name of, say,
q1. Then, make a new query. Bring q1 twice, on will get the name q1_1. Drag
the fields from q1 in the grid. Drag q1_1.totalForThisMonth in the grid.
Under q1.FirstOfMonth, add the criteria:

= DateAdd("m", 1, [q1_1].[FirstOfMonth] )


That second query should then show for the month of q1.FirstOfMonth, the
toal for that month, under q1.totalForThisMonth, and the total for the
previous month, under q1_1.totalForThisMonth.



The SQL statement should look like:



SELECT q1.FirstOfMonth, q1.TotalForThisMonth, q1_1.TotalForThisMonth
FROM q1, q1 AS q1_1
WHERE q1.FirstOfMonth = DateAdd("m", 1, [q1_1].[FirstOfMonth] )





Hoping it may help,
Vanderghast, Access MVP