G
Guest
ok i set up a database that uses info from an oracle server thru ms sql. the
output data is in the form of excel sheets and are linked to an access
database (linked tables). the problem i designed a query using these linked
tables to give an account of customers and their balances for five different
companies. There is a table with all the customer names and the rest has a
joint property to display the balances with any matching names in that table.
So we have a datasheet from the query that has six fields (the customer names
and the five matching balances) however not all the balances would match with
all the names, hence i have alot of null or blank rows in the query/datasheet
preview. the problem is i've also created a form that shows all this
information along with a formula that calculates the total for all five
balances. However because some of the balances might be a null value that
correspond to a specific customer name, the formula doesn't work. it only
works if all five balances have values. so i need to know how i can change
those null values to zeros so as to get the total balances working (again i'm
working with linked tables) so firstly is their any restriction in achieving
this and if ther is how can i get pass it. Secondly i'm desperate and need
any help i can get, i have deadlines to meet with this database so i need
help asap.
to give you an idea of what the query looks like below is a copy of the sql
script for the query, it's simple but the null to zero thing is tricky.
SELECT [CUSTOMER NAMES].NAME, [AINLIM AR DATABASE].BALANCE1, [CSS AR
BALANCE].BALANCE2, [DOCOL AR BALANCE].BALANCE3, [GEDDES GRANT AR
BALANCE].BALANCE4, [NMSL AR BALANCE].BALANCE5
FROM (((([CUSTOMER NAMES] LEFT JOIN [AINLIM AR DATABASE] ON [CUSTOMER
NAMES].NAME = [AINLIM AR DATABASE].NAME) LEFT JOIN [CSS AR BALANCE] ON
[CUSTOMER NAMES].NAME = [CSS AR BALANCE].NAME) LEFT JOIN [DOCOL AR BALANCE]
ON [CUSTOMER NAMES].NAME = [DOCOL AR BALANCE].NAME) LEFT JOIN [GEDDES GRANT
AR BALANCE] ON [CUSTOMER NAMES].NAME = [GEDDES GRANT AR BALANCE].NAME) LEFT
JOIN [NMSL AR BALANCE] ON [CUSTOMER NAMES].NAME = [NMSL AR BALANCE].NAME
thank you for any help i can get
kevin
output data is in the form of excel sheets and are linked to an access
database (linked tables). the problem i designed a query using these linked
tables to give an account of customers and their balances for five different
companies. There is a table with all the customer names and the rest has a
joint property to display the balances with any matching names in that table.
So we have a datasheet from the query that has six fields (the customer names
and the five matching balances) however not all the balances would match with
all the names, hence i have alot of null or blank rows in the query/datasheet
preview. the problem is i've also created a form that shows all this
information along with a formula that calculates the total for all five
balances. However because some of the balances might be a null value that
correspond to a specific customer name, the formula doesn't work. it only
works if all five balances have values. so i need to know how i can change
those null values to zeros so as to get the total balances working (again i'm
working with linked tables) so firstly is their any restriction in achieving
this and if ther is how can i get pass it. Secondly i'm desperate and need
any help i can get, i have deadlines to meet with this database so i need
help asap.
to give you an idea of what the query looks like below is a copy of the sql
script for the query, it's simple but the null to zero thing is tricky.
SELECT [CUSTOMER NAMES].NAME, [AINLIM AR DATABASE].BALANCE1, [CSS AR
BALANCE].BALANCE2, [DOCOL AR BALANCE].BALANCE3, [GEDDES GRANT AR
BALANCE].BALANCE4, [NMSL AR BALANCE].BALANCE5
FROM (((([CUSTOMER NAMES] LEFT JOIN [AINLIM AR DATABASE] ON [CUSTOMER
NAMES].NAME = [AINLIM AR DATABASE].NAME) LEFT JOIN [CSS AR BALANCE] ON
[CUSTOMER NAMES].NAME = [CSS AR BALANCE].NAME) LEFT JOIN [DOCOL AR BALANCE]
ON [CUSTOMER NAMES].NAME = [DOCOL AR BALANCE].NAME) LEFT JOIN [GEDDES GRANT
AR BALANCE] ON [CUSTOMER NAMES].NAME = [GEDDES GRANT AR BALANCE].NAME) LEFT
JOIN [NMSL AR BALANCE] ON [CUSTOMER NAMES].NAME = [NMSL AR BALANCE].NAME
thank you for any help i can get
kevin