ACCESS FEED: RE: [MS_AccessPros] Calculated field?

Rabu, 28 Desember 2011

RE: [MS_AccessPros] Calculated field?

Tiffany-

Modify the query:

SELECT T1.[Unit], T1.YearMonth, T1.Mileage As Current, T2.Mileage As LastMonth,
T1.Mileage - NZ(T2.Mileage, 0) AS Total FROM tblUnitMonthMileage AS T1 LEFT JOIN
tblUnitMonthMileage AS T2 ON (T1.[Unit]=T2.[Unit]) AND (T2.YearMonth =
IIf(Right(T1.YearMonth, 2) = "01", Format(CInt(Left(T1.YearMonth, 4)) - 1,
"0000") & "12", Left(T1.YearMonth, 4) & Format(CInt(Right(T1.YearMonth, 2)) - 1,
"00")));

You can perhaps calculate the average you want by grouping by Year and
calculating the average in the group footer.

John Viescas, author
Microsoft Office Access 2010 Inside Out
Microsoft Office Access 2007 Inside Out
Building Microsoft Access Applications
Microsoft Office Access 2003 Inside Out
SQL Queries for Mere Mortals
http://www.viescas.com/
(Lawton, OK)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com
[mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Wednesday, December 28, 2011 11:30 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: [MS_AccessPros] Calculated field?

Hi,

I can see that I will definitely need assistance on this. Can someone talk me
through this?

I have a report (Reed's Report)with four fields Current, Last Month, Total and
Average. I have a table (tbleunitmonthmileage)that contains all the mileage by
Unit, YearMonth, Mileage and I have a query Querytblunitmonthmileage

SELECT T1.[Unit], T1.YearMonth, T1.Mileage - NZ(T2.Mileage, 0) AS Miles FROM
tblUnitMonthMileage AS T1 LEFT JOIN tblUnitMonthMileage AS T2 ON
(T1.[Unit]=T2.[Unit]) AND (T2.YearMonth = IIf(Right(T1.YearMonth, 2) = "01",
Format(CInt(Left(T1.YearMonth, 4)) - 1, "0000") & "12", Left(T1.YearMonth, 4) &
Format(CInt(Right(T1.YearMonth, 2)) - 1, "00")));

How do I use/direct the information from tblunitmonthmileage or the query to the
fields I have in Reed's Report? The query shows the mileage by actual miles
traveled every month with a beginning odometer reading, the table show the
mileage as a monthly odometer reading.

on the report:Current is the current mileage for the month (odometer reading)
Last Month is the mileage for the previous month (odometer reading)
Total is Last month-Current
Average needs to take all the mileage for that unit from the last 12 months and
average it and project it based on the average to a month in the future. This
will tell me what month the unit will need to be replaced based on usage.

Thanks,

Tiffany

------------------------------------

Yahoo! Groups Links