nevilleg
02-11-2002, 05:40 AM
I'm not an Access developer, so please excuse me if I'm a little vague. My background is in Oracle and I'm wanting to do something in Access that would be no problem in for me in Oracle SQL - but I'm strugling in Access....please help.
I have 2 tables that I'm wanting to do a query on. The first contains assetID for machines, along with the initial cost of these machines. I've then got a second table with 'additional costs' of the same machines, i.e. a machine requires a spare part, and thus has an entry in this table.
The query I'm running gets a full list of the machines from table 1, along with the initial cost of the machine, and also from table 2 the possible 'additional cost' of machines along with a final column which is the sum of the cost of the machine and the additional cost - the the query should return something like:
Asset|OrigionalCost|AdditionalCost|Total Cost
The SQL used to create this query is:
SELECT Asset.AssetID, Asset.[Original Cost], Addition.Cost, ([Original
Cost]+[Cost]) AS Closebal
FROM Asset LEFT JOIN Addition ON Asset.AssetID = Addition.[Asset ID];
This is fine as long as there is an additional cost for the machine. But as soon as there is a machine without an additional cost the final column is blank. This is obviously because the SQL is trying to add a machine value to a NULL additional cost. This would be no problem in Oracle, as I'd just do a DECODE() on the additional cost column to set all of the NULL values to 0, thus adding a machine values to 0. (which would give me the following SQL):
SELECT Asset.AssetID, Asset.[Original Cost], decode(Addition.Cost,null,'0',Addition.Cost), ([Original Cost]+[Cost]) AS Closebal
FROM Asset LEFT JOIN Addition ON Asset.AssetID = Addition.[Asset ID];
However, Access doesn't understand the DECODE function......therefore my question:
Is there another easy SQL statement/function I could try that Access would understand, or should I be doing something to the 'Additional Cost' column?
Thanks for your help and advice in advance.
Cheers
Nev
I have 2 tables that I'm wanting to do a query on. The first contains assetID for machines, along with the initial cost of these machines. I've then got a second table with 'additional costs' of the same machines, i.e. a machine requires a spare part, and thus has an entry in this table.
The query I'm running gets a full list of the machines from table 1, along with the initial cost of the machine, and also from table 2 the possible 'additional cost' of machines along with a final column which is the sum of the cost of the machine and the additional cost - the the query should return something like:
Asset|OrigionalCost|AdditionalCost|Total Cost
The SQL used to create this query is:
SELECT Asset.AssetID, Asset.[Original Cost], Addition.Cost, ([Original
Cost]+[Cost]) AS Closebal
FROM Asset LEFT JOIN Addition ON Asset.AssetID = Addition.[Asset ID];
This is fine as long as there is an additional cost for the machine. But as soon as there is a machine without an additional cost the final column is blank. This is obviously because the SQL is trying to add a machine value to a NULL additional cost. This would be no problem in Oracle, as I'd just do a DECODE() on the additional cost column to set all of the NULL values to 0, thus adding a machine values to 0. (which would give me the following SQL):
SELECT Asset.AssetID, Asset.[Original Cost], decode(Addition.Cost,null,'0',Addition.Cost), ([Original Cost]+[Cost]) AS Closebal
FROM Asset LEFT JOIN Addition ON Asset.AssetID = Addition.[Asset ID];
However, Access doesn't understand the DECODE function......therefore my question:
Is there another easy SQL statement/function I could try that Access would understand, or should I be doing something to the 'Additional Cost' column?
Thanks for your help and advice in advance.
Cheers
Nev