badboytim

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am taking over a database for production. The manager wants to see from a
QA table the counts of product that have a "pass" of "fail" attached to its
inspection. I found a "counta" function in the microsoft support pages, but
the QBE form does not recognize this function. Should I try dcount to look
at the table and then count pass/fail per productline? Thanks.
 
No idea what your underlying data structure looks like, so I could only
guess at a good way to query it...

More info, please...

Jeff Boyce
Microsoft Office/Access MVP
 
I have a table of different products inspected on a pass or fail basis
recorded by product line and lot # with associated quantities. The lots are
able to be reworked, so a product may have multiple entries, before being
released to a customer.

I am able to use the totals row to group the product #'s and I can see where
there are multiple entries for various rework and inspection activities.

My boss wants to see how many pass or fails records are owned by a product
#, regardless of lot, and I am having a real headache trying to populate a
query with a new nump(number passed) and numf (number failed). I have been
able to use 'dcount' to get all the records counted, and another Access help
site got me the count for failed, but nor for pass AND failed records. My
attempts at trying to be tricky and just subtract one from the other were
spotty at best(got some erroneous results I still need to doublecheck, and my
boss has been telling me that he could've counted the records by hand for as
long as I have been working on the problem. I also tried the 'counta'
function,since it sounded like it would count text lines, but I got an error
from the QBE grid, saying that Access 2003 did not recognize the 'counta'
function. This should be pretty simple, no? Or am I overlooking something
really simple.
 
Hi,


We don't have the layout of your original data. Is it like:


ProductNumber, DateTimeStampOfTheTest, TrueIfPassFalseIfFail ' fields
name


if so, then

===============

SELECT ProductNumber,
-SUM(-TrueIfPassFalseIfFail) AS numberPassed,
COUNT(*)+SUM(-TrueIfPassFalseIfFail) AS numberFailled

FROM myTable


GROUP BY ProductNumber

================


should do.



Hoping it may help,
Vanderghast, Access MVP
 
I actually got this SQL from a friend last night when we were discussing the
problem ang got the numbers to output correctly. . . I will try what you
have here and report back. Thanks for the assist.

SELECT Prod_Code, IIF([PassFail] = "Pass",1.0) AS Pass, IIF([PassFail] =
"Fail",1,0) As Fail
FROM [T_QA audit]
WHERE ((([T_QA audit].QADTE) Between #1/1/2005# And #5/1/2006#))

Your group by query then becomes:

SELECT PROD_CODE, Sum([PASS]) AS SumOfPass, Sum([Fail]) AS SumOfFail
FROM [qryPassFail]
GROUP BY [T_QA audit].PROD_CODE;
 
Michael,

I got a few syntax errors, since I a trying to learn SQL, I will take a
closer look at your example and see if I can get it to work. Back to one of
my original questions:

Is there a function that would count the strings, such as "pass" of "fail",
or would there be a more proper way to flag these responses, so that it would
be easier to build a query via the QBE grid, rather than have to use SQL for
the desired result?

badboytim said:
I actually got this SQL from a friend last night when we were discussing the
problem ang got the numbers to output correctly. . . I will try what you
have here and report back. Thanks for the assist.

SELECT Prod_Code, IIF([PassFail] = "Pass",1.0) AS Pass, IIF([PassFail] =
"Fail",1,0) As Fail
FROM [T_QA audit]
WHERE ((([T_QA audit].QADTE) Between #1/1/2005# And #5/1/2006#))

Your group by query then becomes:

SELECT PROD_CODE, Sum([PASS]) AS SumOfPass, Sum([Fail]) AS SumOfFail
FROM [qryPassFail]
GROUP BY [T_QA audit].PROD_CODE;


Michel Walsh said:
Hi,


We don't have the layout of your original data. Is it like:


ProductNumber, DateTimeStampOfTheTest, TrueIfPassFalseIfFail ' fields
name


if so, then

===============

SELECT ProductNumber,
-SUM(-TrueIfPassFalseIfFail) AS numberPassed,
COUNT(*)+SUM(-TrueIfPassFalseIfFail) AS numberFailled

FROM myTable


GROUP BY ProductNumber

================


should do.



Hoping it may help,
Vanderghast, Access MVP
Click to expand...
Click to expand...
 
Hi,


you can definitively use, as you mentioned:

==========
SELECT prod,
SUM( iif( passfail="pass", 1, 0) AS NumberOfPass,
SUM( iif(passfail="fail", 1, 0) As numberOfFail
FROM myTable
GROUP BY prod
==========


Hoping it may help,
Vanderghast, Access MVP


badboytim said:
Michael,

I got a few syntax errors, since I a trying to learn SQL, I will take a
closer look at your example and see if I can get it to work. Back to one
of
my original questions:

Is there a function that would count the strings, such as "pass" of
"fail",
or would there be a more proper way to flag these responses, so that it
would
be easier to build a query via the QBE grid, rather than have to use SQL
for
the desired result?

badboytim said:
I actually got this SQL from a friend last night when we were discussing
the
problem ang got the numbers to output correctly. . . I will try what you
have here and report back. Thanks for the assist.

SELECT Prod_Code, IIF([PassFail] = "Pass",1.0) AS Pass, IIF([PassFail] =
"Fail",1,0) As Fail
FROM [T_QA audit]
WHERE ((([T_QA audit].QADTE) Between #1/1/2005# And #5/1/2006#))

Your group by query then becomes:

SELECT PROD_CODE, Sum([PASS]) AS SumOfPass, Sum([Fail]) AS SumOfFail
FROM [qryPassFail]
GROUP BY [T_QA audit].PROD_CODE;


Michel Walsh said:
Hi,


We don't have the layout of your original data. Is it like:


ProductNumber, DateTimeStampOfTheTest, TrueIfPassFalseIfFail '
fields
name


if so, then

===============

SELECT ProductNumber,
-SUM(-TrueIfPassFalseIfFail) AS numberPassed,
COUNT(*)+SUM(-TrueIfPassFalseIfFail) AS numberFailled

FROM myTable


GROUP BY ProductNumber

================


should do.



Hoping it may help,
Vanderghast, Access MVP




I have a table of different products inspected on a pass or fail basis
recorded by product line and lot # with associated quantities. The
lots
are
able to be reworked, so a product may have multiple entries, before
being
released to a customer.

I am able to use the totals row to group the product #'s and I can
see
where
there are multiple entries for various rework and inspection
activities.

My boss wants to see how many pass or fails records are owned by a
product
#, regardless of lot, and I am having a real headache trying to
populate a
query with a new nump(number passed) and numf (number failed). I
have
been
able to use 'dcount' to get all the records counted, and another
Access
help
site got me the count for failed, but nor for pass AND failed
records. My
attempts at trying to be tricky and just subtract one from the other
were
spotty at best(got some erroneous results I still need to
doublecheck, and
my
boss has been telling me that he could've counted the records by hand
for
as
long as I have been working on the problem. I also tried the
'counta'
function,since it sounded like it would count text lines, but I got
an
error
from the QBE grid, saying that Access 2003 did not recognize the
'counta'
function. This should be pretty simple, no? Or am I overlooking
something
really simple.

:

No idea what your underlying data structure looks like, so I could
only
guess at a good way to query it...

More info, please...

Jeff Boyce
Microsoft Office/Access MVP

I am taking over a database for production. The manager wants to
see
from
a
QA table the counts of product that have a "pass" of "fail"
attached to
its
inspection. I found a "counta" function in the microsoft support
pages,
but
the QBE form does not recognize this function. Should I try
dcount to
look
at the table and then count pass/fail per productline? Thanks.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top