blank query results convert to 0

[rccola24]

I have an access DB that contains data for a full Calendar Year. The data comes from another source which is converted in excel, and then imported into the access DB. There are 5 tables and 5 source files that follow this process. After importing the necessary files, the next step is to update the tables with a "0" whereever there it is null in the tables. It seems that the update query works just fine.

However, when I go to the individual query to retract the data that is imported, I get no data. Here is an example of what happens.

Lets say that my DB contains data from January - November, but not december. Once the update query is ran, the december data should be converted to 0 since I am using an update query to find null values and make them 0. But when I run the query that pulls January-December, it returns blank. If I run the query from Jan-Nov, it returns a value. What I need to do is show the 0 for december. So I need to run the query from Jan-Dec, but populate Dec with a 0 when I execute the query.

Can someone show me how to do this? Do I have so put an IIF function in the actual query? What should I do so that the query returns with the necessary values for Jan-Nov, but zeros for Dec.

Please help!!!!!! I can do a webex or something if someone is willing to show me what to do or needs

 

[Galaxiom]

Use the Nz function to convert Nulls to zero.
http://office.microsoft.com/en-us/access/HA012288901033.aspx

 

[ajetrumpet]

Use the Nz function to convert Nulls to zero.
http://office.microsoft.com/en-us/access/HA012288901033.aspx

either that, or use an IIF().

 

[rccola24]

Ok.. but what happens is I am pulling data from other queries with the same name.. here is what i mean..

I need to display the Sum Of Encounters for 4 different quarters. I have 4 different Queries for each Quarter named the following:

qry_Q1
qry_Q2
qry_Q3
qry_Q4

All of the above queries have a "SumOfEncounters" column. Each of these queries is pulled into another main query called "qry_Final"

The qry_final query contains all of the queries above. What I need to do is use the IIF or NZ function to display a 0 if there is no data for the quarter.

In the qry_final query, I pull the 4 quarter queries above and then I have for columns that display the SumOfEncounters - one for each quarter... that look like this:

SumOfEncounters_Q1: SumOfEncounters
SumOfEncounters_Q2: SumOfEncounters
SumOfEncounters_Q3: SumOfEncounters
SumOfEncounters_Q4: SumOfEncounters

I had to do it this way because of the naming convention where the name "SumOfEcnounters"..

So right now, I have data in Q1 and Q2, but I do not have data in Q3 or Q4. If I run the query as I have it above, it will return completely blank because Q3 and Q4 have no data. If I remove Q3/Q4, it will return with data because Q1/Q2 has data..

However What I need this to do is the show 0 for Q3 and Q4. So it needs to be like:

Q1 - 2000
Q2 - 300
Q3 - 0
Q4 - 0

I basically need each quarter to show, even if there is no data, i Need it to display a 0, so that the whole query works with all 4 quarters.

Any idea how I can accomplish this using the NZ or IIF function using the naming convention I currently have? My issue is with the naming convention that I have is creating difficulty for me on how I can use the IIF or NZ function.

I am available via a websession if anyone can please help.

 

[Vergy39]

Could someone please help me apply the NZ funciton to the below query? Any assistance is greatly appreciated.

