ACCESS FEED: RE: [MS_AccessPros] Calculated field?

Tiffany-

One of the reasons people like Bill, Crystal, Duane, A.D., and me help out on this forum is we get a lot of pleasure watching people grow as they learn to best use our favorite product - Access. However, I don't get the sense that you're learning anything. You post a problem, we provide a solution, and you use it without really understanding what we're doing for you. If you were learning something, you would be able to apply what you learned toward getting better at finding solutions on your own. Don't get me wrong - we're happy to continue to provide you with answers, but I personally wish you would put a bit more effort into understanding what's going on.

For example, let's take a look at queries. Queries are one of the most powerful tools in Access. You can build simple or complex ones to fetch the answers to your questions from your data. Most queries have three main pieces:

SELECT - pick the fields or expressions you want the query to return.
FROM - specify where you want Access to get the data, and when there are multiple related tables, tell Access how to link them all together. Note that you can use not only tables in the FROM clause but also other queries. When you use a query, you're asking to use it like it is a "virtual" table built from what the query returns.
WHERE - specify restrictions as to which rows should be returned from the FROM clause.

Here's the query I built for you:

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")));

Notice that there is no WHERE clause, so the query will return all the data from what's specified in FROM.

The FROM clause is rather complex because it "joins" table tblUnitMonthMileage to itself. I did this so that the returned records will "line up" the data from the current month with the data from the previous month found in the second copy of the table. This lets you calculate the difference between the mileage reading for two months as the number of miles traveled. Let's break it down bit by bit:

FROM tblUnitMonthMileage AS T1 LEFT JOIN tblUnitMonthMileage AS T2 ...

We're getting all the rows from tblUnitMonthMileage and assigning an alias name (T1) to this copy. The LEFT JOIN means we want ALL the rows from the table on the "left" - the T1 copy. On the right side of the JOIN, we've asked Access to include a second copy of tblUnitMonthMileage and call it T2 to distinguish it from the first copy.

Now let's look at how we've asked Access to link the two copies of the same table.

ON (T1.[Unit]=T2.[Unit]) ...

We want rows of data for each unit to "line up" with each other. We're not interested in matching rows for random units. Now comes the complex part:

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")));

We want to match a row where the YearMonth (format yyyymm) in the *second* copy of the table is equal to the month previous to the YearMonth found in the *first* copy of the table. For example, we want:

T1.Unit T1.YearMonth T2.Unit T2.YearMonth
1 201111 1 201110

This will also give us the mileage reading for November 2011 and the mileage reading for October 2011 - simply subtract the two to get the miles traveled in the month ending 2011 11. But how would you figure out the value of the "previous" month? That's where the IIf comes into play. IIf is a very useful function that has three pieces:

expression to evaluate for true/false, expression to return when true, expression to return when false.

Let's look at the first part:

Right(T1.YearMonth, 2) = "01"

The Right function returns the number of characters specified from the "right" end of a string of characters. Let's say T1.YearMonth contains 201101 - January of 2011. To calculate the "previous" month, we really need December of 2010 - or 201012. We can look at the month part of the YearMonth string to see if it contains 01 for January. We're asking the IIf function to perform this test and return the second argument if this is true. If it is not true, we want the third argument. The second argument is:

Format(CInt(Left(T1.YearMonth, 4)) - 1, "0000") & "12"

As you might suspect, the Left function returns the specified number of characters from the left end of a string. Grabbing the leftmost 4 characters should give us the year part of the YearMonth field. In this case, "2011". CInt is a "convert to integer" function. We need to do this because we want to subtract 1 to get the value of the previous year. You cannot perform arithmetic on a string of numbers. So, CInt returns the integer value 2011, and the -1 subtracts one to get 2010. The expression uses the Format function to convert that back to a string, so we get "2010". The ampersand & is the concatenation operator - concatenate the value "12", and we get the string value "201012" that we need to use to find the "previous" month in the second copy of the table.

But what if the current month in T1 isn't January? That's where the third part comes into play:

