Getting totals for different entries in a field

[Sackey]

Please open the DDrive Doc to read my question. Thank you.

[Rabbit]

Please post your question in the thread itself. As a general rule of thumb, I do not open files from strangers.

[Sackey]

i have the field (incidenS)from a table. This field have different entries for each employee (collision, injury, inspection). i am trying to figure out a way to get a total for each entry (total collision, total injury, total inspection)for each employee, and put the individual totals in a form. I tried creating a query to add them up, but when i enter them on the form i get the word 'error' in its place.Some help/advise will be greatly appreciated. Thank you.

PS: I did create a query doing a count, that did give me totals for each (collision, injury, inspection), but i get the asme 'error' when i try to include it in the form.

[Rabbit]

Please show us the query.

[Sackey]

I am trying to copy/paste it but its not working, the paste option is grayed out

[Rabbit]

You can press ctrl+v to paste something that you've copied.

[Sackey]

I had already tried that (ctrl+c ctrl+v) and it did not work, I even try to copy/past using the snipping tool, but nothing seem to work.

[Sackey]

The only way that I can get it to copy/paste is to show it in SQL View which I am not to familiar with, If that will help you in anyway, the following is the SQL VIEW. Thank You.

SQL VIEW:

SELECT [Master Roster].Employee, [Master Roster].[Last Name], [Master Roster].[First Name], DisciplineT.Incidents, [Master Roster].[Adj Hire Date], [Master Roster].[Job Description], [Master Roster].[User Level], Count(DisciplineT.Incidents) AS [CountOfInjury/Collision/Roadside Inspection]
FROM DisciplineT INNER JOIN [Master Roster] ON DisciplineT.[Employee #] = [Master Roster].Employee
GROUP BY [Master Roster].Employee, [Master Roster].[Last Name], [Master Roster].[First Name], DisciplineT.Incidents, [Master Roster].[Adj Hire Date], [Master Roster].[Job Description], [Master Roster].[User Level]
HAVING (((DisciplineT.Incidents)="collision" Or (DisciplineT.Incidents)="injury" Or (DisciplineT.Incidents)="Roadside Inspection"));

[Rabbit]

The SQL view is what I meant. You can't copy and past a query object into a text box. Also, please use code tags when posting code or formatted data.

What do you mean you get an error when you "put it on a form"? You mean you set the query as the row source and tried to set the control sources to the fields in the query? If the query runs, then using the query as a row source shouldn't pose any problems.

I did notice some potential issues with your naming convention though. It's best to avoid spaces and other special characters when naming a table or a field, they tend to cause more trouble than anything.

Also, I'm not sure why you're trying to do this in a form. It's usually better to do this type of summary information in a report because a form is for entering data whereas a report is for viewing data. This type of summary query can only be viewed and can not be edited from a form so it's odd that you would want to put it in a form.

[Sackey]

The user wanted a form instead. They wanted to enter an employee number and have the rest of the form populate. i already have a report and the totals show up as they should, but when i use the query to populat th form most of th records are not showing up. i am aware of the naming convention, but was trying to get the form to work first

[Rabbit]

You can do pretty much the same thing using a report.

How is the form set up? Are all the fields showing #ERROR#? Or just some, which ones? How are those fields set up? Is there any code running?