SELECT DISTINCTROW [TblIssues True Pres Pending].EmployeeID, Sum([TblIssues True Pres Pending].TruePresPending) AS [Sum Of TruePresPending], Sum([TblIssues Verbal Pending].VerbalPending) AS [Sum Of VerbalPending], Sum([TblIssues CRL's Pending].[CRL'sPending]) AS [Sum Of CRL'sPending]
FROM ((Employees INNER JOIN [TblIssues Verbal Pending] ON Employees.EmployeeID = [TblIssues Verbal Pending].EmployeeID) INNER JOIN [TblIssues CRL's Pending] ON Employees.EmployeeID = [TblIssues CRL's Pending].EmployeeID) INNER JOIN [TblIssues True Pres Pending] ON Employees.EmployeeID = [TblIssues True Pres Pending].EmployeeID
GROUP BY [TblIssues True Pres Pending].EmployeeID, [TblIssues Verbal Pending].EmployeeID, [TblIssues CRL's Pending].EmployeeID;

Thanks
David V.

 

[Vergy39]

Could someone please help me apply the NZ funciton to the below query? Any assistance is greatly appreciated.

SELECT DISTINCTROW [TblIssues True Pres Pending].EmployeeID, Sum([TblIssues True Pres Pending].TruePresPending) AS [Sum Of TruePresPending], Sum([TblIssues Verbal Pending].VerbalPending) AS [Sum Of VerbalPending], Sum([TblIssues CRL's Pending].[CRL'sPending]) AS [Sum Of CRL'sPending]
FROM ((Employees INNER JOIN [TblIssues Verbal Pending] ON Employees.EmployeeID = [TblIssues Verbal Pending].EmployeeID) INNER JOIN [TblIssues CRL's Pending] ON Employees.EmployeeID = [TblIssues CRL's Pending].EmployeeID) INNER JOIN [TblIssues True Pres Pending] ON Employees.EmployeeID = [TblIssues True Pres Pending].EmployeeID
GROUP BY [TblIssues True Pres Pending].EmployeeID, [TblIssues Verbal Pending].EmployeeID, [TblIssues CRL's Pending].EmployeeID;

Thanks
David V.

Please? I have tried everything to make this work, but unsuccessful. I am trying to create queries to pull total pending, but need all associates to be listed. The query i have only pulls the associate if they have a pending #. I need for the associate to appear with 0 pending. I am not sure of the NZ function will work in my case, because if the associate does not receive any items for that day, there is not entry or record. Any assistance is greaty apprreciated.

Thanks
David V.

 

[Brianwarnock]

David if I were you I would have started a new thread rather than jump on the back of an old thread that looks as if it has been abandoned.

I think that you need to look at your joins as i suspect that you need outer joins, probably a left join selecting all employees from the employees table, then apply NZ to the fields selected from the pending tables.

Brian

 

[editolis]

Create a module and put this function:

'======================================
Function NullToZero(varValue As Variant) As Variant

If IsNull(varValue) Then
NullToZero = 0
Else
NullToZero = varValue
End If
'==========================================
End Function

Then on the column of your qry use it like this:

SumOfEncounters_Q1: NullToZero([SumOfEncounters])

etc...

Thats it. Now you have 0.

 

[Galaxiom]

Create a module and put this function:
Function NullToZero(varValue As Variant) As Variant

The function described is the Nz function and has been part of Access for a very long time.

 

[Vergy39]

Create a module and put this function:

'======================================
Function NullToZero(varValue As Variant) As Variant

If IsNull(varValue) Then
NullToZero = 0
Else
NullToZero = varValue
End If
'==========================================
End Function

Then on the column of your qry use it like this:

SumOfEncounters_Q1: NullToZero([SumOfEncounters])

etc...

Thats it. Now you have 0.

Thank you for the assistance. I tried to enter this into the query, but not successful. Probably doing something wrong. here is a screen print of the query. Can you assist in where to enter this information. Do I need to change the "SumofEncounters" to "TruePresPending"?

Thanks
David V

 

[editolis]

Can you post a database with the apropriate tables and the qry?

Your issue is simple to found a solution.

 

[Brianwarnock]

That is not the Design grid of the SQL we were looking at earlier.

You do not need the criteria under the EmployeeIds as that is what is enforced by the join.

As Glaxiom has pointed out the NZ function gives you what Editolis's function is doing.

 

[Vergy39]

Can you post a database with the apropriate tables and the qry?

Your issue is simple to found a solution.

Here is a copy of the database.

Thanks
David V

 

[Brianwarnock]

I believe that you now have 2 threads on this issue which is confusing, however as you have ignored my other posts I don't suppose that you will change this situation.

Brian

 

[editolis]

Check your DB and inform if i have fix your problem and this is your solution.

 

[Vergy39]

Check your DB and inform if i have fix your problem and this is your solution.

Wow, you are incredibly close. I just need the records where the CompletedDate is Null. What your qry1 is returing is way too many records. I had this problem as well when I combined the query's. it would double and sometimes quadruple the number of items they actually had. Attached are the actual numbers the query should return. Right now, I have 3 diff query's to get this result. And these results are incomplete since it is missing the associates that do not have any pending items. I need them to be listed and showing zero. Hope this helps you help me.

 

[Vergy39]

I believe that you now have 2 threads on this issue which is confusing, however as you have ignored my other posts I don't suppose that you will change this situation.

Brian

Brian, Please do not think that I am ignoring you. I appreciate all help as this is driving me crazy. I want to learn this and I am taking classes, but the class right now is for beginners. I have a book also, but is confusing. And yes, I do have 2 threads. Someone suggested that I start a new one instead of using this "dead" thread. Anyway, I appreciate your responses and take all suggestions I can get.

Thanks
David V.

 

[Brianwarnock]

It was I who suggested that you start a new thread , but I had intended that you say so on here or delete the posts.
Never mind.
What you are trying to do is simple but will effectively take 7 queries to produce a single output, 2 for each issue type and a Union query to put them together, this is because of the Min date per Issuetype requirement.

BTW the totals off the DB posted earlier are as below not as per the spreadsheet

EmployeeID	LastName	MinOfIssueDate	truepres	verbal	crl
1	Gonzalez	01/12/2009			4	0	5
2	Montoya		15/12/2009			0	0	3
3	Wade		07/12/2009			1	2	2
4	Dillon		19/11/2009			1	0	7
5	Vergara						0	0	0
6	Taylor-Little Elk	16/12/2009		0	0	8
7	Wilmoth		15/12/2009			0	1	6
8	Bradley		15/12/2009			0	1	8
9	Williams	17/12/2009	            2	0	5
10	Judd		08/12/2009	            3	0	5
11	Bryden		09/12/2009	            1	0	5
12	Phillips	10/12/2009		2	0	6
13	Wolters		11/12/2009		0	2	7
14	Howard		14/12/2009		1	0	3
15	Wrobel		08/12/2009		2	0	3
16	Other		17/12/2009		0	2	0

This came from this query

SELECT Employees.EmployeeID, Employees.LastName, Min(TblIssues.IssueDate) AS MinOfIssueDate, Sum(IIf([issuetype]="1",1,0)) AS truepres, Sum(IIf([issuetype]="2",1,0)) AS verbal, Sum(IIf([issuetype]="3",1,0)) AS crl
FROM Employees LEFT JOIN TblIssues ON Employees.EmployeeID=TblIssues.EmployeeID
WHERE (((TblIssues.CompletedDate) Is Null))
GROUP BY Employees.EmployeeID, Employees.LastName;

sorry for the messy layout will try again later

Tried and failed to get it right

Brian

 

[Vergy39]

Thanks Brian. Hope you had a wonderful New Year. I tried to use the query you provided, but keep getting the "Data type mismatch in criteria expression" error. I changed the MinIssueDate to OldestDate since that is what it is called in my DB. Still get the same error. I am attaching an updated version of the db since several changes have been made since we have to track BBB's now. Also, you will see several queries. I am trying to put some together so will work into the report. I am also attaching a copy of the "Old" excel report. This is what I want to create in access. Not sure if it is possble or not. Again thank you for your assistance and any suggestions or help is welcomed.

Thanks
David V

 

[Brianwarnock]

The issuetype appears to have changed from text to numeric I had to use "1" but now its 1.

The spreadsheet has totally different data to the Db

You continue to use unnecessary criteria in the pending queries.

Look at BJWqrytruepresinfo and its associates and see if its going in the right direction, it uses a 3 of your queries tho 2 modified.

brian