Hi everybody,

If I want to run a gap report in Access 2000, what should I do?

for example: I have a box number field that I want to see what I missed. 
and the box number appears like these:

ARU001
ARU003
ARU004
BRU001
BRU002
BRU004

Thanks for your time (even just to look at this email).  I know you are 
the expert.

Sincerely

Nath

do you have another table with the full range of possible values in it?  If
not it might be worth creating something in Excel then importing it.

If you do you can use the query qizard to create a query which shows the
values in one table but not in another.
As you'll see when you look at what Access builds in design view, it uses
NULL to identify missing records across a join.

regards,

Richard Gibson
IT Manager
Walkerpack Ltd


-----Original Message-----
From: nathaliekimlam@h... [mailto:nathaliekimlam@h...]
Sent: 29 January 2003 15:54
To: Access
Subject: [access] gap report


Hi everybody,

If I want to run a gap report in Access 2000, what should I do?

for example: I have a box number field that I want to see what I missed.
and the box number appears like these:

ARU001
ARU003
ARU004
BRU001
BRU002
BRU004

Thanks for your time (even just to look at this email).  I know you are
the expert.

Sincerely

Nath

it's a good point, Richard. But I really don't have any original data to 
compare with.  The box has to be scanned in order to have box number.  and 
the bar code was builded with sequence number. in this case we want to 
look for the miss scanning box.

Nath



do you have another table with the full range of possible values in it?  If
not it might be worth creating something in Excel then importing it.

If you do you can use the query qizard to create a query which shows the
values in one table but not in another.
As you'll see when you look at what Access builds in design view, it uses
NULL to identify missing records across a join.

regards,

Richard Gibson
IT Manager
Walkerpack Ltd

<< for example: I have a box number field that I want to see what I 
missed. 
and the box number appears like these:

ARU001
ARU003
ARU004
BRU001
BRU002
BRU004 >>

The following is assuming the box number is always in the format of three 
letters and three numbers.

First create a make-table query of all the DISTINCT box numbers and SORT 
them by box number.  Call it Table A.

strPrefix is the first three letters of the box no.  It is an array.  
IntSuffix is the last three characters of the box no. converted to integer.
Create a counter, say i.  Remember to declare the variables and arrays.   
Then, in part English part code:

i = 1 'Which box should it be?
j = 1 'Which box is it?
n = no. of boxes

strPrefix(0) = "   " (three spaces)
Do Until j > n
   Read the first box no. from the table and assign it to strBoxNo

   'What are the first three letters of the box no.?
   strPrefix(j) = Left(strBoxNo,3)

   If strPrefix(j) = strPrefix(j-1) then
      '1st 3 letters of this box = 1st 3 letters of prev box, i.e.
      'this box is the next one in the series.
      i = i + 1
   Else
      '1st 3 letters of this box <> lst 3 letters of prev box, i.e.
      'this is box 1 in a new box series.
      i = 1
   End If

   'What are the last three characters of the box no.?
   intSuffix(j) = CInt(Right(strBoxNo,3))

   'Compare the actual box no., intSuffix, to where the counter is.
   
   If intSuffix(j) = i then
      j = j + 1 'Everything is OK.  Go to next box.
   Else
      MsgBox "This box no. is " & strBoxNo & ".  Box No. " & _
      strPrefix(j) & Format(i, "000") & " is missing.", vbExclamation, _
      "Missing Box!"
   End If
Loop

You count from j = 1 to n and compare i to the actual box number.  i 
always increments by 1.  So as j loops...

   j = 1, strBoxNo = "ARU001", strPrefix = "ARU", strSuffix = 1, i = 1 OK
   j = 2, strBoxNo = "ARU003", strPrefix = "ARU", strSuffix = 3, i = 2 Bad
   j = 2, strBoxNo = "ARU003", strPrefix = "ARU", strSuffix = 3, i = 3 OK
   j = 3, strBoxNo = "ARU004", strPrefix = "ARU", strSuffix = 4, i = 4 OK
   j = 4, strBoxNo = "BRU001", strPrefix = "BRU", strSuffix = 1, i = 1 OK
   j = 5, strBoxNo = "BRU002", strPrefix = "BRU", strSuffix = 2, i = 2 OK
   j = 6, strBoxNo = "BRU004", strPrefix = "BRU", strSuffix = 4, i = 3 Bad
   j = 6, strBoxNo = "BRU004", strPrefix = "BRU", strSuffix = 4, i = 4 OK
   j = 7, exit loop

