Report on things not done

  • Thread starter Thread starter Lostguy
  • Start date Start date
L

Lostguy

After many posts and much, much help, the employee and event tracking
database I/we have been working on seemingly forever is almost done.
The goal is for it to track every employee's personal data as well as
every event/task that that employee does. I have one more hurdle left
which is the "guts" of this project:

Here's the setup (I am leaving out some of the supporting lookup
tables):

40 employees (30 civilian, 10 military) divided among 4 sections.

tblEmployee: EmployeeIDpk(1), LName
(Data for each employee)

tblEvent: EventIDpk (join), Event, EventPeriodicity, ApplicableTo,
CivMilBoth
(Data for each event: Flu Shot, 365 (days), all (sections), Both
(Civilian, Military, or Both))

tblEventWhen: EventWhenIDpk (join), EventIDfk (join), EventDate,
EventRemarks
(When each event took place)

tblEmpEvent: EventWhenIDfk (join), EmployeeIDfk (many), EmpEventIDpk
(Which employees did which events)

All relationships are ref int with Join Type 1 (With the 1's and
many's shown above), except for the "joins" shown above that I had to
uncheck ref integrity because I kept getting the error that I could
not delete a record because it had a related record....??

The frmEventEntry (based on tblEventWhen) with fields EventDate,
EventIDfk (combo), and EventRemarks

The continuous subform is from tblEmpEVent with EmployeeIDfk (combo)



I don't know how to setup the following:

For each employee, create a report showing which Events have no
associated EventDate. Do not list any Events that have been deemed not
applicable to that particular employee (based on the ApplicableTo) and
CivMilBoth fields.


??

VR/Lost
 
Create a query showing all people who have completed events.

Then create another query for all people and events left joined on people
and events to the first query. Set ctiteria Is Null for people and events
fields from the first query.
 
Sir,

I can do the 1st query.

Can you Barney-down how to do the second query? I can use the wizard,
but I don't know enough for SQL view. So a "left join" is Greek to me.

??

VR/Lost
 
No SQL necessary. After you have the first query saved the create the select
query.
Open in design view. You will see a rectangle representing the table in the
space above a grid with rows labeled - Field, Table, Sort, and Criteria.
Click on the icon to show tables, select queries tab, select your first
query.
Click on the people field and drag to the people field of the query
rectangle. Click on the connceting line, double click the line, select the
option to see all records in the table and only those of the query that match.
Do the same for the events field. Both line will have an arrow pointing
from table to query fields.
Click on query people field and drag to empty column of the Field row. Do
the same for the events field. In the Criteria row for each of these type in
'Is Null' without the quotes.
Save and run.
 
Back
Top