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" <Jason.RemoveThis@discussions.microsoft.com> wrote in message
news:6B733770-AD93-49FC-A730-849D76D3DB40@microsoft.com...
> 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" <Jason RemoveThis @discussions.microsoft.com> wrote in message
> news:6B733770-AD93-49FC-A730-849D76D3DB40@microsoft.com...
> > 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 here:(if 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 a>Max_number of A and b < B. then a-b can be entered. in the example:
a=26>Max_number of A=25
b=29<B(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: msdnmg.RemoveThis@microsoft.com.
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/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 here:(if 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 a>Max_number of A and b < B. then a-b can be entered. in the example:
> a=26>Max_number of A=25
> b=29<B(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: msdnmg DeleteThis @microsoft.com.
> ===========================================================
> Get notification to my posts through email? Please refer to
> http://msdn.microsoft.com/subscriptions/managednewsgroups/default.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/subscriptions/support/default.aspx.
> ============================================================
> This posting is provided "AS IS" with no warranties, and confers no rights.
> =========================================================
>
>