Without actually coding and trying this, the bugs may not all be out, but 
you get the idea.  If you don't want to get messaged everytime a box 
number is missing, write info into a missing box table instead.

Greg

Thanks sooooo much for your time, Greg.  I am going to try it right now.


> << for example: I have a box number field that I want to see what I 
m> issed. 
a> nd the box number appears like these:

> ARU001
A> RU003
A> RU004
B> RU001
B> RU002
B> RU004 >>

> The following is assuming the box number is always in the format of 
three 
l> etters and three numbers.

> First create a make-table query of all the DISTINCT box numbers and SORT 
t> hem by box number.  Call it Table A.

> strPrefix is the first three letters of the box no.  It is an array.  
I> ntSuffix is the last three characters of the box no. converted to 
integer.
C> reate a counter, say i.  Remember to declare the variables and 
arrays.   
T> hen, in part English part code:

> i = 1 'Which box should it be?
j>  = 1 'Which box is it?
n>  = no. of boxes

> strPrefix(0) = "   " (three spaces)
D> o Until j > n
 >   Read the first box no. from the table and assign it to strBoxNo

>    'What are the first three letters of the box no.?
 >   strPrefix(j) = Left(strBoxNo,3)

>    If strPrefix(j) = strPrefix(j-1) then
 >      '1st 3 letters of this box = 1st 3 letters of prev box, i.e.
 >      'this box is the next one in the series.
 >      i = i + 1
 >   Else
 >      '1st 3 letters of this box <> lst 3 letters of prev box, i.e.
 >      'this is box 1 in a new box series.
 >      i = 1
 >   End If

>    'What are the last three characters of the box no.?
 >   intSuffix(j) = CInt(Right(strBoxNo,3))

>    'Compare the actual box no., intSuffix, to where the counter is.
 >   
 >   If intSuffix(j) = i then
 >      j = j + 1 'Everything is OK.  Go to next box.
 >   Else
 >      MsgBox "This box no. is " & strBoxNo & ".  Box No. " & _
 >      strPrefix(j) & Format(i, "000") & " is missing.", vbExclamation, _
 >      "Missing Box!"
 >   End If
L> oop

> You count from j = 1 to n and compare i to the actual box number.  i 
a> lways increments by 1.  So as j loops...

>    j = 1, strBoxNo = "ARU001", strPrefix = "ARU", strSuffix = 1, i = 1 OK
 >   j = 2, strBoxNo = "ARU003", strPrefix = "ARU", strSuffix = 3, i = 2 
Bad
 >   j = 2, strBoxNo = "ARU003", strPrefix = "ARU", strSuffix = 3, i = 3 OK
 >   j = 3, strBoxNo = "ARU004", strPrefix = "ARU", strSuffix = 4, i = 4 OK
 >   j = 4, strBoxNo = "BRU001", strPrefix = "BRU", strSuffix = 1, i = 1 OK
 >   j = 5, strBoxNo = "BRU002", strPrefix = "BRU", strSuffix = 2, i = 2 OK
 >   j = 6, strBoxNo = "BRU004", strPrefix = "BRU", strSuffix = 4, i = 3 
Bad
 >   j = 6, strBoxNo = "BRU004", strPrefix = "BRU", strSuffix = 4, i = 4 OK
 >   j = 7, exit loop

> Without actually coding and trying this, the bugs may not all be out, 
but 
y> ou get the idea.  If you don't want to get messaged everytime a box 
n> umber is missing, write info into a missing box table instead.

> Greg

Here is another way:

1. Create two single field tables (tblPrefix and tblSuffix) for the three
character prefixes (ARU,BRU,etc) and the three character suffixes
(001,002,003,004, etc.).
2. Use a Cartesian join to create a list of all possible BoxIDs.  Name the
query qryCartesianJoin.

SELECT [tblPrefix]![prefix] & [tblSuffix].[Suffix] AS BoxID
FROM tblPrefix, tblSuffix;

3. Use a NOT-IN query to show all the MISSING box Ids in your sample table
(in this case tblBoxIDs).

SELECT qryCartesianJoin.BoxID
FROM qryCartesianJoin LEFT JOIN tblBoxIDs ON qryCartesianJoin.BoxID 
tblBoxIDs.BoxID
WHERE (((tblBoxIDs.BoxID) Is Null));

