Problem Number 2

[coffeeking]

Thanks for the replies to problem number 1 i am still experimenting to
get it to work on a bound ( to a query) text box.
However problem 2 has me stumped.
I have 1 person[staff] attending 1 course [course] that has 4 modules
( no discreet record ) and i want to find via a query everyone who has
attended all 4 modules. for example.

-IIf([module number]="1","completed",not completed"). works perfectly
for the "1".

However how can i ask it to return "completed" only for the [staff]
that have a 1 and 2 and 3 and 4. I have tried && etc but i fear i am
asking the wrong question because the module numbers are not discreet
records only choices made from a drop down list box.

hope you can understand my question

cheers,

 

[Allen Browne]

The absolute miminum tables you would need to achieve what you describe
would be these four:

a) Staff table (one record for each person), with StaffID primary key

b) Course table (one record for each course), with CourseID primary key

c) CourseModule table (to define the modules that make up the course) with
fields like this:
- CourseModuleID primary key
- CourseID relates to your Course table.

d) StaffModule table (to record when a staff member has completed a module),
with fields:
- StaffID relates to your staff table
- CourseModuleID relates to your module table
- CompleteDate Date/Time (when this person completed this module.)
Make the combination of StaffID + CourseModuleID the primary key (so one
person can't do the same module multiple times.)

Now create two queries:
1. Create a query using the Course and CourseModule tables.
In query design view, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.
In the Total row under CourseID, accept Group By.
In the Total row under CourseModuleID, choose Count.
The query tells you how many modules are in each course.
Save the query with a name such as qryCourseModCount.

2. Create another query using StaffModule and qryCourseModCount as input
"tables."
Depress the Totals button.
Accept Group By in the Total row under CourseID, CountOfCourseID, and
StaffID
In the Total row under CourseModuleID, choose Count.
In the Criteria row under CourseModuleID, enter:
=[CountOfCourseID]

The second query shows a record for each combination of Staff and Course,
the number of modules in the course, and the number of modules the person
has completed. The criteria limits it to those people who have completed all
the modules.

 

[coffeeking]

The absolute miminum tables you would need to achieve what you describe
would be these four:

a) Staff table (one record for each person), with StaffID primary key

b) Course table (one record for each course), with CourseID primary key

c) CourseModule table (to define the modules that make up the course) with
fields like this:
- CourseModuleID primary key
- CourseID            relates to your Course table.

d) StaffModule table (to record when a staff member has completed a module),
with fields:
- StaffID               relates to your staff table
- CourseModuleID relates to your module table
- CompleteDate    Date/Time (when this person completed this module.)
Make the combination of StaffID + CourseModuleID the primary key (so one
person can't do the same module multiple times.)

Now create two queries:
1. Create a query using the Course and CourseModule tables.
In query design view, depress the Total button on the toolbar.
Access adds a Total row to the query design grid.
In the Total row under CourseID, accept Group By.
In the Total row under CourseModuleID, choose Count.
The query tells you how many modules are in each course.
Save the query with a name such as qryCourseModCount.

2. Create another query using StaffModule and qryCourseModCount as input
"tables."
Depress the Totals button.
Accept Group By in the Total row under CourseID, CountOfCourseID, and
StaffID
In the Total row under CourseModuleID, choose Count.
In the Criteria row under CourseModuleID, enter:
    =[CountOfCourseID]

The second query shows a record for each combination of Staff and Course,
the number of modules in the course, and the number of modules the person
has completed. The criteria limits it to those people who have completed all
the modules.

--
Allen Browne - Microsoft MVP.  Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

Thanks for the replies to problem number 1 i am still experimenting to
get it to work on a bound ( to a query) text box.
However problem 2 has me stumped.
I have 1 person[staff] attending 1 course [course] that has 4 modules
( no discreet record ) and i want to find via a query everyone who has
attended all 4 modules. for example.

-IIf([module number]="1","completed",not completed"). works perfectly
for the "1".

However how can i ask it to return "completed" only for the [staff]
that have a 1 and 2 and 3 and 4. I have tried && etc but i fear i am
asking the wrong question because the module numbers are not discreet
records only choices made from a drop down list box.

hope you can understand my question

cheers,- Hide quoted text -

- Show quoted text -

Thanks for the very well presented answer. I will apply sometime over
the next week and let you nkow how I go.

Many thanks JD