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
|
|||
|
|||
Record Validation?? for newbie
I have designed a database in Access 2003 which we will use for scheduling.
I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
#2
|
|||
|
|||
Record Validation?? for newbie
How are the available locations supplied to the user? If it's by combo-box,
you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
#3
|
|||
|
|||
Record Validation?? for newbie
actually the locations are the field labels for the schedule table. I used
lookup for each fields data type to lookup the names from an employee table "Dennis" wrote: How are the available locations supplied to the user? If it's by combo-box, you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
#4
|
|||
|
|||
Record Validation?? for newbie
Your statement 'So there are 45 fields that need to be compared for
duplicates' suggests an un-normalized table structure. In the words of MVP Jeff Boyce, you have "committed spreadsheet". If your tables were properly normalized, you might have a much easier time finding duplicates and performing other tasks. -- Duane Hookom Microsoft Access MVP "CDChilds" wrote: actually the locations are the field labels for the schedule table. I used lookup for each fields data type to lookup the names from an employee table "Dennis" wrote: How are the available locations supplied to the user? If it's by combo-box, you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
#5
|
|||
|
|||
Record Validation?? for newbie
So I should break the schedule up into smaller tables? Its probably a little
too late for that, oooppss. I will remember that next time though "Duane Hookom" wrote: Your statement 'So there are 45 fields that need to be compared for duplicates' suggests an un-normalized table structure. In the words of MVP Jeff Boyce, you have "committed spreadsheet". If your tables were properly normalized, you might have a much easier time finding duplicates and performing other tasks. -- Duane Hookom Microsoft Access MVP "CDChilds" wrote: actually the locations are the field labels for the schedule table. I used lookup for each fields data type to lookup the names from an employee table "Dennis" wrote: How are the available locations supplied to the user? If it's by combo-box, you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
#6
|
|||
|
|||
Record Validation?? for newbie
So I should break the schedule up into smaller tables?
Break your table into multiple records instead of multiple fields. Use a union query like this -- SELECT Employee, WorkDate, Location1 AS Location FROM YourTable UNION ALL SELECT Employee, WorkDate, Location2 AS Location FROM YourTable UNION ALL SELECT Employee, WorkDate, Location3 AS Location FROM YourTable --- through all 45 location fields --- UNION ALL SELECT Employee, WorkDate, Location45 AS Location FROM YourTable; -- KARL DEWEY Build a little - Test a little "CDChilds" wrote: So I should break the schedule up into smaller tables? Its probably a little too late for that, oooppss. I will remember that next time though "Duane Hookom" wrote: Your statement 'So there are 45 fields that need to be compared for duplicates' suggests an un-normalized table structure. In the words of MVP Jeff Boyce, you have "committed spreadsheet". If your tables were properly normalized, you might have a much easier time finding duplicates and performing other tasks. -- Duane Hookom Microsoft Access MVP "CDChilds" wrote: actually the locations are the field labels for the schedule table. I used lookup for each fields data type to lookup the names from an employee table "Dennis" wrote: How are the available locations supplied to the user? If it's by combo-box, you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
#7
|
|||
|
|||
Record Validation?? for newbie
On Sep 8, 3:32*pm, CDChilds
wrote: So I should break the schedule up into smaller tables? *Its probably a little too late for that, oooppss. *I will remember that next time though "Duane Hookom" wrote: Your *statement 'So there are 45 fields that need to be compared for duplicates' suggests an un-normalized table structure. In the words of MVP Jeff Boyce, you have "committed spreadsheet". If your tables were properly normalized, you might have a much easier time finding duplicates and performing other tasks. -- Duane Hookom Microsoft Access MVP "CDChilds" wrote: actually the locations are the field labels for the schedule table. I used lookup *for each fields data type to lookup the names from an employee table "Dennis" wrote: How are the available locations supplied to the user? If it's by combo-box, you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. * I am looking for the best solution to identify duplicate fields within the same record. *The values in the work location fields will come from an employee table using lookup. *I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. *There are about 45 different work locations within our company. *So there are 45 fields that need to be compared for duplicates. *Is there a simple way to do this? *Should I use a macro or record validation rules? *Any advice would be greatly appreciated If you choose not to normalize, querying could get ugly fast. What if you have to find where someone is working? |
#8
|
|||
|
|||
Record Validation?? for newbie
Normalizing doesn't mean to break a table into smaller tables. Quite the
opposite. For instance, if you have a table with fields like: tblSchedule ================ WorkPosition Sunday Monday Tuesday Wednesday Thursday Friday Saturday With names of worker entered into the appropriate weekday field, you would change the structure to something like: tblSchedule ================ WorkPosition WorkerName DayOfWork Duane Hookom Microsoft Access MVP "CDChilds" wrote: So I should break the schedule up into smaller tables? Its probably a little too late for that, oooppss. I will remember that next time though "Duane Hookom" wrote: Your statement 'So there are 45 fields that need to be compared for duplicates' suggests an un-normalized table structure. In the words of MVP Jeff Boyce, you have "committed spreadsheet". If your tables were properly normalized, you might have a much easier time finding duplicates and performing other tasks. -- Duane Hookom Microsoft Access MVP "CDChilds" wrote: actually the locations are the field labels for the schedule table. I used lookup for each fields data type to lookup the names from an employee table "Dennis" wrote: How are the available locations supplied to the user? If it's by combo-box, you could write the query such taht only unassigned locations populate the drop-down. that way, it would be impossible to double-assign someone. "CDChilds" wrote: I have designed a database in Access 2003 which we will use for scheduling. I am looking for the best solution to identify duplicate fields within the same record. The values in the work location fields will come from an employee table using lookup. I just want to have some type of msgbox showing the person scheduling that they may have used the same employee in two different work locations. There are about 45 different work locations within our company. So there are 45 fields that need to be compared for duplicates. Is there a simple way to do this? Should I use a macro or record validation rules? Any advice would be greatly appreciated |
Thread Tools | |
Display Modes | |
|
|