Need to display all names, even those with no entries

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

DANANDLEA@ADELPHIA.NET - 29 Jan 2007 17:45 GMT

I have created a small database to track overtime. I have a table set
up for employee information, one for entry of overtime (which happens
via a form). I need to create a report that lists all employees by
shift, regardless if they have worked overtime or not, but include the
sum of overtime for those that have worked. I can currently only
query those that have worked - any way around this?

Reply to this Message

Klatuu - 29 Jan 2007 18:16 GMT

Post the SQL of your query so we can see what it is doing now.

Signature

Dave Hargis, Microsoft Access MVP

> I have created a small database to track overtime. I have a table set
> up for employee information, one for entry of overtime (which happens
> via a form). I need to create a report that lists all employees by
> shift, regardless if they have worked overtime or not, but include the
> sum of overtime for those that have worked. I can currently only
> query those that have worked - any way around this?

Reply to this Message

obriend - 29 Jan 2007 18:40 GMT

SELECT Sum([TIME WORKED].[HOURS WORKED]) AS [SumOfHOURS WORKED],
[Employee list].NAME
FROM [Employee list] INNER JOIN [TIME WORKED] ON [Employee list].ID =
[TIME WORKED].ID
GROUP BY [Employee list].NAME
ORDER BY Sum([TIME WORKED].[HOURS WORKED]);

> Post the SQL of your query so we can see what it is doing now.
> --
[quoted text clipped - 7 lines]
> > sum of overtime for those that have worked. I can currently only
> > query those that have worked - any way around this?- Hide quoted text -- Show quoted text -

Reply to this Message

Klatuu - 29 Jan 2007 19:19 GMT

Change your INNER JOIN to a LEFT JOIN

Signature

Dave Hargis, Microsoft Access MVP

> SELECT Sum([TIME WORKED].[HOURS WORKED]) AS [SumOfHOURS WORKED],
> [Employee list].NAME
[quoted text clipped - 14 lines]
> > > sum of overtime for those that have worked. I can currently only
> > > query those that have worked - any way around this?- Hide quoted text -- Show quoted text -

Reply to this Message

obriend - 29 Jan 2007 19:27 GMT

THANK YOU VERY MUCH! THAT WAS SUCH A SIMPLE FIX

> Change your INNER JOIN to a LEFT JOIN
> --
[quoted text clipped - 18 lines]
> > > > sum of overtime for those that have worked. I can currently only
> > > > query those that have worked - any way around this?- Hide quoted text -- Show quoted text -- Hide quoted text -- Show quoted text -

Reply to this Message