4. To add additional prefixes (eg CRU) or suffixes (eg 005, 006), just add
them to the tblPrefix or tblSuffix table.

Good luck,

Rand
	

-----Original Message-----
From: nathaliekimlam@h... [mailto:nathaliekimlam@h...]
Sent: Wednesday, January 29, 2003 09:54
To: Access
Subject: [access] gap report

Hi everybody,

If I want to run a gap report in Access 2000, what should I do?

for example: I have a box number field that I want to see what I missed.
and the box number appears like these:

ARU001
ARU003
ARU004
BRU001
BRU002
BRU004

Thanks for your time (even just to look at this email).  I know you are
the expert.

Sincerely

Nath

In some of the messages I'm reading something like "create a list of 
possible numbers..." and "add to the list later."  My preference is NOT to 
do that.  Work with what you already have; don't try to guess what might 
come up because someone somewhere will always create something that you 
weren't expecting.  The solution I gave only works with a list of unique 
box numbers that are already there and sees if there are any gaps in the 
numbering.  It doesn't try to compare itself with any prediction table.  
Predictions are dangerous.

Oh, correction in my original note:  In the last part of it, "strSuffix" 
should read "intSuffix."

Greg

Absolutely - my response was a quick and dirty one off type solution - yours
is a very elegant one

-----Original Message-----
From: Gregory Serrano [mailto:SerranoG@m...]
Sent: 30 January 2003 13:16
To: Access
Subject: [access] RE: gap report


In some of the messages I'm reading something like "create a list of
possible numbers..." and "add to the list later."  My preference is NOT to
do that.  Work with what you already have; don't try to guess what might
come up because someone somewhere will always create something that you
weren't expecting.  The solution I gave only works with a list of unique
box numbers that are already there and sees if there are any gaps in the
numbering.  It doesn't try to compare itself with any prediction table.
Predictions are dangerous.

Oh, correction in my original note:  In the last part of it, "strSuffix"
should read "intSuffix."

Greg

Hi!!

The first step is, identifying what is missing. How can a program "know" 
you "miss" some codes? YOU have to provide that information. Thus, you have to 
know which are the WHOLE CODES you want to check for presence. This means you 
have to create a temporary table of ALL (wanted/theoretical/expected) RECORDS.
Then, you can follow Richard's approach, with an outer join where the criteria 
is "Is Null" in the real data field. But before you can do that, you have to 
build MANUALLY (I mean, programatically) the support table of all codes.
That could be as easy as issueing a loop:

For n = 1 To 5
   For lCounter = 1 To 1000
       rsd.Addnew
       rsd.Code = Chr$(64 + n) & Format$(lCounter,"0000")
   Next lCounter
Next n

rsd.Updatebatch

(or you can use an ADO version of this).

HTH,

   Claudio de Biasio
     Team 97 S.r.l.


Quoting nathaliekimlam@h...:

> it's a good point, Richard. But I really don't have any original data to
> 
> compare with.  The box has to be scanned in order to have box number. 
> and 
> the bar code was builded with sequence number. in this case we want to 
> look for the miss scanning box.
> 
> Nath
> 
> 
> 
> do you have another table with the full range of possible values in it? 
> If
> not it might be worth creating something in Excel then importing it.
> 
> If you do you can use the query qizard to create a query which shows
> the
> values in one table but not in another.
> As you'll see when you look at what Access builds in design view, it
> uses
> NULL to identify missing records across a join.
> 
> regards,
> 
> Richard Gibson
> IT Manager
> Walkerpack Ltd
> 
> 
> 

Hi!

