Real Easy Microsoft ACCESS SQL

[llafretaw]

Hi I'm trying to do some SQL in microsoft access for a query and am running into some problems. I'm trying to find ensure that all values in a particular field are the same.
My first query is a sub query and simply finds all distinct values for this field. The main query should insert all the values into another table should the number of rows returned by the sub query (which it references is greater than 1). I have tried messing around with the Having count > 1 and apparently Access doesn't have @rowcount variable either.
My general syntax of my code is something like this but doesn't work properly:
INSERT INTO FailuresTable
SELECT MainTable.*
FROM MainTable
WHERE (MainTable.[Value1] IN (SELECT sqrySameData.*
FROM sqrySameData))
AND (sqrySameData.[Value1] Having Count >1);

Any help would be much appreciated

 

[PaulBricker]

How about changing this line
AND (sqrySameData.[Value1] Having Count >1);
to this
AND (Not IsNull(sqrySameData.[Value1]));

Haven't tried it but the logic read's "if something isn't null then there must be something there"

Maybe?

Paul

 

[llafretaw]

It was if there was more than one record returned then I wanted to insert all the values into another table, however having a Null value returned by the sub query is allowed, and nothing will be moved from one table to another, but cheers anyway.