Null value's in queries

[BigDogTroy]

I have 3 queries, one generates a calculation for a phone extension, the other generates a calculation for another field for the same phone extension, in the third query, I am using the data from the first 2 queries to show the data side by side, but when I run the third query, if any of the calculations have a null value, it leaves the whole phone extension out. Does anyone know a way around this?

 

[boblarson]

Use the NZ function to handle nulls.

 

[BigDogTroy]

Thanks Pat - Are you reffering to using VBA? If so, I have never used it. I have used Visual Basic 6, and VB.net 2005. I am not sure how to get started. Do you know of any web sites that would help a beginner in this subject? Or, if you would like to help me yourself (I never turn down help), by giving me some pointers, or I just need a little help getting started. Once I get a feel for the flow of things, I will be fine (due to my past experience with VB).

 

[neileg]

When you join two tables in Access you have three options for that join. The default is an inner join (type 1 in Access). This returns records only if there is a match in both tables. By right clicking on the join line in the query grid, you can change the join type. Type 2 is a left join and returns all of the records in the left hand table and only those from the right that match. If there is no record on the right, you get a null value for the right hand fields but you still get the left hand data. Type 3 is a right join and is the reverse of a left.

Normally when you join tables in a query they are placed side by side usually extracting different types of data. A union query stacks the results on top of each other because the results have the same fields.

Now what Pat is suggesting is that you use a left query to get all the records from one side and a right query to get all the records from the other. You then union these together (duplicates will be eliminated automatically) to get all the possible combinations regardless of null values.