Saw this really good solution. It's OK, but it's missing the boxes that SHOULD 
HAVE (and  you actually don't).

I mean, if you have a set called ARNnnnn, but you are missing ALL OF THEM, the 
loop CANNOT IMAGINE you have to have even any one! That's why I prefer to 
create a table of all the "assumed" codes BEFORE. Also, this leads to a really 
fast query, instead of a code-based solution.

HTH

   Claudio de Biasio
     Team 97 S.r.l.

Quoting nathaliekimlam@h...:

> Thanks sooooo much for your time, Greg.  I am going to try it right
> now.
> 
> 
> > << for example: I have a box number field that I want to see what I 
> m> issed. 
> a> nd the box number appears like these:
> 
> > ARU001
> A> RU003
> A> RU004
> B> RU001
> B> RU002
> B> RU004 >>
> 
> > The following is assuming the box number is always in the format of 
> three 
> l> etters and three numbers.
> 
> > First create a make-table query of all the DISTINCT box numbers and
> SORT 
> t> hem by box number.  Call it Table A.
> 
> > strPrefix is the first three letters of the box no.  It is an array. 
> 
> I> ntSuffix is the last three characters of the box no. converted to 
> integer.
> C> reate a counter, say i.  Remember to declare the variables and 
> arrays.   
> T> hen, in part English part code:
> 
> > i = 1 'Which box should it be?
> j>  = 1 'Which box is it?
> n>  = no. of boxes
> 
> > strPrefix(0) = "   " (three spaces)
> D> o Until j > n
>  >   Read the first box no. from the table and assign it to strBoxNo
> 
> >    'What are the first three letters of the box no.?
>  >   strPrefix(j) = Left(strBoxNo,3)
> 
> >    If strPrefix(j) = strPrefix(j-1) then
>  >      '1st 3 letters of this box = 1st 3 letters of prev box, i.e.
>  >      'this box is the next one in the series.
>  >      i = i + 1
>  >   Else
>  >      '1st 3 letters of this box <> lst 3 letters of prev box, i.e.
>  >      'this is box 1 in a new box series.
>  >      i = 1
>  >   End If
> 
> >    'What are the last three characters of the box no.?
>  >   intSuffix(j) = CInt(Right(strBoxNo,3))
> 
> >    'Compare the actual box no., intSuffix, to where the counter is.
>  >   
>  >   If intSuffix(j) = i then
>  >      j = j + 1 'Everything is OK.  Go to next box.
>  >   Else
>  >      MsgBox "This box no. is " & strBoxNo & ".  Box No. " & _
>  >      strPrefix(j) & Format(i, "000") & " is missing.", vbExclamation,
> _
>  >      "Missing Box!"
>  >   End If
> L> oop
> 
> > You count from j = 1 to n and compare i to the actual box number.  i 
> a> lways increments by 1.  So as j loops...
> 
> >    j = 1, strBoxNo = "ARU001", strPrefix = "ARU", strSuffix = 1, i = 1
> OK
>  >   j = 2, strBoxNo = "ARU003", strPrefix = "ARU", strSuffix = 3, i = 2
> 
> Bad
>  >   j = 2, strBoxNo = "ARU003", strPrefix = "ARU", strSuffix = 3, i = 3
> OK
>  >   j = 3, strBoxNo = "ARU004", strPrefix = "ARU", strSuffix = 4, i = 4
> OK
>  >   j = 4, strBoxNo = "BRU001", strPrefix = "BRU", strSuffix = 1, i = 1
> OK
>  >   j = 5, strBoxNo = "BRU002", strPrefix = "BRU", strSuffix = 2, i = 2
> OK
>  >   j = 6, strBoxNo = "BRU004", strPrefix = "BRU", strSuffix = 4, i = 3
> 
> Bad
>  >   j = 6, strBoxNo = "BRU004", strPrefix = "BRU", strSuffix = 4, i = 4
> OK
>  >   j = 7, exit loop
> 
> > Without actually coding and trying this, the bugs may not all be out,
> 
> but 
> y> ou get the idea.  If you don't want to get messaged everytime a box 
> n> umber is missing, write info into a missing box table instead.
> 
> > Greg
> 
> 

<< Saw this really good solution. It's OK, but it's missing the boxes that 
SHOULD HAVE (and  you actually don't).

I mean, if you have a set called ARNnnnn, but you are missing ALL OF THEM, 
the loop CANNOT IMAGINE you have to have even any one! That's why I prefer 
to create a table of all the "assumed" codes BEFORE. Also, this leads to a 
really fast query, instead of a code-based solution. >>

Ah, good point about missing a whole set of ARNnnnn, however, that wasn't 
the question posed.  If the question was "Are there any gaps in the 
numbering AND what box sets are missing?", yes, you'd have to create a 
list of existing possible box sets (e.g. a table listing ARU, BRU, CRU, 
ASQ, BSQ, etc.) and compare.

However, the question posed was ONLY "What GAPS are there in the 
numbering?"  Given that second question, I would not create a prediction 
table.  In this case, you don't care that e.g. box set ASQ was never used, 
you just want to make sure that there are no gaps in ARU001 to ARU004, 
BRU001 to BRU004.

Greg