Getting count of field occurances (trying again)

[Randall Arnold]

I posted this question recently and thought I'd solved it myself but it turns out my solution has bugs. I'm hoping that someone can help.

I have two tables involved in my query. One is a list of audit categories, as shown:

Table 1 Category
Audit Checklist
Housekeeping
MIS
PMs
Procedure
Process
Prod Handling
Prod ID
Safety
Specs


The other table is a list of audit failures, that is essentially composed of a record ID (unique bigint ), a date (can be repeated), the category of an audit failure (nvarchar pulled from Table 1) and the defect type (nvarchar pulled from another table I'm not concerned with at this point).

On any given day, there can be numerous categorical failure records. Any category can be represented up to 4 times, or not at all depending on failures. In the failure table there will only be failures shown so I am not concerned with days of zero failures. So the group of records I retrieve per day can have anywhere from 1 to 40 rows.

I want my result set to count the number of times a category is represented per day. This also includes a count of zero (0) for the days that any category had no failures.

I have not been able to get a query to do this accurately. I can easily count the number of times a categorical failure has been entered per day, but I can't count the number of times per day that no failure occurred for any given categories. I have tried all sorts of logic and joins from all sorts of examples and nothing gets me there.

I have to believe this is possible-- I just need some guidance. Any ideas?

Thanks,

Randall Arnold

 

[Crystal]

Hi Randall,

If you don't have a definition table for failures, make
another query

Name --> FailureList
SELECT DISTINCT Failure_field FROM
Tablename_with_Categories_and_Failures_and_Dates

In another query, use:

1. Category
2. FailureList
3. Tablename_with_Categories_and_Failures_and_Dates

link 1 to 3 --> show all records in 1
link 2 to 3 --> show all records in 2

hope this gives you enough of a start


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Randall Arnold]

Crystal,

Thank you for taking the time to reply.

In this particular case, I am not worried about listing failures. In fact,
that will actually "corrupt" my desired result set. I am looking to count
only categories at this level of activity so I intentionally omit the
failure types per se. I just need the count of categories per defect
opportunity.

Given that, the very first thing I tried was what you suggested (without the
failure list). I linked the category table to the FailuresView table and
selected the option to show all category type records but that did not
produce the desired result. If a category had no defects recorded for a
certain date, then that category does not come up in my result set--
therefore I can't perform a count on it. No matter what I do, the result
set only contains rows where categories are represented.

This one has been driving me nuts for several weeks now. I thought it would
be fairly simple but none of the advice I have read or methods I have
attempted on my own have produced the desired results. There is always at
least one problem with any approach tried. I sure wish someone had an
answer to this... it's impacting my ability to do my job...

Randall Arnold

 

[Crystal]

Hi Randall,

I am new to newsgroups... is there a way to post your
database? It would be a lot easier for me to see what is
going on.

Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com

 

[Randall Arnold]

Thanks, Crystal, but I won't be able to post this database. It's a very
large SQL Server 2000 database that's a mess (not my design). It's only due
to the poor design that I'm having these problems; I can't change it at this
point and I'm having to create workarounds.

Randall Arnold

 

[strive4peace]

Hi Randall,

Could you email me (or post in your message) a JPG of a
screen dump of a relationship diagram with the tables and
queries that you are using?

1. add the tables and queries you are using
2. Make sure to stretch the fieldlists out so everything shows.
3. rearrange the fieldlists so that the "1" side of the
relationship is on the left and the "many" on the right so
the diagram flows as your data does.

then press the PrintScreen key on your keyboard

open Paint (Start, Programs, Accessories, Paint)

click on the selection tool (rectangle at top right) and
drag around the part of the screen that you need
copy
close file
paste
save as... file type --> JPG
and give it a name

JPG files are about 1/10th the size of BMP files


Have an awesome day

Warm Regards,
Crystal

MVP Microsoft Access
strive4peace2006 at yahoo.com