J
Jason
Hi, I have the following query:
SELECT tblValidationMileages.Model, tblValidationMileages.Derivative,
tblValidationMileages.[Sale Date], tblValidationMileages.[Reg Plate],
tblValidationMileages.Mileage, tblValidationMileages.[Sold Price],
tblValidationMileages.Clean, [tblPlates&Dates].[Plate Date],
Format([Sale Date],"dd/mm/yyyy") AS SaleDateFormatted,
DateDiff("d",[Plate Date],[SaleDateFormatted])/365 AS [Age in Years],
[Mileage]/[Age in Years] AS [Annual Mileage]
FROM [tblPlates&Dates] INNER JOIN tblValidationMileages ON
[tblPlates&Dates].[Full Plate] = tblValidationMileages.[Reg Plate]
WHERE (((tblValidationMileages.Mileage)<335000));
The final calculated field is [Annual Mileage]. So I have one Annual
Mileage for each record but what I'd like to do is Average them so the
query just returns one number.
How can I do this in the same query (instead of simply querying this query).
Any help greatly appreciated,
Jason
P.S Forgive the SaleDateFormatted field - this is because the source
table has a date field which is formatted as text and Access won't allow
me to change the datatype, so I have this calculate field to give me it
as an actual date.
SELECT tblValidationMileages.Model, tblValidationMileages.Derivative,
tblValidationMileages.[Sale Date], tblValidationMileages.[Reg Plate],
tblValidationMileages.Mileage, tblValidationMileages.[Sold Price],
tblValidationMileages.Clean, [tblPlates&Dates].[Plate Date],
Format([Sale Date],"dd/mm/yyyy") AS SaleDateFormatted,
DateDiff("d",[Plate Date],[SaleDateFormatted])/365 AS [Age in Years],
[Mileage]/[Age in Years] AS [Annual Mileage]
FROM [tblPlates&Dates] INNER JOIN tblValidationMileages ON
[tblPlates&Dates].[Full Plate] = tblValidationMileages.[Reg Plate]
WHERE (((tblValidationMileages.Mileage)<335000));
The final calculated field is [Annual Mileage]. So I have one Annual
Mileage for each record but what I'd like to do is Average them so the
query just returns one number.
How can I do this in the same query (instead of simply querying this query).
Any help greatly appreciated,
Jason
P.S Forgive the SaleDateFormatted field - this is because the source
table has a date field which is formatted as text and Access won't allow
me to change the datatype, so I have this calculate field to give me it
as an actual date.