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
|
|||
|
|||
duplicate query within an If statement
Hello All
I have a data base which I have inherited and Im trying to sort ... In my data base I have an employee number which is unique to the individual, however over time this has been 'overlooked' and now Im using a data base I would hope to have this as the Primary key ... The problem is ... some records have been created with no employee number, wrongly entered employee number, something totally spurious and finally entered more than once. I have done the provided Duplicate query and confirm 100+ duplicates. I figured for analysis purposes that I would create another colum on my master table which would provide a defined output depending on the error which would subseqently become searchable, however I cant fathom how to use the duplicate query within the If statement ... My code so far is EmpNoStatus: IIf((IsNull(PERSONNEL![Employee No])),"NO EMPNO",IIf((Len(PERSONNEL![Employee No])6),"INVALID","OK")) I think I need to replace the OK with another If loop for duplicates Any help would be grateful Regards |
#2
|
|||
|
|||
Alan wrote: EmpNoStatus: IIf((IsNull(PERSONNEL![Employee No])),"NO EMPNO",IIf((Len(PERSONNEL![Employee No])6),"INVALID","OK")) I think I need to replace the OK with another If loop for duplicates 'Loop' is the wrong mental model. Think in terms of sets. If you want to include another set (e.g. your set of duplicates) in your resultset, use UNION. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Error when running Query, but not Report | Blaze | Running & Setting Up Queries | 29 | September 9th, 2005 05:40 AM |
SQL query showing diff between actual and budget | Bon | Running & Setting Up Queries | 3 | August 25th, 2005 12:07 PM |
Executing 'Transform' SQL in VBA | Leo | General Discussion | 20 | October 28th, 2004 10:42 AM |
Here's a shocker | Mike Labosh | General Discussion | 2 | October 26th, 2004 05:04 PM |
Access 2000 query SQL statement into VBA code | Clint | Running & Setting Up Queries | 1 | June 10th, 2004 01:33 PM |