MS Access this time - stuck again :o

Whoop

Whoop

Whoop

Associate
Joined
27 Nov 2003
Posts
2,486
Location
Loughborough
Hi all,

Hopefully this makes sense :)

I have 2 tables linked together, one has 100 rows the other 80 rows.

If I link the tables together and use an ALL Records join I get 100 rows as a result with blank spaces for the remaining fields. Is it possible to get access to replace any missing records with a zero?? I've looked at Null but it seems to skip the record not replace it with anything. Here is an example -

1 5
2 5
3 <== Need it to auto insert a zero in here
4
5 5
6 5
7 5
8 5
9 5

Basically I have a calculation based on the outcome and without any value in the second table the entire math falls flat. I can't add zeros to the table so I need it to pretend it had one.

If it makes sense then please help :)


Whoop
 
kevpuk said:
Can you use "" instead of Null ?
Click to expand...

I've been looking at training courses but this seems fairly advanced, where would I put that statement :o

Here is the sql version -

SELECT informix_cmprod.cmp_product, informix_cmprod.cmp_desc, informix_cmprod.cmp_group, informix_cmprod.cmp_status, informix_cmprod.cmp_usage, informix_poprod.popr_supplier, informix_cmprod.cmp_supp, informix_poprod.popr_qcost, Freight.FreightPercent, [popr_qcost]+(([popr_qcost]/100)*[FreightPercent]) AS FreightCost, Duty.DutyPercent, [FreightCost]+(([FreightCost]/100)*[DutyPercent]) AS LandedCostInForeignMoney, informix_plsupp.plsup_currency, informix_cmprod.cmp_sellpr, [LandedCostInForeignMoney]/[Rate] AS LandedCost, [cmp_sellpr]-[LandedCost] AS GBPProfit, Currency.Rate
FROM ((((informix_cmprod INNER JOIN informix_poprod ON informix_cmprod.cmp_product = informix_poprod.popr_product) INNER JOIN informix_plsupp ON informix_poprod.popr_supplier = informix_plsupp.plsup_supplier) LEFT JOIN Freight ON informix_cmprod.cmp_product = Freight.Product) LEFT JOIN Duty ON informix_cmprod.cmp_product = Duty.Product) INNER JOIN [Currency] ON informix_plsupp.plsup_currency = Currency.Currency
ORDER BY informix_cmprod.cmp_product;

I don't know where to insert the NULL/"" statement :(


Whoop
 
Why can't you add zeros to the table? Or default that field to be zero?

If a record is inserted in with a value, it gets a value, if it doesn't it goes in as zero?

Whats the name of the field you're trying to get out as 0 if its null?

Might be able to help a bit more then
 
The table is part of a live database system and a row is only created if the field is populated within the system. There are 33000 products and only 8000 records as it stands, saving an awful lot of server space by not having a default value.

the field in question is Freight.Percent in the table Freight. :)


Whoop
 
Whoop said:
The table is part of a live database system and a row is only created if the field is populated within the system. There are 33000 products and only 8000 records as it stands, saving an awful lot of server space by not having a default value.

the field in question is Freight.Percent in the table Freight. :)


Whoop
Click to expand...

I'm an SQL monkey, rather than an Access monkey... but the two are extremely similar... I really can't see how an extra 0 rather than a null on a database that size can make that much of a difference?

Still having a play around for your solution anyway :)
 
Whoop said:
Hi, thanks for the reply and I have attached a pic to help cos I'm not even sure where I put that lol :)

http://premium1.uploadit.org/snipperdag//Access.png

The product field is linked and the FreightPercent is the column with not enough rows.

Cheers,


Whoop
Click to expand...

Ok - just tidied it up a bit so you can see:
Code: 
SELECT informix_cmprod.cmp_product, 
	informix_cmprod.cmp_desc, 
	informix_cmprod.cmp_group, 
	informix_cmprod.cmp_status, 
	informix_cmprod.cmp_usage, 
	informix_poprod.popr_supplier, 
	informix_cmprod.cmp_supp, 
	informix_poprod.popr_qcost, 
	[B]ISNULL(Freight.FreightPercent,0)[/B], 
	[popr_qcost]+(([popr_qcost]/100)*[FreightPercent]) AS FreightCost, 
	Duty.DutyPercent, 
	[FreightCost]+(([FreightCost]/100)*[DutyPercent]) AS LandedCostInForeignMoney, 
	informix_plsupp.plsup_currency, 
	informix_cmprod.cmp_sellpr, 
	[LandedCostInForeignMoney]/[Rate] AS LandedCost, 
	[cmp_sellpr]-[LandedCost] AS GBPProfit, 
	Currency.Rate
FROM ((((informix_cmprod INNER JOIN informix_poprod ON informix_cmprod.cmp_product = informix_poprod.popr_product) INNER JOIN informix_plsupp ON informix_poprod.popr_supplier = informix_plsupp.plsup_supplier) LEFT JOIN Freight ON informix_cmprod.cmp_product = Freight.Product) LEFT JOIN Duty ON informix_cmprod.cmp_product = Duty.Product) INNER JOIN [Currency] ON informix_plsupp.plsup_currency = Currency.Currency
ORDER BY informix_cmprod.cmp_product;

See how that works :)
 
Andre said:
Ok - just tidied it up a bit so you can see:
Code: 
SELECT informix_cmprod.cmp_product, 
	informix_cmprod.cmp_desc, 
	informix_cmprod.cmp_group, 
	informix_cmprod.cmp_status, 
	informix_cmprod.cmp_usage, 
	informix_poprod.popr_supplier, 
	informix_cmprod.cmp_supp, 
	informix_poprod.popr_qcost, 
	[B]ISNULL(Freight.FreightPercent,0)[/B], 
	[popr_qcost]+(([popr_qcost]/100)*[FreightPercent]) AS FreightCost, 
	Duty.DutyPercent, 
	[FreightCost]+(([FreightCost]/100)*[DutyPercent]) AS LandedCostInForeignMoney, 
	informix_plsupp.plsup_currency, 
	informix_cmprod.cmp_sellpr, 
	[LandedCostInForeignMoney]/[Rate] AS LandedCost, 
	[cmp_sellpr]-[LandedCost] AS GBPProfit, 
	Currency.Rate
FROM ((((informix_cmprod INNER JOIN informix_poprod ON informix_cmprod.cmp_product = informix_poprod.popr_product) INNER JOIN informix_plsupp ON informix_poprod.popr_supplier = informix_plsupp.plsup_supplier) LEFT JOIN Freight ON informix_cmprod.cmp_product = Freight.Product) LEFT JOIN Duty ON informix_cmprod.cmp_product = Duty.Product) INNER JOIN [Currency] ON informix_plsupp.plsup_currency = Currency.Currency
ORDER BY informix_cmprod.cmp_product;

See how that works :)
Click to expand...

It accepted the code fine but I get the following error :)

Wrong number of arguments used with function in query expression 'ISNULL(Freight.FreightPercent,0)'

Thanks in advance,


Whoop
 
You must log in or register to reply here.
Back
Top Bottom