Counting Records Retrieved through Queries...

[chrisc]

Hello,

Hope this is the right place for this...

I am creating a testing database for components as they come off a
production line.

My reports need to select faults that are found, as well as pass or
fails dependent on a batch number.

I've got this part working fine, the problems start when I want to
total up the records being displayed by batch number.

I need to count up the passes and fails, and have been using this
method in the text field that displays the number on the report:

Passes: =DCount("Pass_Fail","Faults","Pass_Fail = -1")

Fails: =DCount("Pass_Fail","Faults","Pass_Fail = 0")

The table that this data is retrieved from is called 'Faults', and now
I find that the above statement is counting all records in the table,
not just the queried ones.

I have tried changing the 'Faults' bit to 'Pass_Fail_Test' which is my
query name, but then I just get errors and nothing more :(

Any help would be much appreicated :)

Chris.

[Rog]

Is Pass_Fail a Yes/No field? In that case it should work. I tried it
and it works for me.

[chrisc]

Yes it is a yes/no field..its really odd and highly frustrating!

[bruce]

ch****@cemgraft.co.uk wrote:

Yes it is a yes/no field..its really odd and highly frustrating!

It's working exactly as it should if you're describing it correctly.
Your DCount statements refer to your table ('Faults') and will count
all the records from Faults regardless of what query or table your
report is based on (i.e., what the RecordSource of your report is).
You were on the right track by replacing the table name 'Faults' with
the name of your query. Be sure that you include the field which you
want to count (i.e. the first parameter in your DCount function) in
your query and be sure to include the fields which are part of your
DCount criteria in your query as well. Look up the DCount function in
Help and look at the 'expr' and 'criteria' parameters for more
information.

HTH,
Bruce

[Alan Webb]

Chris,
DCount? Really? Go learn some SQL, it will improve your sex life.
Ok, let's assume a table called FAULT_FACT_TBL. Your table isn't named
this, but work with me. A simple case:
"SELECT COUNT(*) AS PASS_COUNT FROM FAULT_FACT_TBL WHERE PASS_FAIL = -1
GROUP BY BATCH_NO;"

This counts all the passes. Helpful, but we don't know how many failed with
just this. So, let's run this as a seperate query:

"SELECT COUNT(*) AS FAIL_COUNT FROM FAULT_FACT_TBL WHERE PASS_FAIL = 0 GROUP
BY BATCH_NO;"

Now we know in seperate queries how many passed and how many failed. But
our boss wants to see it on one result. Crap. The only thing we are sure
of is that SQL has this reputation as being able to present data pretty much
any way our PHB (Pointy Haired Boss, see www.dilbert.com) wants it. So,
there's gotta be a way, right. Right. A little slight of hand by using
aliases to name our views in our result and a join and we get our "Atta
Boy.":

"SELECT PASS_COUNT_VW.PASS_COUNT, FAIL_COUNT_VW.FAIL_COUNT FROM (SELECT
COUNT(*) AS PASS_COUNT FROM FAULT_FACT_TBL WHERE PASS_FAIL = -1 GROUP BY
BATCH_NO) AS PASS_COUNT_VW INNER JOIN (SELECT COUNT(*) AS FAIL_COUNT FROM
FAULT_FACT_TBL WHERE PASS_FAIL = 0 GROUP BY BATCH_NO) AS FAIL_COUNT_VW ON
PASS_COUNT_VW.BATCH_NO = FAIL_COUNT_VW.BATCH_NO;"

Now, I may be way off base and misread your e-mail. But hopefully I
understood and if I did, the above SQL should do what you want.
--
Alan Webb
kn*******@SPAMhotmail.com
"It's not IT, it's IS

<ch****@cemgraft.co.uk> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Hello,

Hope this is the right place for this...

I am creating a testing database for components as they come off a
production line.

My reports need to select faults that are found, as well as pass or
fails dependent on a batch number.

I've got this part working fine, the problems start when I want to
total up the records being displayed by batch number.

I need to count up the passes and fails, and have been using this
method in the text field that displays the number on the report:

Passes: =DCount("Pass_Fail","Faults","Pass_Fail = -1")

Fails: =DCount("Pass_Fail","Faults","Pass_Fail = 0")

The table that this data is retrieved from is called 'Faults', and now
I find that the above statement is counting all records in the table,
not just the queried ones.

I have tried changing the 'Faults' bit to 'Pass_Fail_Test' which is my
query name, but then I just get errors and nothing more :(

Any help would be much appreicated :)

Chris.

[chrisc]

Thanks for the responses, turned out it had something to do with dcount
not liking records after it had been filtered with a parameter, in this
case the Batch Number..

So with a little SQL and some messing about its working now!

Now for my next little problem, which doesnt belong in this forum so
I'll put it in the right one.

Thanks again,

Chris.