Report totalling

[René]

I am trying unsuccessfully to get a report to total the number of different fields. I would optimally like to do this on the form instead of report, but am totally lost about that one

My employees are involved in education program that I track on a table with yes/no boxes. There are 48 lessons, hence 48 yes/no fields. The ultimate would be a field on the form that stated how many yes' are checked for each employee. If that can't be done any suggestions on a report that will tell me how many yes fields and auto total for me

Thanks

 

[Steve Schapel]

René,

"There are 48 lessons, hence 48 yes/no fields" is incorrect. This
should be 48 records per employee in a separate linked table. Then
your task would be easy Any chance of reviewing this aspect of
the database design, or is it set in concrete? If you must do it with
your existing structure, you can make a calculated field in a query,
like this...
Yesses: Abs([L1]+[L2]+[L3]+...+[L48])
.... but you may have to rename the fields first, so that the query
expression doesn't exceed the limit (off-hand I think it's 255
characters).

- Steve Schapel, Microsoft Access MVP

 

[René]

Thanks, right now the employee information is in one table and the lessons are in another table. The relationship link is the employee name and the fields in the lesson table are numbered from 1 to 48.

I currently view this through a sub-form on a active employee form.

You said on a linked table, does that sound like what I described above?

I'm rolling out this dbase for data entry tomorrow, but this portion can be modified with little problem due to it being in a separate table.

As I said the ultimate would be for the sub-form to total the number of boxes checked on the sub-form. But I would gladly settle for and do need this in report format that will print out/show all active employees and the total of lessons completed

Signed the stubborn beginner

 

[Steve Schapel]

Stubborn Beginner,

I would advise against using the employee name as the basis of
relationships between tables. You will always have to guard against
the possibility of two employees with the same name, plus the vagaries
of spelling errors and stuff like that. Better to use an ID numbering
system of some sort, of which an Autonumber field may well be ideal
for the job.

The example I gave you before is the only way I know of to do what you
are asking with your present structure. This can be done in the
controlsource of an unbound textbox on your form and/or report, but
probably smoother to calculate it in a query and then use the query as
the basis of your form or report.

Yes, you have linked tables. But you should only have *one field* for
lesson, and 48 *records* for each employee, NOT 48 *fields*. The way
you have done it is the type of method you might use in spreadsheets
and stuff like that, but is generally really not suitable in a
database. I can't be much more specific at this stage, without
knowing more about what "leason" means, and what the checks mean, etc.

- Steve Schapel, Microsoft Access MVP

 

[René]

You said something originally about changing the structure of my dbase - if you could clarify or suggest, I'm all ears

The other thought that I thought I should clarify is that the employee name field is automatically entered into the GRAD Lesson table by the linkage, you can't enter info in any other way but through a subform connected to the main form. The GRAD Lesson table is set up as one field for each lesson. When I went into table design view, I assigned the field name as 1 through 48 and the data type to yes/no. That is how that I got 48 yes/no boxes on the form

"There are 48 lessons, hence 48 yes/no fields" is incorrect. This should be 48 records per employee in a separate linked table. -- This is where you lost me, could you give me a little further direction -- Thanks

 

[Steve Schapel]

René,

As I mentioned, it would help me a lot to know what the Lesson means,
and what the check boxes indicate, and what other data you are
recording, and what other data is in the Grad Lesson table etc etc.
My clairvoyant assistant is still on vacation. But I will say one
thing... you shouldn't have the employee name in the grad lesson table
at all. Anyway, see if you can put me in the picture a bit more.

- Steve Schapel, Microsoft Access MVP

 

[René]

Thanks for your patience,

We send out lessons (snail mail) each month. As each employee completes the lesson we track that by checking the corresponding yes/no box to the lesson. ie the emp. completes the homework type lesson and mails it back into the office. They are approved for their raises only if they are current on their lessons/assignments. And receive a bonus at the completion of every 12.

Currently the structure of the GRAD lesson table holds nothing but yes/no fields numbered 1 through 48, ID [AutoNumber], Employee ID[Number] and Full Name (Emp Name)[Text]

My GRAD form is pulled directly from the GRAD table the form contains the following fields, Full Name (Emp Name) and the yes/no fields numbered 1 through 48.

The Link Child Fields and Link Master Fields are both Full Name and the information pulls correctly from the Employee table and the GRAD table correctly.

When I changed the Link Child and Link Master Fields to EmployeeID and go out of design view it gives me a Enter Parameter Value window asking for EmployeeID. I also looked into the GRAD table and do not see any EmployeeID #'s being transferred over into this table. Yes I changed the relationship from Full Name to EmployeeID in the main relationship window.

