If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Report on things not done
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 |
#2
|
|||
|
|||
Report on things not done
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. -- Build a little, test a little. "Lostguy" wrote: 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 |
#3
|
|||
|
|||
Report on things not done
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 |
#4
|
|||
|
|||
Report on things not done
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. -- Build a little, test a little. "Lostguy" wrote: 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 |
Thread Tools | |
Display Modes | |
|
|