Display dual tabels

  • Thread starter Thread starter DonD
  • Start date Start date
D

DonD

I am hoping someone out there might be able to help me.

I have put together an MS Access 07 database that we use to track our
employee’s attendance. It contains two separate files/tables;

1) Roster – This contains the employee’s First, Last, MI, Emp # and a
password that the employee uses to log onto the database.
2) Log – This contains the attendance data for all of our employees. I.E.
Emp #, Clock-In Date and time

The database works well but I want to create an Access form, query or report
which monitors all of the employee’s arrival to work. I want to display the
entire roster and then compare and display the date and time of arrival for
each employee or lack thereof.

I have tried to use an Access report, and a query – but the output only
displays those employees that have arrived and not those that have yet to
report to work.

I have also tried to use an MS Form but my macro writing skills are not
quite sufficient.

Does anyone have some advice for me?

Here is a sample of what I am trying to do;

FIRST MI. LAST EMP# DATE TIME
JOHN C. DOE 11111 9/1/08 6:54 AM
MARY P. SMITH 22222
BRIAN D. COX 33333
FRED A. WILLSON 44444 9/1/08 7:03 AM
End of list

The first, Mi, last names, and employee numbers are stored in the roster
datatable. The date and time are stored in a log datatable.

Employee 22222 and 33333 have yet to arrive to work therefore no log data is
available to display.

All of my attempts result in a report that only displays employee numbers
11111 & 44444. I need to display all employees and any log data that might
exist for each employee.

Thanks in advance
 
Two query solution.

qLoggedInToday - Save a query like this one
SELECT *
FROM LOG
WHERE Log.[Date] = Date()

Now use that query and your roster table in another query.

SELECT R.First, R.MI, R.Last
Q.[Date], Q.[Time]
FROM ROSTER as R LEFT JOIN qLoggedInToday as Q
ON R.[Emp #] = Q.[Emp #]

In Design view
-- Open a new query
-- Select the Log table
-- Add your fields
-- Set criteria for the date field to
=Date()
(by the way Date is a bad name for a field, since Date is also a function
returning the current system date)
-- Save the query as qLoggedInToday

Now
-- Open a new query
-- Select Roster from the tables and qLoggedInToday from the queries
-- Drag from Emp # to Emp #
-- Double-click the relationship line
-- Choose ALL from Roster and only matching from qLoggedInToday
-- Select the desired fields.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
Thank you...That did the trick.
--
DonD


John Spencer said:
Two query solution.

qLoggedInToday - Save a query like this one
SELECT *
FROM LOG
WHERE Log.[Date] = Date()

Now use that query and your roster table in another query.

SELECT R.First, R.MI, R.Last
Q.[Date], Q.[Time]
FROM ROSTER as R LEFT JOIN qLoggedInToday as Q
ON R.[Emp #] = Q.[Emp #]

In Design view
-- Open a new query
-- Select the Log table
-- Add your fields
-- Set criteria for the date field to
=Date()
(by the way Date is a bad name for a field, since Date is also a function
returning the current system date)
-- Save the query as qLoggedInToday

Now
-- Open a new query
-- Select Roster from the tables and qLoggedInToday from the queries
-- Drag from Emp # to Emp #
-- Double-click the relationship line
-- Choose ALL from Roster and only matching from qLoggedInToday
-- Select the desired fields.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
I am hoping someone out there might be able to help me.

I have put together an MS Access 07 database that we use to track our
employee’s attendance. It contains two separate files/tables;

1) Roster – This contains the employee’s First, Last, MI, Emp # and a
password that the employee uses to log onto the database.
2) Log – This contains the attendance data for all of our employees. I.E.
Emp #, Clock-In Date and time

The database works well but I want to create an Access form, query or report
which monitors all of the employee’s arrival to work. I want to display the
entire roster and then compare and display the date and time of arrival for
each employee or lack thereof.

I have tried to use an Access report, and a query – but the output only
displays those employees that have arrived and not those that have yet to
report to work.

I have also tried to use an MS Form but my macro writing skills are not
quite sufficient.

Does anyone have some advice for me?

Here is a sample of what I am trying to do;

FIRST MI. LAST EMP# DATE TIME
JOHN C. DOE 11111 9/1/08 6:54 AM
MARY P. SMITH 22222
BRIAN D. COX 33333
FRED A. WILLSON 44444 9/1/08 7:03 AM
End of list

The first, Mi, last names, and employee numbers are stored in the roster
datatable. The date and time are stored in a log datatable.

Employee 22222 and 33333 have yet to arrive to work therefore no log data is
available to display.

All of my attempts result in a report that only displays employee numbers
11111 & 44444. I need to display all employees and any log data that might
exist for each employee.

Thanks in advance
Click to expand...
Click to expand...
 
Back
Top