Query question

[aoswald]

I need to pull names from a list to report leave taken but I need to list all the names on the report whether they have taken any leave or not

any suggestions?

 

[WayneRyan]

A,

Without knowing how your database is structured, this is just
a guess:

Select A.EmployeeName, Nz(B.TotalLeave, 0)
From   tblEmployees As A Left Join tblLeave As B On
         A.Employeeid = B.EmployeeID

The Left Join will give you all employees, the Nz funciton will provide a
value of 0 when no leave is taken.

Wayne

 

[aoswald]

I have the employees in one table and the leave they have taken in another table. The query returns their name and the date and type of leave taken.

but now the boss wants to see everybody's name on the report whether they have taken leave or not.

 

[neileg]

That's what Wayne explained how to do.

 

[aoswald]

so do i put Select A.EmployeeName, Nz(B.TotalLeave, 0) in the critera field?

 

[aoswald]

ok I have the Contacts table on the left and the Leave table on the right. In the Last Name field I have this expression

[Contacts]![LastName] , Nz ( [Leave]![Pay Period] ,0)

I get a syntax error

 

[WayneRyan]

aos,

Post a sample, it's easier that way.

Wayne

 

[aoswald]

I tried to upload a screenshot of the design view but the file is too big. I can't upload the db because there is sensitive information in it

 

[namliam]

You want the NZ not in the same field as the last name... as a seperate column

 

[aoswald]

Using this suggestion from Wayne Ryan:

Select A.EmployeeName, Nz(B.TotalLeave, 0)
From tblEmployees As A Left Join tblLeave As B On
A.Employeeid = B.EmployeeID

I put [Contacts]![FirstName] ,Nz( [Leave]![Hours] ,0)

Contacts.File_Number = Leave.FileNumber


I get an invalid syntax error

 

[namliam]

So you have a table called Leave and a table called Contacts...

Have you tried replacing the , by ; ? That is a small difference in some versions/regional settings.

Note:
It is considered good practice to prefix any objects in your database as to what they are. tables get tbl, queries qry, etc.
tblContacts
tblLeave
Probably to late to change it in this project, but something to keep in mind.

 

[aoswald]

That is correct - a table called Leave and a table called Contacts

That's a good idea about labeling with tbl prefix. I'll do that on my next project as soon as I get this one to work

changing the comma to a semicolon didn't work.

 

[namliam]

What does your FULL sql look like?

 

[aoswald]

I didn't think of looking at the sql view. Here it is:

SELECT Contacts.FirstName, Contacts.LastName, Leave.Type, Contacts.File_Number, Leave.Hours, Leave.DateUsed, Contacts.PRCode, TimePeriods.PayPeriod, Leave.DateUsed, Leave.Ending, [Contacts]![LastName].Nz([Leave]![Hours],0) AS Expr1
FROM TimePeriods INNER JOIN (Contacts INNER JOIN Leave ON Contacts.File_Number = Leave.File_Number) ON TimePeriods.PayPeriod = Leave.[Pay Period]
WHERE (((Contacts.PRCode) Like "013h*") AND ((TimePeriods.PayPeriod) Like "11/16*") AND ((Contacts.[Termination Date]) Is Null));

 

[neileg]

This bit

[Contacts]![LastName].Nz([Leave]![Hours],0)

should just be

Nz([Leave]![Hours],0)

 

[aoswald]

OK that got rid of the syntax error but I'm still not getting the results I need from the query

the leave table has the hours people have taken in each time period. The contact table has everybodys name in it. Manangement wants everybody's name on the report whether they have taken leave for a particular time period or not. I have been running the query for each time period and reporting only those hour taken. Now they want to see everybodys name.

cant get it to work

 

[namliam]

Yes . to , or remove the lastname thing all together
Funny that in your post above it is right...

I put [Contacts]![FirstName] ,Nz( [Leave]![Hours] ,0)

 

[namliam]

but I'm still not getting the results I need from the query

Select A.EmployeeName, Nz(B.TotalLeave, 0)
From   tblEmployees As A Left Join tblLeave As B On
         A.Employeeid = B.EmployeeID

FROM TimePeriods INNER JOIN (Contacts INNER JOIN Leave ON

 

[aoswald]

Ok - looks like I am getting some names with a 0 in the hours taken column but I need to narrow it down to a specific pay period. When I do that, I don't get the names with zero hours anymore

 

[namliam]

Did you change both inner to left?

Full SQL?