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!?

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!?

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!?

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!?

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!?

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!?

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.
================================================== =======

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.
================================================== =======

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.
================================================== =======

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.
================================================== =======