I am trying to return 0 if a value doesn't exist. Can someone please give me a hand.

SELECT Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, Project.PercentComp, Project.LastPercentComp, nz(Sum([Hours]),0) AS TotalHours
FROM Project LEFT JOIN TimeFile ON (Project.Project_Component = TimeFile.Component) AND (Project.Project_Area = TimeFile.Area) AND (Project.Project_Name = TimeFile.Project)
WHERE (((TimeFile.Date) Between #10/1/2008# And #10/8/2008#))
GROUP BY Project.Project_Name, Project.Project_Area, Project.Project_Component, Project.Allowable_Time, Project.PercentComp, Project.LastPercentComp
HAVING (((Project.Project_Name)="HALL-HOUSTON MI560 L"));


The above Query returns only the records that can return Sum(Hours). I would like to see all of the Components regardless of the Sum(Hours). Hope that I explained this well enough.

Thanks in Advance!

I'm thinking in your WHERE clause, for the TimeFile.Date criteria, you'll need your BETWEEN statement and also include nulls.

Something like: WHERE (TimeFile.Date Between #10/1/2008# And #10/8/2008#) OR (TimeFile.Date IS NULL)

Since you are explicitly asking for populated data from a field in the TimeFile table, it'll only give you back populated data that meet that criteria. If you want to also include nulls as part of that LEFT OUTER JOIN, then you'll have to also include it in your WHERE clause.

Hope that helps!
Kael

Thanks for the quick reply. I am still learning. I did what you suggested. It did return some Null values, but not all of them. I have a table named Project which has all of the Project data that I want to see. The timefile table has all of the charged hours, etc. Do I need to do query the timefile data instead of the project data?

If I understand you correctly...I guess it depends on what data you are ultimately interested in. If you can only get to the timefile data you need from the project table, then you'll have to include that in your query.

I'm not sure why you are still missing nulls. From the example query, are there null timefile records for project name = "HALL-HOUSTON MI560 L"? that you know are missing?

Also, do you think you have any empty string values? If so, you'll probably need to include that as well:

WHERE (TimeFile.Date Between #10/1/2008# And #10/8/2008#) OR (TimeFile.Date IS NULL) OR (TimeFile.Date = "")

When I used your above suggestion i got a data type mismatch error. I do realize now that Project, Area, Component will not be in the timefile if the time isn't charged. Let me explain a little more:

Project Table has the following fields:
Project_Name
Project_Area
Project_Component
Allowable_Time
PercentComp
LastPercentComp

TimeFile has the following fields:
Project
Component
Area
Hours
Date

I need the following to show up from the query:

Project, Area, Component, Allowable_Time, PercentComp, LastPercentComp, SumOfHours(Between two Dates)

Project, Area, Component may not exist in TimeFile between the two dates. I do however need to see them with the null values.

I apologize for the mess, your help is greatly appreciated.

Thanks again

Oh, I see. I think the problem is on the three fields that comprise the join with regards to nulls.

You might not be able to link via the typical LEFT OUTER JOIN since nulls are likely being suppressed. You'll probably have to perform what I call an "equi-join", that is...joining fields in a WHERE clause. Doing the joins in a WHERE clause gives you the ability to create expressions to join on if needed.

Now that I think about it...if you are wanting null records if the Project, Area, or Component are null, then you may want to change which table you get those three fields from. Instead of the project table, select the records from the timefile table instead.

See if that gets you closer...i'll put more thoughts into it after lunch!

Thanks, I will mess around with that. Please post back when you have some time.

Had another thought -- if you are wanting records from the TimeFile table where Project, Area, or Component might be null, then you may want to change the direction of your join -- selecting all records from the TimeFile table and only those records from the Project table where the fields are equal.

I know it sounds weird, but if you have records in the TimeFile table but not in the Project table that you *want* in the query, then you'll have to do it a little backwards.

SELECT TimeFile.Project, TimeFile.Area, TimeFile.Component, Project.Allowable_time, Project.PercentComp, Project.LastPercentComp, Sum(TimeFile.Hours) AS TotalHours
FROM TimeFile LEFT JOIN Project ON (TimeFile.Project = Project.Project_Name) AND (TimeFile.Component = Project.Project_Component) AND (TimeFile.Area = Project.Project_Area)
WHERE (((TimeFile.Date) Between #10/1/2008# And #10/8/2008# Or (TimeFile.Date) Is Null))
GROUP BY TimeFile.Project, TimeFile.Area, TimeFile.Component, Project.Allowable_time, Project.PercentComp, Project.LastPercentComp
HAVING (((TimeFile.Project)="HALL-HOUSTON MI560 L"));

What happens when you run the query above?

it doesn't return any null values.

Post deleted by jholokai02

I think that ZIP file is empty...

it is, i'm trying to get it smaller so that i can upload it. sorry

lets try again, sorry.

How about this (see attachment)

I think I see the problem -- you're wanting to see all projects, but then if there are any time entries, you want to sum those up.

It's really tricky because of the multiple joins. There might be a cleaner way, but I ended up breaking it up into two queries.

The subquery first identifies the records that match between the two tables (where there are time records) and performs the sum. This is just a base query to perform one step of the calculation. Then, we join that subquery back to the projects table, and perform an OUTER JOIN to identify all projects and peform an NZ on the TotalHours if it wasn't found.

Is this more of what you were looking for?

Great!!! Thanks for the help. That is what I need. I really appreciate all of the help.

You're welcome! Whew! That was a really tricky one -- need one of these after all that! >> o!

I hear that, i've been working on this all day.
Again, thanks for all the help.

uhhhh, just something to think about. when i bring those queries into the actual database, I am getting the original data that we were trying to fix. basically, its not working. what could be the problem with this? if you could give this some thought, it would be greatly appreciated. i am heading home for the day but will be at it again tomorrow. thanks again!!!!