To find missing entries - Delivery Note Nos

[Anthony Joseph]

I have a database in which my Invoice table has a unique Invoice ID. The
corresponding Delivery note number is mentioned in the same table. A single
Invoice ID may have mulitple Delivery Note Nos as invoicing for many
customers is done at the end of the month though delivery of goods is done
multiple times during the month. In such instances the delivery note numbers
are seperated by commas. It might also happen that a single Delivery note has
items mentioned in it split into two invoices.

Kindly note that the delivery notes are handwritten on preprinted sequential
numbered book and the data is entered in Invoice table for reference purpose.

I would like to ensure that all the delivery note that has been issued for
the month has been invoiced.

For example DO's issued for the month is from 1000 to 1025.

In the Invoice table the entries might be as follows
Invoice No - Delivery Note
1 1000
2 1002,1003,1010
3 1004,1009
4 1006,1025
5 1008,1023
6 1011,1012,1013,1014,1015,1016,1017
7 1018,1019,
8 1021

So I want to find out if I enter a criteria of search for missing Delivery
Note between 1000 - 1025, the system should list 1005,1007,1020,1022,1024 as
deliverynote that has not been invoiced.

It would be much better if it can tabulate this result that shows the
delivery note nos in a sequential order in a single column, matched by
corresponding Invoice number and then I can add the customer name to that
row. In this case the blank Invoice ID corresponding to the DO nos will let
me know which DO's have not been invoiced.

Many thanks in advance, especially for the patience in reading this query. I
am quite a newbie on access with not much programming experience.

 

[scubadiver]

So each invoice can have multiple delivery notes. That requires an
improvement in the design.

As far as I am aware Access can't do this because you want to search for
numbers that Access isn't aware of.

I think you ought to have a many-to-many relationship between invoices and
delivery notes. The "delivery note" table can store all sequential numbers
and the junction box can hold all delivery notes that have been issued.

You can then create a special query that can show all delivery numbers that
haven't been issued or are missing.

Am I close?

 

[EllenM]

Hi Anthony,
You might want to read the recent "text to columns" post to break up your
Delivery Notes Nos into individual cells. From there you can normalize your
data.

Hope I gave you enough key words to get the solution.

Ellen

 

[Anthony Joseph]

Thanks for the reply, I think you understood my problem spot on. However I
would prefer entering the range of delivery note to be searched as a query
parameter as this will keep increasing on a daily basis. Also it would avoid
me the trouble of changing the backend table design as over 50 invoices do
get punched in each day and I dont want to tinker with a working solution
especially with my limited knowledge.

In this case now that I am manually entering the delivery note range to be
searched is there some query that I could run to find my missing DO's the
ones where goods have been delivered but the customer has not been invoiced.

Thanks once again

 

[Anthony Joseph]

Thanks Ellen,

Apologies for my limited knowledge, and yes I will try to read all I can on
this matter, atleast I think I have someplace to start now. Will update the
post if I am able to get it working.

 

[scubadiver]

If you have created a separate table for the entire delivery note range you
can insert that table and the delivery note table into a query and drag the
appropriate field from one to the other.

Click on the line and, in the box, choose option two. This will give you all
delivery notes in one table that don't exist in the other.

Drag the delivery number field from each table into the bottom pane. In the
criteria of the second table type in "is null".

You will have to mess around with it to get what you need.

 

[Anthony Joseph]

Thanks,

It has partially answered my requirement. Now I have to find a way to break
my delivery note field that contains multiple delivery note numbers seperated
by comma. For example for some invoices as I mentioned I have multiple
delivery notes that are entered as 1,2,5,6 in a single field.

 

[Anthony Joseph]

I managed to get a working solution based on your inputs, however I am not
very pleased with it. It sort of feels like crude. Any help would be welcome
to make it more refined.
-------------------------
1) Copied this module

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)
End Function

2) Ran this query - to break text to columns

UPDATE YourTable SET YourTable.Field1 = ParseText([FieldName],0),
YourTable.Field2 = ParseText([FieldName],1), YourTable.Field3 =
ParseText([FieldName],2), YourTable.Field4 = ParseText([FieldName],3);

3) Ran this query to normalize the table

SELECT [InvoiceID], Field1 as DO
FROM [YourTable]
WHERE Field1 is Not Null
UNION ALL
SELECT [InvoiceID], Field2
FROM [YourTable]
WHERE Field2 is Not Null
UNION ALL
SELECT [InvoiceID], Field3
FROM [YourTable]
WHERE Field3 is Not Null
UNION ALL SELECT [InvoiceID], Field4
FROM [YourTable]
WHERE Field4 is Not Null;

4)Made a table that had a list of all Delivery note issued as ScubaDiver had
advised

