Updateable Query Problem
in [Queries]
|
Prev: First 10 numbers
Next: Help with expression for Qtr
From: John W. Vinson on 26 Apr 2010 18:24 On Mon, 26 Apr 2010 13:16:07 -0700, Cathy <Cathy(a)discussions.microsoft.com> 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]
From: vanderghast on 27 Apr 2010 07:58 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" <Cathy(a)discussions.microsoft.com> wrote in message news:44763AE9-784B-4AA4-B699-07F7E61D9D1C(a)microsoft.com... > 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? >
From: david on 27 Apr 2010 08:16 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" <Cathy(a)discussions.microsoft.com> wrote in message news:F567F255-B8E5-40F7-8432-B262A2BB91EB(a)microsoft.com... >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
From: vanderghast on 27 Apr 2010 08:46 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(a)com> wrote in message news:1575CB29-D366-4386-A4E4-8B63B9AE1BAD(a)microsoft.com... > 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" <Cathy(a)discussions.microsoft.com> wrote in message > news:44763AE9-784B-4AA4-B699-07F7E61D9D1C(a)microsoft.com... >> 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? >> >
From: John W. Vinson on 27 Apr 2010 12:37
On Mon, 26 Apr 2010 15:18:01 -0700, Cathy <Cathy(a)discussions.microsoft.com> 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] |