Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

When I run the following query in MS-Access:

SELECT a1.ConStateNumber, a2.ConStateNumber 
FROM qryW2_yr_sum_slash_qryW2_q4_sum_result a1 INNER JOIN 
     qryW2_q3_sum a2 ON a1.ConStateNumber = a2.ConStateNumber

I end up with values in the a2.ConStateNumber column, but only empty values in the a1.ConStateNumber column. Also this query results in 372 records.


Now I'm pretty sure that the keys are matching up and it is only displaying an empty value in a2 because when I run the following query I get more results than I do when I run the previous query...

SELECT a1.ConStateNumber, a2.ConStateNumber FROM 
qryW2_yr_sum_slash_qryW2_q4_sum_result a1 RIGHT JOIN 
qryW2_q3_sum a2 ON a1.ConStateNumber = a2.ConStateNumber

...resulting in 402 records, but still no a1.ConStateNumber

And further still when I modify the query to display only the values that have NULL values the originals that appeared in the inner join do not appear.

SELECT a1.ConStateNumber, a2.ConStateNumber 
FROM qryW2_yr_sum_slash_qryW2_q4_sum_result a1 RIGHT JOIN 
qryW2_q3_sum a2 ON a1.ConStateNumber = a2.ConStateNumber 
WHERE a1.ConStateNumber IS NULL

...and this returns 30 rows.

But I still do not understand why the values are not being displayed, there are a bunch of other queries / VIEWs that this query pulls from, so could this in some way effect the queries ability to display the a1.ConStateNumber values?

It doesn't matter that much provided the next query will be able to use the values to key off of, but I just wondered what was going on.

share|improve this question
    
Which version of MS-Access? –  ypercube May 15 '13 at 18:25
    
The numbers are consistent (372=402-30) for inner, outer and outer-with-is-null queries. The weird is the first query (if it shows different values for the two columns that are used for the join.) –  ypercube May 15 '13 at 18:28
    
What happens if you add aliases in the select? SELECT a1.ConStateNumber AS number1, a2.ConStateNumber AS number2 ... –  ypercube May 15 '13 at 18:30
    
I had to go to a previous query that this one was pulling from and change the names of the columns there. In this query doing that didn't change anything. Thank you for your help. –  leeand00 May 15 '13 at 20:04
    
If these are not table but other queries, perhaps you can add that code in the question. –  ypercube May 15 '13 at 20:06
show 1 more comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.