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
|
|||
|
|||
validation on fields with number ranges
I have a database which I inherited and so am somewhat limited in how it was
originally designed. I want to place a validation rule on a field. Basically the application is that I am tracking an identifying number on a product we ship to ensure that I never send the same customer the same number (I cant have duplication within the same customer). I think I've pretty much figured out how to validate what is being sent against what has already been sent by using the dlookup function. The problem is that the field containing the numbers was originally formatted as text (problem 1) and that the numbers were entered as ranges. As an example we shipped 3 items and entered the value "1-3" rather than having one record for each item. Even if I convert the field to numeric, I don't see how I can validate against a range of numbers. any ideas? Also, if I were to redesign this database so that each item sent had its own record, how can these be entered efficiently. In other words I don't want the operator to have to make 3 entries when we send three items (in reality we deal with hundreds at a time, not 3). I want them to be able to make an entry on the form which would populate the appropriate number of records. help!? |
#2
|
|||
|
|||
validation on fields with number ranges
You can use an append query to make 3 entries if your field always has a dash.
Create a table named CountNumber with field CountNUM containing 1 through your maximum ship number. Substitute your table names for [Change Requests] and [Change Request-1] and fields [x] and [y]. INSERT INTO [Change Request-1] ( x ) SELECT CountNumber.CountNUM FROM CountNumber, [Change Requests] WHERE (((CountNumber.CountNUM) Between Val([y]) And IIf(InStr([y],"-")=0,Val([y]),Abs(Val(Mid([y],InStr([y],"-"),99)))))); -- KARL DEWEY Build a little - Test a little "Jason" wrote: I have a database which I inherited and so am somewhat limited in how it was originally designed. I want to place a validation rule on a field. Basically the application is that I am tracking an identifying number on a product we ship to ensure that I never send the same customer the same number (I cant have duplication within the same customer). I think I've pretty much figured out how to validate what is being sent against what has already been sent by using the dlookup function. The problem is that the field containing the numbers was originally formatted as text (problem 1) and that the numbers were entered as ranges. As an example we shipped 3 items and entered the value "1-3" rather than having one record for each item. Even if I convert the field to numeric, I don't see how I can validate against a range of numbers. any ideas? Also, if I were to redesign this database so that each item sent had its own record, how can these be entered efficiently. In other words I don't want the operator to have to make 3 entries when we send three items (in reality we deal with hundreds at a time, not 3). I want them to be able to make an entry on the form which would populate the appropriate number of records. help!? |
#3
|
|||
|
|||
validation on fields with number ranges
A re-design of your schema may be indicated.
Before you start on that you should first complete an analysis of what your application is intended to do, in real-world terms, and document it thoroughly. Get knowledgeable managers and users to participate in detailing their understanding of the whole process. List every requirement surfaced by anyone at all. Add things that you know are required but that no one else mentioned. Once done, get those same managers and users to sign off on your analysis. If they disagree, get the details and amend the analysis as/if required. Base your new design on the new analysis you've performed. The existing application serves as just one resource for you to use in arriving at your design. While you may be able to copy over large chunks from the old design to the new one, be careful that you don't also copy the dysfunctional and limiting elements. Your current situation is that you are trying to retrieve/compare information that may exist only by inference. You haven't explained your process well enough that we can infer the purpose of the elusive number. I'm guessing that it's nothing more than a count of the quantity of unnamed things tracked in the current record. By simply entering the quantity in the record the sum of all of the quantities for this customer can be found by DSum() or an appropriate Query and displayed in a Form or Report. Note that it is *not stored*. If I missed by a long shot then please post back with a more complete description of the real world process and the details of your schema. HTH -- -Larry- -- "Jason" wrote in message ... I have a database which I inherited and so am somewhat limited in how it was originally designed. I want to place a validation rule on a field. Basically the application is that I am tracking an identifying number on a product we ship to ensure that I never send the same customer the same number (I cant have duplication within the same customer). I think I've pretty much figured out how to validate what is being sent against what has already been sent by using the dlookup function. The problem is that the field containing the numbers was originally formatted as text (problem 1) and that the numbers were entered as ranges. As an example we shipped 3 items and entered the value "1-3" rather than having one record for each item. Even if I convert the field to numeric, I don't see how I can validate against a range of numbers. any ideas? Also, if I were to redesign this database so that each item sent had its own record, how can these be entered efficiently. In other words I don't want the operator to have to make 3 entries when we send three items (in reality we deal with hundreds at a time, not 3). I want them to be able to make an entry on the form which would populate the appropriate number of records. help!? |
#4
|
|||
|
|||
validation on fields with number ranges
Thanks for your reply. I'm a little unsure how to apply this to my situation.
You reference 2 different tables and to substitute their names for my tables. However, I only have one table that is relevant to this. It is called "Cards Sent". I basically just have one table which lists card numbers we have sent out to each account. The only other table in the database is the customer info database. "KARL DEWEY" wrote: You can use an append query to make 3 entries if your field always has a dash. Create a table named CountNumber with field CountNUM containing 1 through your maximum ship number. Substitute your table names for [Change Requests] and [Change Request-1] and fields [x] and [y]. INSERT INTO [Change Request-1] ( x ) SELECT CountNumber.CountNUM FROM CountNumber, [Change Requests] WHERE (((CountNumber.CountNUM) Between Val([y]) And IIf(InStr([y],"-")=0,Val([y]),Abs(Val(Mid([y],InStr([y],"-"),99)))))); -- KARL DEWEY Build a little - Test a little "Jason" wrote: I have a database which I inherited and so am somewhat limited in how it was originally designed. I want to place a validation rule on a field. Basically the application is that I am tracking an identifying number on a product we ship to ensure that I never send the same customer the same number (I cant have duplication within the same customer). I think I've pretty much figured out how to validate what is being sent against what has already been sent by using the dlookup function. The problem is that the field containing the numbers was originally formatted as text (problem 1) and that the numbers were entered as ranges. As an example we shipped 3 items and entered the value "1-3" rather than having one record for each item. Even if I convert the field to numeric, I don't see how I can validate against a range of numbers. any ideas? Also, if I were to redesign this database so that each item sent had its own record, how can these be entered efficiently. In other words I don't want the operator to have to make 3 entries when we send three items (in reality we deal with hundreds at a time, not 3). I want them to be able to make an entry on the form which would populate the appropriate number of records. help!? |
#5
|
|||
|
|||
validation on fields with number ranges
Sorry, in an effort to not make a huge paper for someone to read, I have
omitted some obviously necessary details. Basically, here's the situation: I ship access control cards (for building access control). Each card has a number stamped on it from the factory (essentially its ID number) There are only roughly 65,000 numbers available so numbers can be duplicated. I cannot ever send the same card number to the same customer. I always have to make sure that new cards we send them have a unique (to them) number on them. We typically will send anywhere from 10 to several hundred cards at a time. The existing data so far has been entered in the following form in one table. One record per shipment. A field called card numbers which contains a listing of their card numbers. That field has never had validation so is a little messy but is usually in the form of "1-10" (assuming the card numbers were 1-10). Our only method right now of assuring we are not sending duplicate numbers is to manually compare the numbers being sent to all previous records for that customer. Tedious and error prone for sure! I need a way to validate the card numbers being entered against what has been entered already. If creating a single record for each card is necessary I can do that but: 1) Not sure if there's a way to import what I have now without doing all manual entry 2) Ongoing, how can we enter the data in a form similar to the "1-10" on a form but have it populate multiple records? I just can't have someone typing 100 record entries when we ship 100 cards. Thanks in advance! "Larry Daugherty" wrote: A re-design of your schema may be indicated. Before you start on that you should first complete an analysis of what your application is intended to do, in real-world terms, and document it thoroughly. Get knowledgeable managers and users to participate in detailing their understanding of the whole process. List every requirement surfaced by anyone at all. Add things that you know are required but that no one else mentioned. Once done, get those same managers and users to sign off on your analysis. If they disagree, get the details and amend the analysis as/if required. Base your new design on the new analysis you've performed. The existing application serves as just one resource for you to use in arriving at your design. While you may be able to copy over large chunks from the old design to the new one, be careful that you don't also copy the dysfunctional and limiting elements. Your current situation is that you are trying to retrieve/compare information that may exist only by inference. You haven't explained your process well enough that we can infer the purpose of the elusive number. I'm guessing that it's nothing more than a count of the quantity of unnamed things tracked in the current record. By simply entering the quantity in the record the sum of all of the quantities for this customer can be found by DSum() or an appropriate Query and displayed in a Form or Report. Note that it is *not stored*. If I missed by a long shot then please post back with a more complete description of the real world process and the details of your schema. HTH -- -Larry- -- "Jason" wrote in message ... I have a database which I inherited and so am somewhat limited in how it was originally designed. I want to place a validation rule on a field. Basically the application is that I am tracking an identifying number on a product we ship to ensure that I never send the same customer the same number (I cant have duplication within the same customer). I think I've pretty much figured out how to validate what is being sent against what has already been sent by using the dlookup function. The problem is that the field containing the numbers was originally formatted as text (problem 1) and that the numbers were entered as ranges. As an example we shipped 3 items and entered the value "1-3" rather than having one record for each item. Even if I convert the field to numeric, I don't see how I can validate against a range of numbers. any ideas? Also, if I were to redesign this database so that each item sent had its own record, how can these be entered efficiently. In other words I don't want the operator to have to make 3 entries when we send three items (in reality we deal with hundreds at a time, not 3). I want them to be able to make an entry on the form which would populate the appropriate number of records. help!? |
#6
|
|||
|
|||
validation on fields with number ranges
The two tables refer to the current table containing '1 - 3' type numbering
and a second table to append to for 1, 2, & 3. The second table can be details of the shipment in a one-to-many relationship. To reduce the number of records needed in the CountNumber table use 0 (zero) through your maximum and change query to this -- INSERT INTO [Change Request-1] ( x ) SELECT CountNumber.CountNUM + Val([y]) FROM CountNumber, [Change Requests] WHERE (((CountNumber.CountNUM) Between Val([y]) And IIf(InStr([y],"-")=0,Val([y]),Abs(Val(Mid([y],InStr([y],"-"),99)))))); -- KARL DEWEY Build a little - Test a little "Jason" wrote: Thanks for your reply. I'm a little unsure how to apply this to my situation. You reference 2 different tables and to substitute their names for my tables. However, I only have one table that is relevant to this. It is called "Cards Sent". I basically just have one table which lists card numbers we have sent out to each account. The only other table in the database is the customer info database. "KARL DEWEY" wrote: You can use an append query to make 3 entries if your field always has a dash. Create a table named CountNumber with field CountNUM containing 1 through your maximum ship number. Substitute your table names for [Change Requests] and [Change Request-1] and fields [x] and [y]. INSERT INTO [Change Request-1] ( x ) SELECT CountNumber.CountNUM FROM CountNumber, [Change Requests] WHERE (((CountNumber.CountNUM) Between Val([y]) And IIf(InStr([y],"-")=0,Val([y]),Abs(Val(Mid([y],InStr([y],"-"),99)))))); -- KARL DEWEY Build a little - Test a little "Jason" wrote: I have a database which I inherited and so am somewhat limited in how it was originally designed. I want to place a validation rule on a field. Basically the application is that I am tracking an identifying number on a product we ship to ensure that I never send the same customer the same number (I cant have duplication within the same customer). I think I've pretty much figured out how to validate what is being sent against what has already been sent by using the dlookup function. The problem is that the field containing the numbers was originally formatted as text (problem 1) and that the numbers were entered as ranges. As an example we shipped 3 items and entered the value "1-3" rather than having one record for each item. Even if I convert the field to numeric, I don't see how I can validate against a range of numbers. any ideas? Also, if I were to redesign this database so that each item sent had its own record, how can these be entered efficiently. In other words I don't want the operator to have to make 3 entries when we send three items (in reality we deal with hundreds at a time, not 3). I want them to be able to make an entry on the form which would populate the appropriate number of records. help!? |
#7
|
|||
|
|||
validation on fields with number ranges
Hi Jason,
This is Mark Han, a SQL Server Engineer. I'm glad to assist you with the issue. Based on your detail and clear description, I completely understand your concenr. Here, I would like to give you an example: For example: ============= There are 3 rows in the table and the value of the field called card number is 1-10, 15-25,30-40,50-60 and the card number what you would like to enter is a-b my suggested step ============= create 2 new column in the table. For instance: Min_number Max_number 1 10 15 25 30 40 50 60 transform a-b to 2 number (a and b) To select the min( Min_number) where Min_number a and to select the Max(Min_number) where Min_number a. To easy to understand, let's use A as min( Min_number) where Min_number a; and B as Max(Min_number) where Min_number a Then we can get the 4 value: in the example hereif a=26, b= 29), I can get the following Min_number Max_number 15 25 30 40 So, we just need to compare the a with the Max_number of A and the b with B. if aMax_number of A and b B. then a-b can be entered. in the example: a=26Max_number of A=25 b=29B(Max(Min_number))=30 Besides, to make the fuction run quickly, I suggest to creat indexes on column Min_number. Besides, please tell me what you would like to get if the a-b have part duplicated number as those in the table. in the above example, if a=20 and b=27, what you want to get? If anything is unclear or need me explain further, please tell me. Best regards, Mark Han Microsoft Online Community Support ================================================== ========= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . ================================================== ========= Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscripti...t/default.aspx. ================================================== ========== This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#8
|
|||
|
|||
validation on fields with number ranges
This is great! Thanks so much for your reply. I don't totally understand but
I am starting to see how this is possible. I can create 2 new columns and repair my existing data so that beginning number is in the "starting number" column and the last number is in the "end number" column. The process then of new card entry becomes fairly easy as we'd only have to actually add one record per shipment but it would contain the range of cards in two columns rather than the one column that I have now. My question remains though of how to validate new records against old ones. I'm not real familiar with validation so I need a little extra explanantion on this step. It is common that we don't start at "1" and go up from there. For example, we may issue numbers 50-60 the first time and then later issue numbers 20-30 or 70-80. So, the validation needs to take into account anything that was sent previoulsy, not just to make sure it is a greater number than the last time. Also, there are cases where only a single card is sent. In this case I assume my starting and ending number would be the same? If they enter a range which contains any of the numbers previously issued, I want it to not enter the data in the table but display an error message to the effect of "Card number already issued". "Mark Han[MSFT]" wrote: Hi Jason, This is Mark Han, a SQL Server Engineer. I'm glad to assist you with the issue. Based on your detail and clear description, I completely understand your concenr. Here, I would like to give you an example: For example: ============= There are 3 rows in the table and the value of the field called card number is 1-10, 15-25,30-40,50-60 and the card number what you would like to enter is a-b my suggested step ============= create 2 new column in the table. For instance: Min_number Max_number 1 10 15 25 30 40 50 60 transform a-b to 2 number (a and b) To select the min( Min_number) where Min_number a and to select the Max(Min_number) where Min_number a. To easy to understand, let's use A as min( Min_number) where Min_number a; and B as Max(Min_number) where Min_number a Then we can get the 4 value: in the example hereif a=26, b= 29), I can get the following Min_number Max_number 15 25 30 40 So, we just need to compare the a with the Max_number of A and the b with B. if aMax_number of A and b B. then a-b can be entered. in the example: a=26Max_number of A=25 b=29B(Max(Min_number))=30 Besides, to make the fuction run quickly, I suggest to creat indexes on column Min_number. Besides, please tell me what you would like to get if the a-b have part duplicated number as those in the table. in the above example, if a=20 and b=27, what you want to get? If anything is unclear or need me explain further, please tell me. Best regards, Mark Han Microsoft Online Community Support ================================================== ========= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . ================================================== ========= Get notification to my posts through email? Please refer to http://msdn.microsoft.com/subscripti...ult.aspx#notif ications. Note: The MSDN Managed Newsgroup support offering is for non-urgent issues where an initial response from the community or a Microsoft Support Engineer within 1 business day is acceptable. Please note that each follow up response may take approximately 2 business days as the support professional working with you may need further investigation to reach the most efficient resolution. The offering is not appropriate for situations that require urgent, real-time or phone-based interactions or complex project analysis and dump analysis issues. Issues of this nature are best handled working with a dedicated Microsoft Support Engineer by contacting Microsoft Customer Support Services (CSS) at http://msdn.microsoft.com/subscripti...t/default.aspx. ================================================== ========== This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#9
|
|||
|
|||
validation on fields with number ranges
Hi Jason,
Thank you for the update. Based on the requirement: If they enter a range which contains any of the numbers previously issued, I want it to not enter the data in the table but display an error message to the effect of "Card number already issued", my suggestion could help to resolve the issue, if entrying number numericly or not. The reason why the example in my previous reply is to entry number numericly is make my suggestion easy to understand. The step to validate new records against old ones. Let me make an example again. For example, you would like to entry 40-45 and the existing record is Min_number Max_number 50 60 20 30 70 80 31 35 To get the minimumest number which is biggerer than 40. in this example, the number is 50 To get the maximal number which is smaller than 40. in this example, the number is 31 So we ge the following record: Min_number Max_number 50 60 31 35 Since 4035 and 4550. the number 40-45 can be entried into the database. If you still has question on my suggestion, please let me know. Best regards, Mark Han Microsoft Online Community Support ================================================== ======= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
#10
|
|||
|
|||
validation on fields with number ranges
Hi Jason,
I am interested in this issue. Would you mind letting me know the result of the suggestions? If you need further assistance, feel free to let me know. I will be more than happy to be of assistance. Best regards, Charles Wang Microsoft Online Community Support ================================================== ======= Delighting our customers is our #1 priority. We welcome your comments and suggestions about how we can improve the support we provide to you. Please feel free to let my manager know what you think of the level of service provided. You can send feedback directly to my manager at: . ================================================== ======= This posting is provided "AS IS" with no warranties, and confers no rights. ================================================== ======= |
|
Thread Tools | |
Display Modes | |
|
|