Updateable Query Problem

[Cathy]

I have a table with an autonum primary key. I'm unable to create a key using
several fields because two of the fields which should be part of the key can
be null.

I am using this table as a temp table and need to test the data for possible
user errors. I've created an error# field in the table where I would like to
store the error # information so I can report the error row and reason to the
user. Then the user can use that information to correct the record.

One of the 'tests' I would like to conduct is a Duplicate Check. I have
created a query which groups and counts the records (without the primary
key). I recognize that this query is creating my updateable query problem.

How else can I check for dups?

Thank you,
Cathy

 

[vanderghast]

You can define an index on multiple field, and specify that this index must
enforce uniqueness.

In table design view, open the index sheet,
create a new index on a new row, supplying an index name, then, under field
name, supply one of the field,
go to the next row, leave the index name blank, and under field name, supply
the second field,
repeat as required.
Move back to the first line of the index definition, and in the bottom,
change Unique from No to Yes.

Note that if (F1, F2) are in an index with UNIQUE constraint, then:


f1 f2
aaa null
aaa null


is allowed, with JET, but not with MS SQL Server.




Vanderghast, Access MVP

 

[John W. Vinson]

I have a table with an autonum primary key. I'm unable to create a key using
several fields because two of the fields which should be part of the key can
be null.

I am using this table as a temp table and need to test the data for possible
user errors. I've created an error# field in the table where I would like to
store the error # information so I can report the error row and reason to the
user. Then the user can use that information to correct the record.

One of the 'tests' I would like to conduct is a Duplicate Check. I have
created a query which groups and counts the records (without the primary
key). I recognize that this query is creating my updateable query problem.

How else can I check for dups?

Thank you,
Cathy

If a field is null, what constitutes a duplicate? NULL means "this value is
unknown, undefined", so two records both with NULL values in a field (and
otherwise identical) will not be seen as duplicates.

You can a unique multifield Index that isn't a Primary Key, and that index can
be set to ignore nulls; or you can use the NZ() function to compare records
with nulls. Post the relevant fieldnames if you'ld like help.

 

[Cathy]

That is a great suggestion. But I do want to allow the duplicates to come
into the table, then alert the users that they exist. These dups will not be
allowed to flow from the temp table to the final table.

My concern is that these users are not DB knowledgable enough to watch
record counts. They will not notice, even if I attempt to draw attention to
the fact that not all of their records have loaded. They will understand the
process if I show them the duplicates on the table.

Is there a way I can achieve this?

 

[Cathy]

John,

The fields that would make up the primary key, if not for this null issue,
would be:

Fiscal Year
Period
OrganizationUnit
Type (Forecast, Budget)
ForecastIndicator (1+11, 2+10, 3+9, ect) <- Only exists for Forecast, Null
for Budget
Version <-Only exists for Forecast, Null for Budget

So what I am calling a duplicate is any two records with the same values in
the above fields. It can have a different dollar amount.

How do I update an error field when this happens?

Thank you!

 

[John W. Vinson]

One of the 'tests' I would like to conduct is a Duplicate Check. I have
created a query which groups and counts the records (without the primary
key). I recognize that this query is creating my updateable query problem.

How else can I check for dups?

You could display the duplicates, or the count of duplicates, in a subform or
a popup form. I'm not certain how you're doing this test and how you want it
presented to the user though!

 

[vanderghast]

You can then make a temp table without the index not allowing duplicated
(pair of ) fields, and the final table with an index not allowing duplicated
pairs. The "flow" from the temp table to the final table can be an append
query which will reject duplicated records ( nnn records not appended due
to unique constraint violation) or roll back the whole operation as a whole,
not allowing any flow from the tables if ONE record is duplicated, the final
detail is your choice, you can even do it with with VBA and your own
transaction, rather than using the one automatically supplied to you, if you
prefer greater control on the 'dialogs'.


Vanderghast, Access MVP

 

[david]

What exactly is your "updateable query problem"?

I see that you have created a count query, and that it
is not updatable. Is that a problem? I don't see how,
but if so, perhaps you could use a DCOUNT field.

(david)

 

[vanderghast]

You can also append the records with a query, without error message than
"nnn records have not been appended", like:

INSERT INTO...
SELECT tableName AS a
FROM ...
WHERE 1 = ( SELECT COUNT(*)
FROM tablename AS b
WHERE a.f1 = b.f1 AND a.f2=b.f2)



where f1 and f2 are the fields which defines who is a duplicated record, or
not.


A query with an aggregate is not updateable, unless that aggregate appears
(as a sub query) in the WHERE clause, like here.




Vanderghast, Access MVP

 

[John W. Vinson]

How do I update an error field when this happens?

Well... I would recommend that you not HAVE such a field. It's derived data;
if you store it in your table then its value will be WRONG the moment some
change is made to some different record in the table. You would need to run a
massive complicated update query every time you change *ANYTHING* involving
one of these fields, to edit the Error field in all the other affected
records.

Just use a Query to find the duplicates, and don't store the error value at
all.