5)Used Find Unmatched query wizard to find DO's that did not invoice issued
for them as ScubaDiver had advised

YES things did work, but not satisfied, mainly because I dont know whether
there will be 4 DO's in a single invoice or 20 DO's in a single invoice. Is
there a better code and ways to automate this procedure. Must admit that I
found all this information from the post. So thanks to everyone who
contributed. I did feel like a detective looking for clues as I went on the
path shown by you.

 

[scubadiver]

If your table design is crude then what you get out of it will be the same!

"I dont want to tinker with a working solution especially with my limited
knowledge"

Do you have a working solution and why should your knowledge be limited?

The database has to be correct for what you want to achieve. With the design
of such applications you can't take shortcuts.

--

The 11th day of every month:

http://truthaction.org/forum/index.php

I managed to get a working solution based on your inputs, however I am not
very pleased with it. It sort of feels like crude. Any help would be welcome
to make it more refined.
-------------------------
1) Copied this module

Public Function ParseText(TextIn As String, X) As Variant
On Error Resume Next
Dim var As Variant
var = Split(TextIn, ",", -1)
ParseText = var(X)
End Function

2) Ran this query - to break text to columns

UPDATE YourTable SET YourTable.Field1 = ParseText([FieldName],0),
YourTable.Field2 = ParseText([FieldName],1), YourTable.Field3 =
ParseText([FieldName],2), YourTable.Field4 = ParseText([FieldName],3);

3) Ran this query to normalize the table

SELECT [InvoiceID], Field1 as DO
FROM [YourTable]
WHERE Field1 is Not Null
UNION ALL
SELECT [InvoiceID], Field2
FROM [YourTable]
WHERE Field2 is Not Null
UNION ALL
SELECT [InvoiceID], Field3
FROM [YourTable]
WHERE Field3 is Not Null
UNION ALL SELECT [InvoiceID], Field4
FROM [YourTable]
WHERE Field4 is Not Null;

4)Made a table that had a list of all Delivery note issued as ScubaDiver had
advised

5)Used Find Unmatched query wizard to find DO's that did not invoice issued
for them as ScubaDiver had advised

YES things did work, but not satisfied, mainly because I dont know whether
there will be 4 DO's in a single invoice or 20 DO's in a single invoice. Is
there a better code and ways to automate this procedure. Must admit that I
found all this information from the post. So thanks to everyone who
contributed. I did feel like a detective looking for clues as I went on the
path shown by you.

Hi Anthony,
You might want to read the recent "text to columns" post to break up your
Delivery Notes Nos into individual cells. From there you can normalize your
data.

Hope I gave you enough key words to get the solution.

Ellen

 

[EllenM]

Hi Anthony Joseph,
Sounds like you've made progress. I don't have time to check out your
scripts, but in the past I've used "NormalizeDenormalize.mdb" that I've
downloaded from http://www.rogersaccesslibrary.com.

Also, I know you've already broken up your DOs into individual cells, but
Excel does the same thing (text to columns) quite easily.


Ellen

 

[Anthony Joseph]

Hello once again,

I think if I need to improve the database design how should I go about it.
One of the reasons, I think I went for a text field for a delivery note, was
because of the sheer number of deliverynotes that can be represented in a
single invoice. For example, there can be as many as 40 delivery note for a
single Invoice. So the option of inputting them as comma seperated values at
that time seemed the obvious thing to do, but now I know the limitations of
such an approach. Any ideas.

 

[John Spencer]

I would add a table "InvoiceDeliveryNotes" and in that table I would
have two fields - InvoiceID and DeliveryNoteID.

Then I would create one record for each applicable Delivery note for the
Invoice. Best way to do this at data entry is to use a subform attached
to the Invoice form.

'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[Anthony Joseph]

Thanks John,

I will give it a try, and I also feel confident it would work. The only
question that pops my mind is that I would now need to convert those rows
into a csv text file for it to appear in my Invoice report properly. The
other doubt I have is the field Delivery Note ID, should I be keeping it as a
text field or a number field. Currently the sequence is in 15000's, so it
will be 5 digit number for the next I would say atleast a couple of years. If
I leave it as a number field will it affect my delivery note field in the
report. If I leave it as a text field will it affect sorting and finding
missing sequences? I dont want to update the list of issued delivery note on
a daily basis as each delivery note book has about 50 pages only. What I
would prefer doing is that at the end of the day / month I input a range
(15000 - 16000) of delivery note and then my improved database gives me
results in a jiffy.

Thanks once again

 

[John Spencer]

As far as concatenating the data that is in the many table (Delivery Notes),
Duane Hookom has a sample solution at
http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane

I would probably store the NoteID as a number.

I don't completely understand your table setup, so beyond the two
suggestions I am not able to offer more advice.

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..