Help With Simple Report? Thank you in advance

[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?

 

[John W. Vinson]

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]

 

[RobertKellerman8 via AccessMonster.com]

Thank you for responding,

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

|joe| |Trans error| |code error| |date|

each has its own field.

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

|joe| |Trans error| |code error| |date|
1 2 11/07/07
|joe| |Trans error| |code error| |date|
3 4 11/08/07
|joe| |Trans error| |code error| |date|
5 6 11/09/07
|ann| |Trans error| |code error| |date|
2 1 11/07/07
|ann| |Trans error| |code error| |date|
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

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]

 

[John W. Vinson]

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

|joe| |Trans error| |code error| |date|

each has its own field.

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

|joe| |Trans error| |code error| |date|
1 2 11/07/07
|joe| |Trans error| |code error| |date|
3 4 11/08/07
|joe| |Trans error| |code error| |date|
5 6 11/09/07
|ann| |Trans error| |code error| |date|
2 1 11/07/07
|ann| |Trans error| |code error| |date|
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

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]