Need employee ID to show up even if no records.

[Teresae123]

I have an error database that I'm attempting to build. I need the results of the query to show User ID when there are no errors during a defined time frame

 

[Teresae123]

seq so far is:

SELECT [a USER ID and Name].[Sterling User ID], [a USER ID and Name].[First Name], [a USER ID and Name].[Last Name], Nz(Count(QVCENT_TASK_DETAIL.SRC_TASK_SEQUENCE_NBR),0+0) AS CountOfSRC_TASK_SEQUENCE_NBR, QVCENT_TASK_HEADER.TASK_DESCRIPTION_TXT, QVCENT_TASK_HEADER.MODIFIED_DATETIME INTO [1 TASKS COUNT]
FROM [a USER ID and Name] INNER JOIN (QVCENT_TASK_HEADER INNER JOIN QVCENT_TASK_DETAIL ON QVCENT_TASK_HEADER.SRC_TASK_ID = QVCENT_TASK_DETAIL.SRC_TASK_ID) ON [a USER ID and Name].[Sterling User ID] = QVCENT_TASK_HEADER.USER_ID
GROUP BY [a USER ID and Name].[Sterling User ID], [a USER ID and Name].[First Name], [a USER ID and Name].[Last Name], QVCENT_TASK_HEADER.TASK_DESCRIPTION_TXT, QVCENT_TASK_HEADER.MODIFIED_DATETIME, QVCENT_TASK_HEADER.WAREHOUSE_NBR
HAVING ((([a USER ID and Name].[Sterling User ID])=[User ID]) AND ((QVCENT_TASK_HEADER.MODIFIED_DATETIME) Between [START DATE] And [END DATE]) AND ((QVCENT_TASK_HEADER.WAREHOUSE_NBR)="0150"));

 

[sjd]

I think you need to specify a LEFT OUTER JOIN, instead of an INNER JOIN.

A good guide for the joins: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

I use that all the time =)

 

[Teresae123]

Thanks sjd.... i am new to this. Will this also give me the zero in the task count if none are present. If so looking over the link that you sent me is still confusing. Please excuse me if i ask too many questions

 

[sjd]

SELECT [a USER ID and Name].[Sterling User ID], [a USER ID and Name].[First Name], [a USER ID and Name].[Last Name], Nz(Count(QVCENT_TASK_DETAIL.SRC_TASK_SEQUENCE_NBR) ,0+0) AS CountOfSRC_TASK_SEQUENCE_NBR, QVCENT_TASK_HEADER.TASK_DESCRIPTION_TXT, QVCENT_TASK_HEADER.MODIFIED_DATETIME INTO [1 TASKS COUNT]
FROM [a USER ID and Name] INNER JOIN (QVCENT_TASK_HEADER INNER JOIN QVCENT_TASK_DETAIL ON QVCENT_TASK_HEADER.SRC_TASK_ID = QVCENT_TASK_DETAIL.SRC_TASK_ID) ON [a USER ID and Name].[Sterling User ID] = QVCENT_TASK_HEADER.USER_ID
GROUP BY [a USER ID and Name].[Sterling User ID], [a USER ID and Name].[First Name], [a USER ID and Name].[Last Name], QVCENT_TASK_HEADER.TASK_DESCRIPTION_TXT, QVCENT_TASK_HEADER.MODIFIED_DATETIME, QVCENT_TASK_HEADER.WAREHOUSE_NBR
HAVING ((([a USER ID and Name].[Sterling User ID])=[User ID]) AND ((QVCENT_TASK_HEADER.MODIFIED_DATETIME) Between [START DATE] And [END DATE]) AND ((QVCENT_TASK_HEADER.WAREHOUSE_NBR)="0150"));

I think you will need to change the first JOIN clause to a LEFT OUTER JOIN. I would copy the query, then try it with the new join and see how it works. It should, show all [a USER ID and Name].[Sterling User ID], and null entries for missing [QVCENT_TASK_HEADER.USER_ID].

You might have to alter the count to have a WHERE [QVCENT_TASK_HEADER.USER_ID] is not null. To make sure it does not count the empty/missing values. But I would try it out first, and then add a where clause.

 

[Teresae123]

changing the 1st Inner join to left outer join gives error

"JOIN expression not supported"

 

[sjd]

I've run into that before. According to MS, the join can have different outcomes based on the order in which they are joined. Or, the ON clause is insufficient and should have/be in a where clause instead.

Reference: http://office.microsoft.com/en-us/access-help/HV080760616.aspx

I'm not sure if you tried to manually edit the query, or use design view to adjust the relationship. I would give the latter a try. Design view might adjust the code to correct the JOIN or ON.

Otherwise, I am not too sure. Without know more about the table relationships. Complex queries are fun sometimes.

 

[Teresae123]

I've tried everything i know of... for 2 weeks. ready to give up

 

[sjd]

Do you have a sample database? (Or some schema data, what relationships are present)
Do you have some sample output?

Mostly the last bit is confusing to me, "QVCENT_TASK_HEADER.MODIFIED_DATETIME INTO [1 TASKS COUNT]".

I remember when I was trying something complex, an assembly price per unit, I ended up having to make several queries, one building off another to get what I wanted working. In your case, it might be easier to create a view of the tasks first, then a view of that view with the count/grouping data. If that makes sense.

Also note, if this is for a report. You may not even want to have the grouping in the query, as the report will redo the grouping again anyways. (Similarly, counts/aggregation functions may be handled directly in the report, depending on how you want to do things.)

 

[Teresae123]

This will be for a report. Basically i have a datawarehouse table and and error table that i'm trying to link. Some days the employees are off so no tasks would be completed for the day. I'll need to calculate the accuracy based on how many errors and how many tasks completed in a time frame. I have several queries to do this. But if no errors during this time, i can't get the user ID nor First or last name to come up. Will i need the zero to populate to get the report to calculate? The same for tasks. Pulling the task is a little different because there are sequals for some tasks. In those cases, i have to count the seq, then sum to get correct total.