Access SQL - How to ingore a null field

[soahc00]

Hey guys/gals,

I have database we use for financial tracking. I have the following query below. With some help from the forums I have created this query. What was a problem was that there are two tables, tracking (where mostly everything is held) and releases. I had a problem with the table storing the release1-6 field as the primary key, but again with some help from the forums I was able to use the workaround for int(). So when a user enters the release, it links up to the releases table and matches the primary key.

Right now what's causing problems is the 'INNER JOIN' Statemeny, because it works great if there is a release in the release1 field, but if there isn't the data is ignored and the query moves on, ignoring the any other relevant data that is filled in. How can tell the query that null data in the release field is ok because what I really care about is that the value from the form I'm getting is = to the value in the last name field.

Can anyone help!?!?!

THANKS a bunch!

Mark

SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day, [tracking].[hrs1] AS Hours, [tracking].[task1seg] AS Segment, [releases].[release] AS Release
FROM tracking  INNER JOIN [releases] ON Int(tracking.release1)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs1 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day,  [tracking].[hrs2] AS Hours,  [tracking].[task2seg] AS Segment,  [releases].[release] AS Release
FROM tracking  INNER JOIN [releases] ON Int(tracking.release2)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs2 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day,  [tracking].[hrs3] AS Hours,  [tracking].[task3seg] AS Segment,  [releases].[release] AS Release
FROM tracking  INNER JOIN [releases] ON Int(tracking.release3)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs3 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day,  [tracking].[hrs4] AS Hours,  [tracking].[task4seg] AS Segment,  [releases].[release] AS Release
FROM tracking  INNER JOIN [releases] ON Int(tracking.release4)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs4 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day,  [tracking].[hrs5] AS Hours,  [tracking].[task5seg] AS Segment,  [releases].[release] AS Release
FROM tracking  INNER JOIN [releases] ON Int(tracking.release5)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs5 <>0 )

UNION ALL SELECT [tracking].[LastName] AS Name, [tracking].[date] AS Day,  [tracking].[hrs6] AS Hours,  [tracking].[task6seg] AS Segment,  [releases].[release] AS Release
FROM tracking  INNER JOIN [releases] ON Int(tracking.release6)=[Releases].[ID]
WHERE ((GetLastUserNameEmployeeAct()=tracking.LastName) AND tracking.hrs6 <>0 )
ORDER BY Day;

 

[PHV]

Simply replace INNER JOIN by LEFT JOIN

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244