Adding records

[Guest]

I have a query with Person Creating Error and a total errors column.
My output looks like this:
Person Creating Error Total Errors
John Smith 2
John Smith 3
John Smith 1
Susan Smith 1
Susan Smith 2
Mary Smith 1
Mary Smith 2


What I would like to see is:
Person Creating Error Total Errors
John Smith 6
Susan Smith 3
Mary Smith 3

Any help would be appreciated and thanks in advance.

 

[Carl Rapson]

Use an aggregate function (SUM) and a GROUP BY clause in your query:

SELECT [Person Creating Error],Sum([Total Errors])
FROM table
GROUP BY [Person Creating Error];

Carl Rapson

 

[Guest]

I am fairly new with access. I have sum the total errors and have group by
for the Person creating error, where is this suppose to go
SELECT [Person Creating Error],Sum([Total Errors])
FROM table
GROUP BY [Person Creating Error];

Thanks for your help so far.

Use an aggregate function (SUM) and a GROUP BY clause in your query:

SELECT [Person Creating Error],Sum([Total Errors])
FROM table
GROUP BY [Person Creating Error];

Carl Rapson

I have a query with Person Creating Error and a total errors column.
My output looks like this:
Person Creating Error Total Errors
John Smith 2
John Smith 3
John Smith 1
Susan Smith 1
Susan Smith 2
Mary Smith 1
Mary Smith 2


What I would like to see is:
Person Creating Error Total Errors
John Smith 6
Susan Smith 3
Mary Smith 3

Any help would be appreciated and thanks in advance.

 

[John W. Vinson]

I have a query with Person Creating Error and a total errors column.
My output looks like this:
Person Creating Error Total Errors
John Smith 2
John Smith 3
John Smith 1
Susan Smith 1
Susan Smith 2
Mary Smith 1
Mary Smith 2


What I would like to see is:
Person Creating Error Total Errors
John Smith 6
Susan Smith 3
Mary Smith 3

Any help would be appreciated and thanks in advance.

You say you are getting this from a Query. What's the Query? Could you
post the SQL?

What you apparently need - depending on the structure of your table
and the nature of this query - is a Totals query. Open the query in
design view; click the Greek Sigma icon (looks like a sideways M);
leave the default [Group By] on the Totals row under [Person Creating
Error] and select Sum under [Total Errors].


John W. Vinson [MVP]

 

[Guest]

I do have it as a totals query I am just unsure where I am suppose to put the
clause. I am assuming by your statement to put it in an SQL? Which one, I
have 3 different kinds to choice from.

Thanks again

 

[John W. Vinson]

I do have it as a totals query I am just unsure where I am suppose to put the
clause. I am assuming by your statement to put it in an SQL? Which one, I
have 3 different kinds to choice from.

Thanks again

I cannot see your database.
I do not know what fields exist in your table.
I do not know what your current query looks like.
And I do not know what you mean by "put it in an SQL?".

SQL is the language of queries. The query grid is JUST A TOOL to help
you build SQL strings; the grid isn't the query. You can see the
*real* query - the SQL - by selecting View... SQL from the menu.
Please do so and post the current SQL of your query to a message here.

John W. Vinson [MVP]

 

[Guest]

SELECT [Error Detail tbl].[Person Creating Error], Sum(([AIS omitted]+[AIS
incorrect]+[Amendment]+[AWD misc]+[AWD sources]+[AWD Pol Print]+[AWD Pol
Print exam]+[AWD transaction not created]+[AWD Surf for Sources]+[AWD Notes
Unclear]+[AWD Notes Not Followed]+[AWD Push Case]+[AWD CSD]+[Procedural
Rules]+[System Entry]+[Underwriting]+[EnCorr letter]+[Agent]+[Companion Not
Issued]+[MVR/IR]+[Pull Labs]+[Reissue]+[Reprint]+[Update Mainframe]+[Wrong
Policy Scheme]+[Replacement]+[Requirement]+[Entry Incorrect]+[Other])*-1) AS
Total
FROM [Error Detail tbl] INNER JOIN [Employee Team tbl] ON [Error Detail
tbl].[Person Creating Error] = [Employee Team tbl].Name
GROUP BY [Error Detail tbl].[Person Creating Error], [Employee Team
tbl].Team, [Error Detail tbl].Date
HAVING ((([Employee Team tbl].Team)=[Forms]![QR Reports form]![Team
Selection IDP]) AND (([Error Detail tbl].Date) Between ([Forms]![QR Reports
form]![IAG Start Date]) And ([Forms]![QR Reports form]![IAG End Date])))
ORDER BY [Error Detail tbl].[Person Creating Error];