Let me know if this clears anything up or just muddied the water

 

[Steve Schapel]

René,

Thank you, this is much clearer.

I recommend your tables be constructed as follows:

Table: Employees
EmployeeID
EmployeeName
other employee-related data

Table: GRAD
GRAD_ID
EmployeeID
LessonNumber
Completed (yes/no)

That's if you in fact really want to list all lessons not completed.
If you just want to record those lessons actually completed (and this
is probably all that would be required for your tracking and
statistical purposes), then all you need is...
Table: GRAD
GRAD_ID
EmployeeID
LessonNumber

- Steve Schapel, Microsoft Access MVP

 

[René]

Praise be, we're finally speaking the same language. I've been having that trouble alot lately!

I would like to have a reference to lessons not completed so that employees can have them resent at a later date if need be.

Table: GRAD
GRAD_ID
EmployeeID
LessonNumber -- is this a separate field from Completed?
Completed (yes/no)

What next to see at a glance both on the form and report how many completed?

Earlier you had said
Yesses: Abs([L1]+[L2]+[L3]+...+[L48]) -- in a calculated field in a query - I'm not familiar with this but will play around until I hear back from you



Sorry if I seem dense, but every once in a while a light bulb goes on. Thanks

 

[Steve Schapel]

René,

The calculated query field example I gave was a work-around for the
non-normalised field structure. [L1] etc were supposed to represent
your lesson fields. If you go with the table structure I suggested,
you will not use this type of approach to show the number of lessons.
Instead, you will put an unbound textbox in the Footer section of the
form or report, with it's control source set to...
=Count(*)

I would strongly recommend you do not include the Completed field in
the table. If your reason for wanting it there is as stated, i.e. to
identify those not done, well... you don't need it for this, and it
just adds unnecessary clutter and complication. I would use...
Table: GRAD
GRAD_ID
EmployeeID
LessonNumber

Let's say that Employee number 1 has done lessons 1-6 and Employee
number 2 has done lessons 1-4 and 6-8, then the datasheet of the table
might look like this:

1 1 1
2 1 2
3 2 1
4 1 3
5 2 2
6 2 3
7 1 4
8 1 5
9 2 4
10 2 6
11 1 6
12 2 7
13 2 8

Get the idea?

You will base your report on a query which combines the two tables, so
that you have the employee's name available to show. The report
should be grouped, via the report design's Sorting and Grouping
dialog. As for the form, it would be best to use a form based on the
GRAD table as a subform on the Employee form.

In addition, it will then be a breeze to use queries to get answers to
such questions as "which employees have done Lesson 17?"

I know some of the concepts I am mentioning are possibly infamiliar to
you, but look them up in Help, or in your book, and you will soon get
the idea.

- Steve Schapel, Microsoft Access MVP

 

[René]

I think we're half way there, I'm see a faint light in the tunnel.

I currently have a main employee form with various sub-forms ie. requirements, schedule, GRAD and Contact & Follow Up. These are all set up as a tab top

The GRAD subform was originally set up by the wizard to open form with matching fields Full Name<->Full Name

I went back into the table and deleted the Full Name field from the GRAD table (it still contains EmployeeID) I also removed the relationship between Full Names in both the Employee table and the GRAD table.

In the GRAD form this is what I have in the record source:
SELECT [GRAD Levels].*, [Employees].[Full Name] FROM [GRAD Levels], Employees;

I am assuming that somehow I need to match EmployeeID<->EmployeeID to have the correct information pulled into the form. Because the form now has the employee name but doesn't show that any of the boxes have been checked.

While looking at the datasheet view of the query, I also do not see that any of the EmployeeID #'s are being carried over into this table (GRAD). The datasheet is currently listing employee names 2 to three times with GRADID #'s from 1 to 3 and some boxes are checked ie everybody has some GRADID 3 checked etc. Is there a joined property that I should be using?

When I join from GRAD to Employees, I only get three records but no name or ID

 

[Steve Schapel]

Well, the SQL of the recordsource, as you have it, would need to be
something like this:
SELECT [GRAD Levels].*, Employees.[Full Name] FROM [GRAD Levels],
Employees WHERE Employees.EmployeeID = [GRAD Levels].EmployeeID

But really, I might use something like the above for a report, but in
the case of a form/subform, it doen't really make a lot of sense.
Just use the GRAD Levels table as the record source of the subform.
There is no need to include the employee name in the subform, because
it will already, presumably, be displayed on the main form, and you
don't need to see it all over the place. This is managed via the use
of the subform's LinkMasterFields and LinkChildFields properties,
which in this case would both be set to EmployeeID.

- Steve Schapel, Microsoft Access MVP