Left(T1.YearMonth, 4) & Format(CInt(Right(T1.YearMonth, 2)) - 1, "00"

If the month is 02, 03, 04, 05, ..., 10, 11, or 12, we don't need to change the year part for the comparison. We grab the leftmost 4 characters for the year and concatenate that with the calculation for the previous month value. Right(T1.YearMonth, 2) gets us the month value. CInt converts that to an integer, subtract 1, then use Format to change it back to a 2-digit string. The end result is when YearMonth in T1 is, for example, 201111, the comparison value for T2 becomes 201110.

If it's not obvious, an expression like T1.YearMonth is asking for the YearMonth field in the T1 copy of the table. Finally, let's look at the SELECT:

SELECT T1.[Unit], T1.YearMonth, T1.Mileage As Current, T2.Mileage As LastMonth,
T1.Mileage - NZ(T2.Mileage, 0) AS Total

T1.[Unit] returns the Unit from the first copy of the table.
T1.YearMonth returns YearMonth from the first copy of the table - the "current" month.
T1.Mileage returns the mileage reading at the end of the month found in T1.YearMonth
T2.Mileage returns the mileage reading at the end of the *previous* month found in T2.YearMonth
Subtract T2.Mileage from T1.Mileage to get the total miles driven. The NZ function - Null To Zero - guards against not finding a matching row for the "previous" month. When that happens, T2.Mileage will contain the special Null value. NZ will look at T2.Mileage, and if it is not Null, then the value will be returned. If it is Null, then the 0 we specified will be used instead.

If the above is not clear, please ask further questions.

Now you have said you need the average for the last twelve months. Don't you suppose you need to add a WHERE clause to limit the rows returned? You have T1.YearMonth, so you want to limit it to just the last 12 months - including the current month.

Here's where a few date/time functions come in handy.

Date() - returns today's date from your computer clock.
DateAdd() - will add or subtract seconds, minutes, hours, days, months, or years from a date/time value
Year() - returns the 4-digit year part of a date/time value
Month() - returns the 2-digit month part of a date/time value

You probably will run this query in the middle of a month - so the "last" month's worth of data will be for the previous month. What you need is the YearMonth for the previous month. Here's one way to do it:

Format(DateAdd("m", -1, Date()), "yyyymm")

We are asking DateAdd to subtract one month ("m") from the current data. We then use the Format function to turn that into a string to match what's in the YearMonth field. Since today is December 30, 2011, that expression will return "201111" - the last month of interest.

To find out the first month you want, use the same expression above, but subtract 13 to get the start month.

Why don't you take a stab at a WHERE clause that will limit the rows to the ones you want?

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/
(Waco, Texas <yee haw>)

-----Original Message-----
From: MS_Access_Professionals@yahoogroups.com [mailto:MS_Access_Professionals@yahoogroups.com] On Behalf Of cc0623
Sent: Thursday, December 29, 2011 10:06 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Calculated field?

John,

That worked excellent, but the report is now showing the Unit for every month there is a mileage instead of just for the current month. Almost there!

Tiffany Atchley

WWW.ODYSSEYMINISHETLANDS.NET

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Thursday, December 29, 2011 8:24 AM
Subject: RE: [MS_AccessPros] Calculated field?

Tiffany-

Where is the relationship (JOIN) between Assets and the query? Try this:

SELECT Assets.Unit, Assets.Year, Assets.Make, Assets.Description, Assets.Driver, Assets.Department, Assets.[Vin#], Assets.Supervisor, Assets.Sold, Qrytblunitmonthmileage.Current, Qrytblunitmonthmileage.LastMonth, Qrytblunitmonthmileage.Total
FROM Assets INNER JOIN Qrytblunitmonthmileage ON Assets.Unit = Qrytblunitmonthmileage.Unit

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: Thursday, December 29, 2011 9:06 AM
To: MS_Access_Professionals@yahoogroups.com
Subject: Re: [MS_AccessPros] Calculated field?

John,

That worked great! But when I added the needed fields to QryReedsReport,

SELECT Assets.Unit, Assets.Year, Assets.Make, Assets.Description, Assets.Driver, Assets.Department, Assets.[Vin#], Assets.Supervisor, Assets.Sold, Qrytblunitmonthmileage.Current, Qrytblunitmonthmileage.LastMonth, Qrytblunitmonthmileage.Total
FROM Assets, Qrytblunitmonthmileage;

It shows on the report as no mileage for Last Month, and current & total is the same for every record. How do I tell the report to pick up the Current month (every month), the last month and total it.

Also I need to not show the sold vehicles on the report. So I added the sold field with the intention of telling it to exclude all records with this checked field but it is not working out the way I thought it would.

Tiffany Atchley

________________________________
From: John Viescas <john@viescas.com>
To: MS_Access_Professionals@yahoogroups.com
Sent: Wednesday, December 28, 2011 7:48 PM
Subject: 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

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links

[Non-text portions of this message have been removed]

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

Yahoo! Groups Links