query null issue

[Johnny Drama]

I'm not sure why this happens, but:

I have a query pulling data from two tables: tbl_audit and tbl_exceptions

I created a named tbl_status with the fields statusID and status, added a statusID field to tbl_exceptions and created a relationship between the two. I then added tbl_status to the query and **poof** no data is pulled into the query.

Adding data to the statusID field in the exceptions table will then cause the query to populate with those records that have data int he status field. If I set the criteria in the query to "Is Null" it still won't return anything.

My problem is this: I have some historical data that I do not have a status for and will not be able to populate that field for the older records, but need to populate it on a go forward basis while still being able to pull the historical data.

Any thoughts?

Thanks in advance.

 

[pbaldy]

Try changing the join between those 2 tables to a LEFT JOIN instead of INNER JOIN. In design view, right-click on the join line to edit and change the selection as appropriate.

 

[rainman89]

try doing a left join. ie selecting all records from the tbl_exceptions and only those where it matches from the tbl_status

 

[rainman89]

Try changing the join between those 2 tables to a LEFT JOIN instead of INNER JOIN. In design view, right-click on the join line to edit and change the selection as appropriate.

beat me again!

 

[pbaldy]

It's the higher altitude here. Less air resistance means my fingers can type faster.

 

[Johnny Drama]

I used rainman89's suggestion and it fixed the problem. Can either of you tell me what I just did in theory so I can better understand and stop asking so many damn questions?

Thanks!

 

[pbaldy]

Edit the join in design view like I suggested, which will give you descriptions of the options, then look at what the result was in SQL view. That will help you associate the SQL term with what it does.