Counting 3 different fields seperately on a report

[SLKoelker]

Every equation I have tried (that I found on this website) has given me a
'parameter' prompt. What I need is very simple. There are 3 fields, accepted,
rejected, and pending. I want to count the "X's" in each column to show
totals for each column at the end of the report. can anyone help??

 

[Jeff C]

There are may different methods, the one I would probably use is to just
quickly build a totals query for each of the three fields. I would then
place each of these totals queries in the query you are using as the record
source for your report. You can then just place the field from each of the
totals queries in the appropriate location in your report.

 

[SLKoelker]

Sounds simple enough, however, I am not sure how to build a 'totals query'.
Is there a place in my query I need to type in a certain equation? I have
'criteria for the three fields in my query. Where would the totals query fit
in? Is it a seperate query all together? I need more direction, please.

 

[Jeff Boyce]

If you are using a query to get a total, and it gives you a parameter
prompt, often that's Access' way of reminding you that spelling counts!
Access asks you for a value (via a parameter prompt) when it can't find the
object you've named.

Your three fields, are they text fields, into which you have inserted the
letter "X"? Are they True/False fields, that store a "0" and a "-1" (for
Access data), but can be made to display a checkbox, or ...?

How you total up the columns depends on what the columns hold ... you might
need to use "Count" instead of "Sum"!

Also, from your description, it sounds like the three fields are actually
mutually exclusive (i.e., a record can only ever be only one of the three).
If that's the case, then your table is designed more like a spreadsheet than
a relational database table.

If this is true, you could use a single field and store the status
(accepted, rejected, pending) in that field.

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[SLKoelker]

My database is linked to a spreadsheet in excel (not sure if that is why I
can't get it to work). The three fields are text fields in which I insert an
'X'. These fields are mutually exclusive. If the material was rejected
initially the 'X' is in the rejected column. When I re-submit the material
the 'X' is moved to the pending column. And if it passes the second test the
'X' moves to the accept column. Does this help clarify? Please help.

 

[Jeff C]

Open the query builder, place the linked table in the query, insert one of
the fields you wish to count the X's.

click on the button with the summation sign which makes your select query a
totals query.

In the criteria for the field you inserted place: Like "X"

for the summation select Count

 

[SLKoelker]

Thank you so much for trying to help me, but I can't seem to get it to work.
I created the 'totals query' and put in the criteria Like "X" but when I go
to creat a report with my material query and my totals query (using wizard) I
get an error message that says: 'You've chosen fields from record sources
which the wizard can't connect. You may have chosen fields from a table and a
query based on that table, If so try choosing fields from only the table or
query.'

However, I did not use a table and a query I used fields from two seperate
query's based off the table. Please help.

 

[Jeff C]

skip the query wizard and build your query from scratch, open the query
builder, select the add table icon and choose the tables and/or queries you
want to add to the query to base your report on, add the fields including the
fields from your totals query.