Sum Records With Text

[hammer187]

I have a form with a combo box in it. The combo box has 3 items. Completed, In Progress, Incomplete. A user completes the form and access puts the information in the database. How do i do a query that gives me how many "Completes" a user has or "Incompletes"?

[orange]

You haven't told us anything about your tables.

Let's suppose you have a tblWorkOrders, since you didn't give us any info, and suppose for each WorkOrder you could have WorkOrderStatus of any of

InProgress
, Completed
, InCompleted

Try this as a template for your query

Select WorkOrderStatus, Count(WorkOrder) from TblWorkOrders
Group By WorkOrderStatus;

[hammer187]

GSTDtbl is my table. I have 5 combo boxes named RootStatus1, RootStatus2... RootStatus5. Each of these RootStatus combo boxes have incomplete, completed, In Progress. They are stored in the field of the table named RootStatus1, RootStatus2... I am trying to sum up all of the "Completed" and "Incomplete" and "In Progress" of all of these fields for each user. The code you provided works good for one RootStatus, but I need it to encompass all of the RootStatus boxes.

[hammer187]

I guess I could write 5 different queries. If I go that route, how do I combine those 5 queries into one query summing up the fields that I summed up in the other queries? Which way would be easier? Writing one query that does all of the RootStatus boxes or just combining the separate queries?

[hammer187]

Nevermind, I think I found the answer. A union query. Thanks for your help.

[orange]

I would not recommend using Lookups (combobox) at the table field level.

Lookup tables, referenced via combo boxes (and list boxes) in forms is the
preferred method of handling lookups.

[hammer187]

I do not have combo boxes in the table themselves just on the forms.

[hammer187]

<SELECT GSTDtbl.RootStatus1, Count(GSTDtbl.RootStatus1) AS CountOfRootStatus1, GSTDtbl.RootStatus2, Count(GSTDtbl.RootStatus2) AS CountofRootStatus2, GSTDtbl.RootStatus3, Count(GSTDtbl.RootStatus3) AS CountofRootStatus3, GSTDtbl.RootStatus4, Count(GSTDtbl.RootStatus4) AS CountofRootStatus4, GSTDtbl.RootStatus5, Count(GSTDtbl.RootStatus5) AS CountofRootStatus5
FROM GSTDtbl
GROUP BY GSTDtbl.RootStatus1, GSTDtbl.RootStatus2, GSTDtbl.RootStatus3, GSTDtbl.RootStatus4, GSTDtbl.RootStatus5;>

For some reason this query doesn't add the items. For example it has completed 1 completed 1 under RootStatus1 instead of having completed 2. Individual queries work but when I add them all, it doesn't add the items.

[orange]

That part wasn't clear. Do you have your query working?
Why do you have

I have 5 combo boxes named RootStatus1, RootStatus2... RootStatus5. Each of these RootStatus combo boxes have incomplete, completed, In Progress. They are stored in the field of the table named RootStatus1, RootStatus2...

[hammer187]

I have my query that does RootStatus1 working and I can get it to work with all of the others but when I combine them, it doesn't add the items. The reason why I have 5 combo boxes is that the user has to put 5 RootCause statements and each RootStatus is for each RootCause statement.

[hammer187]

Figured it out. I will mark as solved.