Adding fields 2

[micang]

Access 2003

Hi All,

I have a table with field headings '2006,'2005','2004' Each field contains number values.

I would like to add the values in field '2005' and '2004' into a field called 'Pre-2006'

eg:

Cars Colour 2006 2005 2004
BMW Red 10 2 6
Ferrari Red 2 3 1


Output desired:

Cars Colour 2006 Pre-2006
BMW Red 10 8
Ferrari Red 2 4

I have no idea how to add fields across together.

Many thanks

Michael

 

[PHV]

Something like this (SQL code) ?
SELECT Cars, Colour, [2006], (Nz([2005],0)+Nz([2004],0)) AS [Pre-2006]
FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[lespaul]

the biggest problem is that your data isn't normalized.

Create a query that normalizes your data:

SELECT Car, Color, '2006' As CarYear, 2006 As NumberOfCars From TableName
UNION
SELECT Car, Color, '2005', 2005 From TableName
UNION
SELECT Car, Color, '2004', 2004 FROM TableName

save this query: qryNormal

Now:

SELECT Car, Color, CarYear, Count(*) FROM qryNormal WHERE CarYear = 2006
UNION
SELECT Car, Color, 'Pre-2006', Count(*) FROM qryNormal WHERE CarYear < 2006

This will produce:

BMW Red 2006 10
BMW Red Pre-2006 8
Ferrari Red 2006 2
Ferrari Red Pre-2006 4

then use the CrossTab Query wizard to convert it to:
Car Color 2006 Pre-2006
BMW Red 10 8
Ferrari Red 2 4

Leslie

Anything worth doing is a lot more difficult than it's worth - Unknown Induhvidual

Essential reading for database developers:
The Fundamentals of Relational Database Design
Understanding SQL Joins

 

[micang]

PH and Leslie, thanks so much, your help has solved my problem.

Much appreciated.

Michael