Bryan, Sounds like a case for a Union query. (Excuse the explanation if you already know what one is.)
In a Union query, you run separate queries, then perform the Union of their two result sets. You can do this with or without duplicate rows. So one query returns 5, 7, 15 The other returns 5, 12, 16, 3 The Union contains 3, 5, 5 (optionally), 7, 12, 15, 16. MS Access can do Union queries, so the Help items on this might be a good learning tool. HTH. -Ben -----Original Message----- From: Langford, Bryan [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 4:36 PM To: CF-Talk Subject: RE: Dealing with nulls while adding two columns together Ben, Thanks for the suggestion. After researching this option I now have another question: will this allow me to produce the effect I am looking for because both tables can have null values. An example would be: Table1 ID Value 1 5 2 7 3 4 15 Table2 ID Value 1 2 5 3 12 4 16 5 3 So the result set I am looking for would be: 5, 12, 12, 31, 3 Thanks, Bryan Langford -----Original Message----- From: Braver, Ben [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 4:20 PM To: CF-Talk Subject: RE: Dealing with nulls while adding two columns together Bryan, 2 quick comments: 1. "NZ" is a Microsoft Access function. It's basically just an IIf saying if the field is not null, pass the contents of the field, but if the field is null, pass a zero. 2. The default join type is an "inner" join where the field exists in both tables. Look up "outer" joins where the field is optional in one of the tables. HTH. -Ben -----Original Message----- From: Langford, Bryan [mailto:[EMAIL PROTECTED]] Sent: Monday, October 01, 2001 4:10 PM To: CF-Talk Subject: Dealing with nulls while adding two columns together Hey everyone, I have two separate views that pull numeric data from a table using SQL. I am looking to add the two together in another view before bringing it back with cold fusion for display. My problem is that if either table has a null value data field where the join fields are equal, the request comes back blank. So I have tried "coalesce" and even went as far as trying an old trick I learned when I developed Access databases which was to use "NZ(table.field)+NZ(table2.field2)" but as expected, that errored out. Does anyone have some suggestion I might try to correct this problem and if so, what are they? ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Your ad could be here. Monies from ads go to support these lists and provide more resources for the community. http://www.fusionauthority.com/ads.cfm FAQ: http://www.thenetprofits.co.uk/coldfusion/faq Archives: http://www.mail-archive.com/[email protected]/ Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
