If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Updateable Query Problem
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 |
#2
|
|||
|
|||
Updateable Query Problem
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 "Cathy" wrote in message ... 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 |
#3
|
|||
|
|||
Updateable Query Problem
On Mon, 26 Apr 2010 13:16:07 -0700, Cathy
wrote: 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. -- John W. Vinson [MVP] |
#4
|
|||
|
|||
Updateable Query Problem
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? "vanderghast" wrote: 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 "Cathy" wrote in message ... 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 |
#5
|
|||
|
|||
Updateable Query Problem
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" wrote: On Mon, 26 Apr 2010 13:16:07 -0700, Cathy wrote: 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. -- John W. Vinson [MVP] . |
#6
|
|||
|
|||
Updateable Query Problem
On Mon, 26 Apr 2010 13:16:07 -0700, Cathy
wrote: 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! -- John W. Vinson [MVP] |
#7
|
|||
|
|||
Updateable Query Problem
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 "Cathy" wrote in message ... 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? |
#8
|
|||
|
|||
Updateable Query Problem
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) "Cathy" wrote in message ... 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 |
#9
|
|||
|
|||
Updateable Query Problem
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 "vanderghast" vanderghast@com wrote in message ... 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 "Cathy" wrote in message ... 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? |
#10
|
|||
|
|||
Updateable Query Problem
On Mon, 26 Apr 2010 15:18:01 -0700, Cathy
wrote: 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. -- John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|