validation on fields with number ranges

[Jason]

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

 

[KARL DEWEY]

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))))));

 

[Larry Daugherty]

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

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

 

[Jason]

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.

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

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

 

[Jason]

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!

 

[KARL DEWEY]

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

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.

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

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

 

[Mark Han[MSFT]]

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 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: (e-mail address removed).
===========================================================
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.
=========================================================

 

[Jason]

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]]

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 40>35 and 45<50. 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: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Mark Han[MSFT]]

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: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Jason]

Thanks so much for your help. I'm hesitant about asking for more help because
it is simply my lack of experience that is keeping me from fully
understanding.

I think I've got the concept of what you're doing but don't really know how
to write the validation rule for it.

Lets start fresh. I now have two columns in the database ("Start#" and
"End#"). Lets assume the following values are already in that table:

Start End
5 10
15 20

Now, I want to add validation that disallows any new ranges which would
overlap as follows:
1-4 would be valid
21-25 would be valid
9-11 would not be valid
etc.

I assume I need to add the validation rule to the "Start" and "End" fields
in the table. I'm just not sure how to write it. If you can write the example
validation rule for me I'm sure I would then understand.

 

[Michael Gramelspacher]

Thanks so much for your help. I'm hesitant about asking for more help because
it is simply my lack of experience that is keeping me from fully
understanding.

I think I've got the concept of what you're doing but don't really know how
to write the validation rule for it.

Lets start fresh. I now have two columns in the database ("Start#" and
"End#"). Lets assume the following values are already in that table:

Start End
5 10
15 20

Now, I want to add validation that disallows any new ranges which would
overlap as follows:
1-4 would be valid
21-25 would be valid
9-11 would not be valid
etc.

I assume I need to add the validation rule to the "Start" and "End" fields
in the table. I'm just not sure how to write it. If you can write the example
validation rule for me I'm sure I would then understand.

It is possible to have a CHECK constraint to do this, but most people would do the overlaps check in
the BeforeUpdate event of a Form. A Check constraint is part of the table definition and would work
even when values are added directly into a table. There are problems with Check constraints, in
that even after almost four versions, the Access interface has not been changed to handle them.

Here is an example just to give you the general idea:

Sub CreateConstraint()

With CurrentProject.Connection

..Execute "ALTER TABLE ProductPriceHistory ADD CONSTRAINT" & _
" Overlapping_periods_not_allowed CHECK (NOT EXISTS( SELECT *" _
& " FROM ProductPriceHistory AS P1 WHERE 1 < (SELECT COUNT(*)" _
& " FROM ProductPriceHistory AS P2 WHERE P1.upc = P2.upc" _
& " AND P1.start_time <= P2.end_time" _
& " AND P2.start_time <= NZ(P1.end_time,'3000-01-01'))));"

End With
End Sub

Google for a newsgroup message "Check Constraint Usage" by Jamie Collins for all the details.

 

[Mark Han[MSFT]]

Hi Jason,

Thank you for the update.

To do you a favor, I write a general sample for you:

set a=21
set b=25

set A= select min(start) where start >@a
set B=select max(start) where stat<@a
set C=select End where start=@B

If (@a>@C and @b<@A)
insert ......

Hope the above is helpful

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================

 

[Mark Han[MSFT]]

Hi Jason,

What is this issue going on?

If there is any issue, please feel free to post back. We are very glad for
further assistance.

Have a good day!

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: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and co

 

[Jason]

Thanks for the reply. I'm not explaining it correctly or something because I
cant seem to put Mark's answers together to help me out. I simply want a
validation (beforeupdate on the form is fine) to ensure that I dont send out
duplicate card numbers. For instance:

I may have sent in the past:

1-10
25-50
12
14
70-80

I cant send any of these again. So, 11-24 would be valid. Any single number
between 11-24 would be valid, etc. 25-50 would not be valid, 40-50 would not
be valid, etc.

The start and end numbers will be stored in separate columns. The form will
have entries for each as well. I need the entry on the form to validate the
range being sent to all other ranges or single cards to verify no duplicates.

 

[Mark Han[MSFT]]

Hi Jason,

Thank you for the update.

I completely understand your concern. However, our newsgroup only focus on
broken-fix issue. For your convenience, I have provided a suggestion with
a simple example already.

The validation rule is according to your detail requirement. I am able to
give you a general suggestion. if you need assistance beyond incident based
break-fix product maintenance, such as product migration, code review, or
new program development, you may select Microsoft Advisory Services option.
Information about the types of Advisory Services available, visit the
http://support.microsoft.com/gp/advisoryservice. I appreciate your
understanding and cooperation.

Best regards,
Mark Han
Microsoft Online Community Support
Get Secure! - www.microsoft.com/security
====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
====================================================