NZ Function

[Vergy39]

I am having a problem with a query. I need to pull a query of pending items for all associates. I created a query for each category( True Pres, Verbal, and CRL). The problem is if the associate does not have an item pending, they will not appear on the list. I need them to appear on the list with 0 pending. I searched on this site, and found some talk about an NZ function, but I do not know how to apply it to the query. Here is a copy of one of the query's in SQL view. Any assistance on where the NZ function goes would be greatly appreciated. Also, I have attached a copy of the database that I am building.

SELECT DISTINCTROW TblIssues.EmployeeID, Count(*) AS TruePresPending, Min(TblIssues.IssueDate) AS OldestDate
FROM Employees LEFT JOIN TblIssues ON Employees.EmployeeID = TblIssues.EmployeeID
WHERE (((TblIssues.CompletedDate) Is Null))
GROUP BY TblIssues.EmployeeID, Employees.EmployeeID, TblIssues.IssueType
HAVING (((TblIssues.EmployeeID)=1) AND ((Employees.EmployeeID)=1) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=2) AND ((Employees.EmployeeID)=2) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=3) AND ((Employees.EmployeeID)=3) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=4) AND ((Employees.EmployeeID)=4) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=5) AND ((Employees.EmployeeID)=5) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=6) AND ((Employees.EmployeeID)=6) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=7) AND ((Employees.EmployeeID)=7) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=8) AND ((Employees.EmployeeID)=8) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=9) AND ((Employees.EmployeeID)=9) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=10) AND ((Employees.EmployeeID)=10) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=11) AND ((Employees.EmployeeID)=11) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=12) AND ((Employees.EmployeeID)=12) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=13) AND ((Employees.EmployeeID)=13) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=14) AND ((Employees.EmployeeID)=14) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=15) AND ((Employees.EmployeeID)=15) AND ((TblIssues.IssueType)="1")) OR (((TblIssues.EmployeeID)=16) AND ((Employees.EmployeeID)=16) AND ((TblIssues.IssueType)="1"));

 

[John Big Booty]

Which form and which query are we talking about?

Also you may wish to implement a naming protocol for you DB object currently you have Tables, Queries and Forms all with names like TblSomethingOrOther. This will cause you no end of grief sooner or later.

Consider something like TBL_TableName, FRM_FormName, QRY_QueryName, RPT_ReportName. Limit yourself to alpha numeric characters and under score (_), avoid spaces and all other special characters.

 

[Vergy39]

Which form and which query are we talking about?

Also you may wish to implement a naming protocol for you DB object currently you have Tables, Queries and Forms all with names like TblSomethingOrOther. This will cause you no end of grief sooner or later.

Consider something like TBL_TableName, FRM_FormName, QRY_QueryName, RPT_ReportName. Limit yourself to alpha numeric characters and under score (_), avoid spaces and all other special characters.

Thanks John. The query name is "TblIssues True Pres pending". I attached an updated copy of the database. I had made some changes, so this is the newest version so far. I know I need to go back and rename these forms, but I am not sure if these will be the final product so just working with them now. I do appreciate your help though.

Thanks
David V.

 

[John Big Booty]

I'd rename your DB object sooner rather than later, as the longer you leave it the harder it will become to correct, as you get more and more interconnected references that you will need to hunt down and change.

 

[John Big Booty]

I'll have a look at your DB when I get a chance. Unfortunately what was looking like a nice quite day has has just turned to S.H.1.T. so I'm not sure when that will be.

 

[Vergy39]

I'll have a look at your DB when I get a chance. Unfortunately what was looking like a nice quite day has has just turned to S.H.1.T. so I'm not sure when that will be.

I certainly appreciate it. Hope your day gets better. I will be leaving shortly, but will be working tomorrow. Have a Happy New Year.

Thanks
David V>

 

[John Big Booty]

Having had a look at the DB, I'm not sure that the NZ function is going to be the answer to what you are trying to achieve, and to be honest I'm not sure how to get the result you are after, but I will think on it.

Also you might want to read this article concerning the evils of Table Level Lookups.

 

[Vergy39]

Which form and which query are we talking about?

Also you may wish to implement a naming protocol for you DB object currently you have Tables, Queries and Forms all with names like TblSomethingOrOther. This will cause you no end of grief sooner or later.

Consider something like TBL_TableName, FRM_FormName, QRY_QueryName, RPT_ReportName. Limit yourself to alpha numeric characters and under score (_), avoid spaces and all other special characters.

John, I renamed the queries as you suggested. It does make things easier to keep track of.

Thanks
David V

 

[John Big Booty]

Have a look at QRY_3, that's on the way to what you are looking for. The only way I can see to get the other employees into the query is to put some dummy data in the table that will return an "N/A" result.

 

[Vergy39]

