Help With Simple Report? Thank you in advance

21 Nov 2007 1:40 PM

RobertKellerman8 via AccessMonster.com

Hello,

I have multiple employees. I track each employees work to provide them with
feedback.
Each employee enters information into an accounting system.
I have a form that I use (given to me) in access that allows me to enter the
employees name, their entry and any mistakes ( I let them know how good they
are doing, not how bad :) The mistakes are different types. The data base
in access holds the following: Joe, 1 Transaction error, 1 coding error.
11/01/07. Each day this type of entry is made. The result is many joes and
many transaction errors and coding errors. I need the report to gather all
of Joes errors and total each kind for the month: Example: Joe 4
transaction errors, 3 coding errors for November. Ann 2 transaction codes
and 1 coding error.
What I am getting is a list of what I already have entered Example:
Joe 1 trans error, 1 code error 11/02/07
Joe 0 trans error, 2 code error 12/02/07
ETC.....

What can I do to get Access to gather all of Joes and place them all on one
line?

--
Message posted via http://www.accessmonster.com

21 Nov 2007 6:34 PM

John W. Vinson

On Wed, 21 Nov 2007 13:40:09 GMT, "RobertKellerman8 via AccessMonster.com"
<u39253@uwe> wrote:

Show quote

>Hello,
>
>I have multiple employees. I track each employees work to provide them with
>feedback.
>Each employee enters information into an accounting system.
>I have a form that I use (given to me) in access that allows me to enter the
>employees name, their entry and any mistakes ( I let them know how good they
>are doing, not how bad :) The mistakes are different types. The data base
>in access holds the following: Joe, 1 Transaction error, 1 coding error.
>11/01/07. Each day this type of entry is made. The result is many joes and
>many transaction errors and coding errors. I need the report to gather all
>of Joes errors and total each kind for the month: Example: Joe 4
>transaction errors, 3 coding errors for November. Ann 2 transaction codes
>and 1 coding error.
>What I am getting is a list of what I already have entered Example:
>Joe 1 trans error, 1 code error 11/02/07
>Joe 0 trans error, 2 code error 12/02/07
>ETC.....
>
>What can I do to get Access to gather all of Joes and place them all on one
>line?

What's the structure of your table? Are you storing a text string

Joe 1 trans error, 1 code error 11/02/07

If so... that's the source of your problem. Fields should be "atomic" having
only one meaning. A proper structure would have three tables:

Employees
EmployeeID <primary key>
LastName
FirstName
<other biographical data>

ErrorTypes
ErrorType <e.g. "Trans", "Code", ...> <primary key>

EmployeeErrors
ErrorID <Autonumber, primary key>
EmployeeID <who made the error>
ErrorDate <date/time, when>
ErrorType <what error was made>
Comments <memo, e.g. "crashed the server and required eleven hours to
recover">

Perhaps you could describe your actual table structure and post the SQL view
of the query you're using.

John W. Vinson [MVP]

21 Nov 2007 8:32 PM

RobertKellerman8 via AccessMonster.com

Thank you for responding,

I have a form, I use that places each item seperately.

each has its own field.

Because each day I enter information, when I run a report, it shows like this:

1 2 11/07/07

3 4 11/08/07

5 6 11/09/07

2 1 11/07/07

4 3 11/07/07

Ideally, I would like the report to generate this:

Month: November

|Trans Error| |Code Error|

Joe 9 12
Ann 6 4

John W. Vinson wrote:
Show quote

>>Hello,
>>
>[quoted text clipped - 17 lines]
>>What can I do to get Access to gather all of Joes and place them all on one
>>line?
>
>What's the structure of your table? Are you storing a text string
>
>Joe 1 trans error, 1 code error 11/02/07
>
>If so... that's the source of your problem. Fields should be "atomic" having
>only one meaning. A proper structure would have three tables:
>
>Employees
> EmployeeID <primary key>
> LastName
> FirstName
> <other biographical data>
>
>ErrorTypes
> ErrorType <e.g. "Trans", "Code", ...> <primary key>
>
>EmployeeErrors
> ErrorID <Autonumber, primary key>
> EmployeeID <who made the error>
> ErrorDate <date/time, when>
> ErrorType <what error was made>
> Comments <memo, e.g. "crashed the server and required eleven hours to
>recover">
>
>Perhaps you could describe your actual table structure and post the SQL view
>of the query you're using.
>
> John W. Vinson [MVP]

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/200711/1

21 Nov 2007 8:59 PM

John W. Vinson

On Wed, 21 Nov 2007 20:32:41 GMT, "RobertKellerman8 via AccessMonster.com"
<u39253@uwe> wrote:

Show quote

The form that you use is irrelevant: a form does not contain data, it's only a
window, a tool to enter data into a table. The table is the basis of
everything!

Create a new Query based on your table. Select the name field, the [Trans
Error] and the [Code Error] fields; select the date field only if you want the
report to be filtered to a specific range of dates.

Click the Greek Sigma "totals" icon - looks like a sideways M. You'll get a
new row in the query design grid labeled Totals, with "Group By" as the
default.

Change that to Sum under the two error fields, and - if you're using criteria
- to Where under the date field. Type a criterion such as

BETWEEN [Enter start date:] AND [Enter end date:]

on the criteria line under the date field if that's what you want - or to get
the previous month's data, you could use

>= DateSerial(Year(Date()), Month(Date())-1, 1) AND < DateSerial(Year(Date()), Month(Date()),1)

Base a report on this query.

John W. Vinson [MVP]