Need report to show records not matching combinations listed in table 1

[irethedo]

I have set up a table with the following data:

category type
======== =====
color blue
color red
color yellow
shape square
shape circle
shape triangle
texture smooth
texture rough

I would like to create a report that reports any record in my table where the category and type do not match the combinations shown in the table above. Can this be done easily and if so how?


Thank you for your help with this!

 

[BoxHead]

Generally speaking, forms are for collecting data, tables are for storing data, queries are for manipulating data and reports are for displaying data.

I'd suggest using a query on the table you are checking and add a column that counts how many times the Type value in the table appears in the Category table you posted. The DCount function can do that.

 SELECT * , DCount("[Type]","CategoryTableName","[Type] = '" & [tblIWantToCheck].[TypeField] & "'")
FROM tblIWantToCheck

If the type field in the table you're checking is 'blue', 'red', 'yellow', 'square', 'circle', 'trangle', 'smooth' or 'rough' the value returned by DCount will be 1. If it does not match one of these, the value will be zero.

Set the criteria to this field to zero and base your report on that query.


HTH


John







When Galileo theorized that Aristotle's view of the Universe contained errors, he was labeled a fool.
It wasn't until he proved it that he was called dangerous.

 

[irethedo]

Thanks for the reply but I am not sure if what you suggested is what I am looking for as I am not trying to show records that do not have a specific type but I am trying to make sure that the types that I do have match up with another category (field) as defined by my table...

An "after the fact" table checking routine is really what I am looking for where I have some 20 categories and the number of types of each category varies but each type is exclusine to a certain category.

What I am finding is that because of data entry problems (that we have since resolved) our table has some incorrect data where the categories and types do not match up which is causing problems with some of our reports.

We have added a routine (

http://www.tek-tips.com/viewthread.cfm?qid=1197753&page=1)

based upon the table shown in my original post which limits the combinations of what is entered byt the pairs in the table and now I am looking for a way to query the table for any instances where these combinations do not exist.

 

[BoxHead]

I had assumed that the colors were colors and the shapes were shapes, but from your second post it seems that you may have data entered where the Shape is Red and the texture is Square and the Color is Rough.

Same sort of thing

SELECT * , DCount(*,"CategoryTableName","[Type] = '" & [tblIWantToCheck].[TypeField] & "' And [Category] = '" & [tblIWantToCheck].[CategoryField] & "'")
FROM tblIWantToCheck

Again, the results that show zero mean that the values in the table you are checking do not exist in any records in the Category table you have made.


HTH

John

 

[irethedo]

thanks John-

I tried this but I keep getting a syntax error when I run this query so I must have transcribed something wrong....

The error is:
Syntax error (missing operator) in query expression 'Dcount(*, "allowable",'[partnum]= " & [FPY Table].[Assembly Number] & " And [Operation] = " & [FPY Table].[Operation] & "")'.

You suggested:
--------------
SELECT * , DCount(*,"CategoryTableName","[Type] = '" & [tblIWantToCheck].[TypeField] & "' And [Category] = '" & [tblIWantToCheck].[CategoryField] & "'")
FROM tblIWantToCheck

Here is what I have:
-------------------
SELECT *, DCount(*,"allowable","[partnum] = '" &
[FPY Table].[Assembly Number] & "' And [Operation] = '"
& [FPY Table].[Operation] & "'")
FROM [FPY Table];

My "category table" name is "allowable".
Within the "category table" my type field is "partnum"
and the operation field is "operation"

The table holding the records is "FPY Table".
Within the 'FPY Table" my type field is "Assembly Number"
and my field is "Operation".

What did I leave out?

 

[BoxHead]

AARRRGGGHHH!


I'm sorry!

The Asterisk in the DCount needs to be in quotes.

DCount("*","allowable"....

Let me know. That should be the syntax error.

 

[irethedo]

Thanks John-

That "*" cleared up that syntax error and I can run the query now- it looks like it is going to work just fine- thanks!

 

[BoxHead]

You're very welcome. Sorry for the miscue.

 

[irethedo]

Say John-

I thought this was working but it isn't showing the mismatches like I thought it was...

I have the following:

SELECT "*", DCount(*,"allowable","[partnum] = '" &
[FPY Table].[Assembly Number] & "' And [Operation] = '"
& [FPY Table].[Operation] & "'")
FROM [FPY Table];


It actually includes all records and I am wondering if it is missing something to point to the operation field instead of:

And [Operation] = '" - should point to the operation field from the allowable table?

I tried{

And "allowable",[Operation] = '"

and:

And [allowable].[Operation] = '"

but both give me syntax errors

any ideas?

thanks again

 

[BoxHead]

A couple of things,

The asterisk that should be enclosed in quotes is the one in the Dount function:

SELECT *, DCount("*","allowable","[partnum] = '" &
[FPY Table].[Assembly Number] & "' And [Operation] = '"
& [FPY Table].[Operation] & "'")
FROM [FPY Table];


The query should return all records from the table, [FPY Table]. The DCount value should tell you whether the values are valid.

The query is essentialy saying, Show me all of the records from [FPY Table] and for each one, count how many times the partnumber and operation name show up together in the [allowable] table.

The DCount statement should return zero if the combination of Part Number and Operation does not exist in a record in the allowable table.

When you run the query, what sort of values are being returned for the DCount statement?

 

[irethedo]

Thanks for the reply John-


This query is to be used to find bad records in my table which may include null fields or mismatches between the part number and operation...

The SQL view of the total query looks like this:

SELECT [FPY Table].Date, [FPY Table].ID, [FPY Table].Operation, [FPY Table].[Assembly Number], DCount("*","allowable","[partnum] = '" & [FPY Table].[Assembly Number] & "'' And [Operation] = '" & [FPY Table].Operation & "'") AS Expr1, [FPY Table].DTS, operator.Name, [FPY Table].TermID FROM [FPY Table] INNER JOIN operator ON [FPY Table].Operator = operator.Operator
WHERE ((([FPY Table].ID) Is Null)) OR ((([FPY Table].Operation) Is Null)) OR ((([FPY Table].[Assembly Number]) Is Null)) OR ((([FPY Table].DTS) Is Null));


When I run the query I get the following error message when I click on the dcount field:

syntax error (missing operator) in query expression '[partnum]= '17xx-xxxx" and [operation] 16 - pump test"
=========
hence my confusion....