This is close, thank you very much. I think you are right that I may need to put some dummy data in.

Thanks
David V

 

[zaze]

I'm having a similar issue... I have a database that may or may not contain a value for what I'm looking for. More specifically, its a legacy employee database where old employees have an additional ID number (iso) that I would like to include in my query result table. If they don't exist in that second database, I'd like them included anyway with 0 or NULL as the "iso". As of right now, if their EMPLID is not in the legacy database, the record does not show up in my result...

Heres my query, the table.field which I'm talking about is dbo_EMPLID_to_iso_xref.iso.

SELECT DWHCRPT_PS_OCC_EML_OSU_SVW.LAST_NAME,
DWHCRPT_PS_OCC_EML_OSU_SVW.FIRST_NAME,
DWHCRPT_PS_OCC_EML_OSU_SVW.EMAIL_ADDR, DWHCRPT_PS_STDNT_CAR_TERM.EMPLID,
dbo_EMPLID_to_iso_xref.iso, DWHCRPT_PS_STDNT_CAR_TERM.ACAD_LEVEL_BOT,
DWHCRPT_PS_OSR_PLAN_PRI.OSR_PRIMARY_PLAN, DWHCRPT_PS_OSR_ENROLL.STRM INTO
PostRosterRun
FROM (((DWHCRPT_PS_CLASS_TBL INNER JOIN DWHCRPT_PS_OSR_ENROLL ON
(DWHCRPT_PS_CLASS_TBL.CLASS_NBR = DWHCRPT_PS_OSR_ENROLL.CLASS_NBR) AND
(DWHCRPT_PS_CLASS_TBL.STRM = DWHCRPT_PS_OSR_ENROLL.STRM)) INNER JOIN
DWHCRPT_PS_STDNT_CAR_TERM ON (DWHCRPT_PS_OSR_ENROLL.ACAD_CAREER =
DWHCRPT_PS_STDNT_CAR_TERM.ACAD_CAREER) AND
(DWHCRPT_PS_OSR_ENROLL.INSTITUTION =
DWHCRPT_PS_STDNT_CAR_TERM.INSTITUTION) AND (DWHCRPT_PS_OSR_ENROLL.STRM =
DWHCRPT_PS_STDNT_CAR_TERM.STRM) AND (DWHCRPT_PS_OSR_ENROLL.EMPLID =
DWHCRPT_PS_STDNT_CAR_TERM.EMPLID)) INNER JOIN (DWHCRPT_PS_OCC_EML_OSU_SVW
INNER JOIN DWHCRPT_PS_OSR_PLAN_PRI ON DWHCRPT_PS_OCC_EML_OSU_SVW.EMPLID =
DWHCRPT_PS_OSR_PLAN_PRI.EMPLID) ON (DWHCRPT_PS_STDNT_CAR_TERM.EMPLID =
DWHCRPT_PS_OSR_PLAN_PRI.EMPLID) AND (DWHCRPT_PS_STDNT_CAR_TERM.INSTITUTION
= DWHCRPT_PS_OSR_PLAN_PRI.INSTITUTION) AND
(DWHCRPT_PS_STDNT_CAR_TERM.ACAD_CAREER =
DWHCRPT_PS_OSR_PLAN_PRI.ACAD_CAREER) AND (DWHCRPT_PS_STDNT_CAR_TERM.STRM =
DWHCRPT_PS_OSR_PLAN_PRI.STRM)) INNER JOIN dbo_EMPLID_to_iso_xref ON
DWHCRPT_PS_OCC_EML_OSU_SVW.EMPLID = dbo_EMPLID_to_iso_xref.EMPLID
WHERE (((DWHCRPT_PS_OSR_ENROLL.STRM)="1100") AND
((DWHCRPT_PS_CLASS_TBL.SUBJECT)="ISE"));


Probably confusing.. I know. Any thoughts?

 

[Brianwarnock]

Beginners, no offense meant we've all been there, often want to do everything in 1 query, but the use of cascading queries is the norm, that is were you run a query which causes others to run.

In the situation where you have a final query result which does not include all of your emploees say, then atleast 1 more query is required.

In the design grid drag in the employee table then the final query, join on employee ID right click on the join and change the join properties to a join that pulls all records from the Table, in the above scenario it will be option 2 which will in sql appear as a leftjoin.
Select the Basic employyee details from the table and the rest of the information from the query, it is now that you may be able to tackle the issue of no data in certain fields for some employees.


Brian

 

[gemma-the-husky]

in the case of the original posting - i think when you have so many "or's" it is perhaps symptomatic of some database refinement needed. maybe an additional y/n flag in one of the tables.

in this case, having to test for special cases based on the employee id - is clearly non-normalised behaviour, i would have thought.

the aim is to get queries that work irrespective of how the data is set up