Stephen:
First lets make the query a bit more readable by getting rid of the
table names before each column name (as its all from one table you
don't need them) and by putting in some carriage returns so the
reference to each column is on a new line. To get the total mileage
you subtract the MIN([start mileage]) from the MAX([home mileage]),
and to get the total business mileage you Sum the difference between
the [start mileage] and the [home mileage] values from each row. As
the table is, to judge by its name, presumably just for 2007 you don't
need to group it by year. So the query looks like this:
SELECT MIN([start mileage]) AS [Start of year mileage], MAX([home
mileage]) AS [End of year mileage],
MAX([home mileage]) - MIN([start mileage]) AS [Total mileage],
SUM([home mileage] – [start mileage]) AS [Total business mileage],
SUM([gas]) AS [Sum Of gas], SUM([US gas]) AS [Sum Of US gas], SUM([US
gas2]) AS [Sum Of US gas2], SUM([servise $]) AS [Sum Of servise $],
SUM([us cost]) AS [Sum Of us cost], SUM([gas pur]) AS [Sum Of gas
pur], SUM([insureance]) AS [Sum Of insureance], SUM([plate]) AS [Sum
Of plate]
FROM [travel 2007];
Just paste the above into the query in SQL view and switch to
datasheet view. You should then get the results you are looking for.
If so save the query.
You'll see that I've changed the first two column names to something
more meaningful than [Min Of start mileage] etc. You don't have to
accept the names Access gives to aggregated columns, but can change
them either in SQL view or in design view.
I assume that you'll have created a new table for 2008 now. Having
separate tables per year is not good database design; it amounts to
encoding data (the year in this case) as table names, whereas it’s a
fundamental principle of the relational database model that data is
stored as values at column positions in rows in tables, and in no
other way (its called the 'Information Principle'). I assume you must
have a date column in the table, e.g. TravelDate, if so you can store
everything in one table and get the results for each year by
restricting the query, e.g.
SELECT
< and so on>
FROM [travel]
WHERE YEAR([TravelDate]) = 2007;
or for multiple years:
SELECT
< and so on>
FROM [travel]
WHERE YEAR([TravelDate]) BETWEEN 2005 AND 2007;
which would give the data for the three years, 2005, 2006 and 2007.
It could also be done with:
SELECT
< and so on>
FROM [travel]
WHERE [TravelDate]
BETWEEN #01/01/2005# AND #31/12/2007#;
Note that date literals must be in US date format or an otherwise
internationally unambiguous format (just to catch us Yurpeans out!)
Ken Sheridan
Stafford, England
Stephen said:
Thanks here is the code
*******************************
SELECT DISTINCTROW Min([travel 2007].[start mileage]) AS [Min Of
start mileage], Max([travel 2007].[home mileage]) AS [Max Of home
mileage], Sum([travel 2007].[gas]) AS [Sum Of gas], Sum([travel
2007].[US gas]) AS [Sum Of US gas], Sum([travel 2007].[US gas2]) AS
[Sum Of US gas2], Sum([travel 2007].[servise $]) AS [Sum Of servise
$], Sum([travel 2007].[us cost]) AS [Sum Of us cost], Sum([travel
2007].[gas pur]) AS [Sum Of gas pur], Sum([travel 2007].[insureance])
AS [Sum Of insureance], Sum([travel 2007].[plate]) AS [Sum Of plate]
FROM [travel 2007];
******************************
gibberish to me
Thank you for your help
Stephen
