Updateable Query Problem
in [Queries]
|
Prev: First 10 numbers
Next: Help with expression for Qtr
From: Cathy on 26 Apr 2010 16:16 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 26 Apr 2010 16:31 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" <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: John W. Vinson on 26 Apr 2010 17:03 On Mon, 26 Apr 2010 13:16:07 -0700, Cathy <Cathy(a)discussions.microsoft.com> 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]
From: Cathy on 26 Apr 2010 17:19 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" <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: Cathy on 26 Apr 2010 18:18
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 <Cathy(a)discussions.microsoft.com> > 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] > . > |