Sorry I am very new at this and what I know I have self taught so I do not
know all the ends and outs of the product. But here is what I have so far in
the SQL view. Thanks in advance for any help.

 

[John W. Vinson]

SELECT [Error Detail tbl].[Person Creating Error], Sum(([AIS omitted]+[AIS
incorrect]+[Amendment]+[AWD misc]+[AWD sources]+[AWD Pol Print]+[AWD Pol
Print exam]+[AWD transaction not created]+[AWD Surf for Sources]+[AWD Notes
Unclear]+[AWD Notes Not Followed]+[AWD Push Case]+[AWD CSD]+[Procedural
Rules]+[System Entry]+[Underwriting]+[EnCorr letter]+[Agent]+[Companion Not
Issued]+[MVR/IR]+[Pull Labs]+[Reissue]+[Reprint]+[Update Mainframe]+[Wrong
Policy Scheme]+[Replacement]+[Requirement]+[Entry Incorrect]+[Other])*-1) AS
Total
FROM [Error Detail tbl] INNER JOIN [Employee Team tbl] ON [Error Detail
tbl].[Person Creating Error] = [Employee Team tbl].Name
GROUP BY [Error Detail tbl].[Person Creating Error], [Employee Team
tbl].Team, [Error Detail tbl].Date
HAVING ((([Employee Team tbl].Team)=[Forms]![QR Reports form]![Team
Selection IDP]) AND (([Error Detail tbl].Date) Between ([Forms]![QR Reports
form]![IAG Start Date]) And ([Forms]![QR Reports form]![IAG End Date])))
ORDER BY [Error Detail tbl].[Person Creating Error];

Sorry I am very new at this and what I know I have self taught so I do not
know all the ends and outs of the product. But here is what I have so far in
the SQL view. Thanks in advance for any help.

Well, this will give you a Sum grouped by [Person Creating Error],
[Team] and [Date]. This will give you as many lines as there are Teams
and Dates for each person. If you don't want to group by Team or Date,
change the Group By on each of these fields to WHERE. The resulting
SQL will be more like

SELECT [Error Detail tbl].[Person Creating Error],
Sum(([AIS omitted]+[AIS incorrect]+[Amendment]+[AWD misc]+[AWD
sources]+[AWD Pol Print]+[AWD Pol Print exam]+[AWD transaction not
created]+[AWD Surf for Sources]+[AWD Notes Unclear]+[AWD Notes Not
Followed]+[AWD Push Case]+[AWD CSD]+[Procedural Rules]+[System
Entry]+[Underwriting]+[EnCorr letter]+[Agent]+[Companion Not
Issued]+[MVR/IR]+[Pull Labs]+[Reissue]+[Reprint]+[Update
Mainframe]+[Wrong Policy Scheme]+[Replacement]+[Requirement]+[Entry
Incorrect]+[Other])*-1) AS Total
FROM [Error Detail tbl] INNER JOIN [Employee Team tbl] ON [Error
Detail tbl].[Person Creating Error] = [Employee Team tbl].Name
GROUP BY [Error Detail tbl].[Person Creating Error]
WHERE ((([Employee Team tbl].Team)=[Forms]![QR Reports form]![Team
Selection IDP]) AND (([Error Detail tbl].Date) Between ([Forms]![QR
Reports
form]![IAG Start Date]) And ([Forms]![QR Reports form]![IAG End
Date])))
ORDER BY [Error Detail tbl].[Person Creating Error];

I can see several problems with your table design. For one thing, Date
is a reserved word (fot the built in Date() function), and Access very
likely will get confused; worse, you're "committing spreadsheet" by
putting DATA in fieldnames.

John W. Vinson [MVP]