Access Report Problem

[kg]

I have limited experience in Access. I have developed databases for my own
use with simple expressions/queries. However I am having the following
problem.

I have an Employee database. One table [Employee Information] has all
pertinent employee information. Another table [Attendance Tracking] keeps
track of their daily schedules. I am trying to develop a new report that
lists all employees and lists an absence code, if any, from the [Attendance
Tracking] table for a specific date. So far, I can only get those employees
with a record in [Attendance Tracking] to show up. I would like the report
to look like this:

Daily Schedule for 10/1/08

Emp. No. Emp. Name Status Active/Inactive
0001 John Smith Vacation Inactive
0002 Jane Doe Active

 

[KARL DEWEY]

In design view of the query for report source click on the connecting line
between the two tables. Double click and in the new window select the option
that shows all records from employee table and only those in the attendance
table that match.

 

[kg]

I had tried this prior to my post and it does not work. It still only shows
the employees who have records in the [Attendance Tracking] table for a
specific date.
--
kg

In design view of the query for report source click on the connecting line
between the two tables. Double click and in the new window select the option
that shows all records from employee table and only those in the attendance
table that match.
--
KARL DEWEY
Build a little - Test a little

I have limited experience in Access. I have developed databases for my own
use with simple expressions/queries. However I am having the following
problem.

I have an Employee database. One table [Employee Information] has all
pertinent employee information. Another table [Attendance Tracking] keeps
track of their daily schedules. I am trying to develop a new report that
lists all employees and lists an absence code, if any, from the [Attendance
Tracking] table for a specific date. So far, I can only get those employees
with a record in [Attendance Tracking] to show up. I would like the report
to look like this:

Daily Schedule for 10/1/08

Emp. No. Emp. Name Status Active/Inactive
0001 John Smith Vacation Inactive
0002 Jane Doe Active

 

[John Spencer]

The problem is that you are applying criteria to the Attendance Tracking table
in your query. That effectively negates the left join.

You will need to "stack" your queries.

QueryOne:
Just query Attendance tracking for the specified date
SELECT *
FROM [Attendance Tracking]
WHERE SomeDateField = CDate([Enter Date])

Then use that query in another query along with Employee Information

SELECT [Employee Information].[Emp. No.], [Emp. Name]
, Status, [Active/Inactive]
FROM [Employee Information] LEFT JOIN QueryOne
ON [Employee Information].[Emp. No.] = QueryOne..[Emp. No.]

By the way, if your field names and tables names consisted of only Letters,
Numbers, and the underscore character you could do this all in ONE query.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[KARL DEWEY]

The left join will work if you add to the criteria like this --
WHERE SomeDateField = CDate([Enter Date]) OR SomeDateField Is Null

--
KARL DEWEY
Build a little - Test a little

The problem is that you are applying criteria to the Attendance Tracking table
in your query. That effectively negates the left join.

You will need to "stack" your queries.

QueryOne:
Just query Attendance tracking for the specified date
SELECT *
FROM [Attendance Tracking]
WHERE SomeDateField = CDate([Enter Date])

Then use that query in another query along with Employee Information

SELECT [Employee Information].[Emp. No.], [Emp. Name]
, Status, [Active/Inactive]
FROM [Employee Information] LEFT JOIN QueryOne
ON [Employee Information].[Emp. No.] = QueryOne..[Emp. No.]

By the way, if your field names and tables names consisted of only Letters,
Numbers, and the underscore character you could do this all in ONE query.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I had tried this prior to my post and it does not work. It still only shows
the employees who have records in the [Attendance Tracking] table for a
specific date.

 

[John Spencer]

I think it won't because you are going to have other records in the Attendance
Tracking table for the individual where the date is not null. Adding
SomeDateField Is Null only works when there are NO records in the joined
subordinate table.

Another way to handle this if you only need ONE field from Attendance Tracking
table would be to use DLookup or a subquery in the select clause and not
include Attendance Tracking table in the tables at all.

SELECT [Employee Information].*
, (SELECT First(Status) FROM [Attendance Tracking]as A WHERE A.WorkDateField =
CDate([Enter Date:]) AND A.[Emp. No.] = [Employee Information].[Emp. No.]) as
StatusToday
FROM [Employee Information]

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[kg]

This solution worked. Thank you very much.
--
kg

The problem is that you are applying criteria to the Attendance Tracking table
in your query. That effectively negates the left join.

You will need to "stack" your queries.

QueryOne:
Just query Attendance tracking for the specified date
SELECT *
FROM [Attendance Tracking]
WHERE SomeDateField = CDate([Enter Date])

Then use that query in another query along with Employee Information

SELECT [Employee Information].[Emp. No.], [Emp. Name]
, Status, [Active/Inactive]
FROM [Employee Information] LEFT JOIN QueryOne
ON [Employee Information].[Emp. No.] = QueryOne..[Emp. No.]

By the way, if your field names and tables names consisted of only Letters,
Numbers, and the underscore character you could do this all in ONE query.


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

I had tried this prior to my post and it does not work. It still only shows
the employees who have records in the [Attendance Tracking] table for a
specific date.