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
Tidak ada komentar:
Posting Komentar