Proof and Control

[Stacy]

Hi!

I am using Access 2000.
And I apologize for my ramblings up front....

I have a requirements table.
I have a field in that table that lists "type" (there are 5)
I have a field next to that for Divison (there are 3)

For that requirement, I want to make sure every Requirement has a
record for all 5 types.
Is there a simple query that I can build to see when one or more types
are missing?

For example:
Requirement Type Division
01 TypeA 01
01 TypeA 02
01 TypeB
01 TypeC
01 TypeD
01 TypeE
02 TypeA
02 TypeC
02 TypeE

I want to create a query that shows me that Requirement 02 is missing
Type B and Type D.

Thanks in advance for any assistance.
Stacy

 

[Bob Quintal]

Hi!

I am using Access 2000.
And I apologize for my ramblings up front....

I have a requirements table.
I have a field in that table that lists "type" (there are 5)
I have a field next to that for Divison (there are 3)

For that requirement, I want to make sure every Requirement
has a record for all 5 types.
Is there a simple query that I can build to see when one or
more types are missing?

For example:
Requirement Type Division
01 TypeA 01
01 TypeA 02
01 TypeB
01 TypeC
01 TypeD
01 TypeE
02 TypeA
02 TypeC
02 TypeE

I want to create a query that shows me that Requirement 02 is
missing Type B and Type D.

Thanks in advance for any assistance.
Stacy

You can use a crosstab query for this, with a trick. Crosstabs
have one or more columns on hte left, and a row at the top made
from a column at the top, with a value (sum or average or count)
at the intersection.

The trick is to use requirement as the row, type as the column
and count of type as the value.

 

[Stacy]

You can use a crosstab query for this, with a trick. Crosstabs
have one or more columns on hte left, and a row at the top made
from a column at the top, with a value (sum or average or count)
at the intersection.

The trick is to use requirement as the row, type as the column
and count of type as the value.

Oh my God! That's awesome!!!!!
Thanks so much!!!!!
Stacy

 

[John W. Vinson]

Hi!

I am using Access 2000.
And I apologize for my ramblings up front....

I have a requirements table.
I have a field in that table that lists "type" (there are 5)
I have a field next to that for Divison (there are 3)

For that requirement, I want to make sure every Requirement has a
record for all 5 types.

Well, this obviously cannot be a validation rule in the table, since you have
to start with an empty table, then add one record, and so on!

Is there a simple query that I can build to see when one or more types
are missing?

For example:
Requirement Type Division
01 TypeA 01
01 TypeA 02
01 TypeB
01 TypeC
01 TypeD
01 TypeE
02 TypeA
02 TypeC
02 TypeE

I want to create a query that shows me that Requirement 02 is missing
Type B and Type D.

The quick way to do it is to Group By on a Group By:

SELECT Requirement, Type
FROM (SELECT DISTINCT Requirement, Type FROM Requirements AS X)
GROUP BY Requirement, Type
HAVING Count(*) < 5;

This will find all REQUIREMENTS with fewer than five distinct types.

You may want (if you don't already have one) a table of Types.

John W. Vinson [MVP]

 

[Jamie Collins]

Well, this obviously cannot be avalidationrule in the table, since you have
to start with an empty table, then add one record, and so on!

Indeed. A 'Table Validation Rule' is actually row-level, therefore
cannot reference other rows. A truly table-level CHECK constraint
would suffer the multiple assignment problems you mention because
Access/Jet does not support deferrable constraints (which would allow
constraints to be checked on a transaction basis) plus the engine is
fundamentally flawed in that it erroneously checks constraints on a
row-by-row, table-by-table basis (should be on a SQL statement
basis).

If a constraint is requirement for you then consider breaking your own
'don't commit spreadsheet' rule and have five NOT NULL columns.

However, I suspect it is not a requirement in the OP's case,
relational division ('suppliers who supply all parts') being a *query*
requirement commonly encountered...

SELECT Requirement, Type
FROM (SELECT DISTINCT Requirement, Type FROM Requirements AS X)
GROUP BY Requirement, Type
HAVING Count(*) < 5;

This will find all REQUIREMENTS with fewer than five distinct types.

You may want (if you don't already have one) a table of Types.

Yes, there should IMO be a lookup table for these values. Relational
division ('Requirements that have all Types') as follows:

SELECT DISTINCT R1.Requirement
FROM BarRequirements AS R1
WHERE NOT EXISTS
(SELECT *
FROM FooTypes
WHERE NOT EXISTS
(SELECT *
FROM BarRequirements AS R2
WHERE R1.Requirement = R2.Requirement
AND R2.foo_type = FooTypes.foo_type));

To find the 'Requirements that do not have all Types', change the top
level NOT EXISTS to EXISTS.

Jamie.

--