Queries returning blank lines with joined tables

[Guest]

I have looked at others having this same issue and can't make anything work
to produce all ruecords when I run the query.
The SQL query is this...
SELECT Servers.Host, Servers.ID, NIC.IPAddress, NIC.MACAddress, NIC.NICID
FROM Servers RIGHT JOIN NIC ON Servers.ID = NIC.ID
ORDER BY Servers.ID;
Basically Servers table has all records in it and a NICID in some records, I
am trying to querey all records but all blank NICID recrords are dropped. I
have seen others talk about an outer join but that isn't available to me or I
am doing something wrong. The help system references using NZ but when I try
that in the criteria it doesn't work at all for me. How do I display all
records even those with null values?

Thanks

 

[Marshall Barton]

I have looked at others having this same issue and can't make anything work
to produce all ruecords when I run the query.
The SQL query is this...
SELECT Servers.Host, Servers.ID, NIC.IPAddress, NIC.MACAddress, NIC.NICID
FROM Servers RIGHT JOIN NIC ON Servers.ID = NIC.ID
ORDER BY Servers.ID;
Basically Servers table has all records in it and a NICID in some records, I
am trying to querey all records but all blank NICID recrords are dropped. I
have seen others talk about an outer join but that isn't available to me or I
am doing something wrong. The help system references using NZ but when I try
that in the criteria it doesn't work at all for me. How do I display all
records even those with null values?

Your Right (or Left) Join is an outer join.

It sounds like you want to use INNER JOIN instead.

 

[Roger Carlson]

A RIGHT JOIN *is* an outer join. So is a LEFT JOIN. The difference is in
which one will display ALL of the record. The RIGHT JOIN will use the table
on the Right of the Join equation:
ON Servers.ID = NIC.ID
So your query is showing all of the records in NIC and related records from
Servers. You want it the other way around. Therefore, you want a LEFT
JOIN. So simply change RIGHT to LEFT, and it should work.

--
--Roger Carlson
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L