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]