Does anyone have a recommendation on how to track numbers in a seq

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Janet - 14 Nov 2004 22:19 GMT

Does anyone have a recommendation on how to create a database to track number
sold in a sequence.
I have an order form that has a reference number. This order form is
included in a product. The customer fills in the form from the product he
has paid for and sends the form to us. We then send the customer the
product. To keep people from copy or creating counterfeit forms I would like
to track the form. I sell a sequence of a minum of 10 forms to a retailer.
When I get the order form from the customer who has purchased the product
from the retailer I would like to be able to verify that this is not a
duplicate form or a counterfeit form ....I hope this makes sense. I would
like suggestions on the best way to accomplish this.

Reply to this Message

Tim Ferguson - 15 Nov 2004 17:32 GMT

> I sell a sequence of a minum of 10 forms to a retailer.
> When I get the order form from the customer who has purchased the
> product from the retailer I would like to be able to verify that this
> is not a duplicate form or a counterfeit form ....I hope this makes
> sense.

Yes, it makes sense. What you have not told us is what checks you plan to
make in order to avoid duplication or counterfeiting. When you have worked
out that in real life, then it will probably be pretty easy to model in the
database.

HTH

Tim F

Reply to this Message

Janet - 15 Nov 2004 18:53 GMT

I plan to use a database and enter the reference number to see what retailer
purchased it and if this product has been sent to a customer or not....

I have a table called reference numbers that I add the reference numbers
from the forms as I purchase the order forms. The forms are a bit like
checks that they do not have duplicate reference numbers.

I have a table called retailers that I add all the retailers that I sell
blocks of these forms to. 10 is the minimun number that can be purchased.

I have a table called orders that has the retailers name as a field and a
field for reference numbers
My delima is this.....

I can manage adding reference numbers to retailers very easily however the
only way I can figure out how to do it is to add one reference number at a
time to a retailer. If a retailer purchases 1000 reference numbers.....I'd
like to be able to add the next 1000 not sold in the table references.....
I have added a field Sold - yes/no to the reference table.....

I created a form and added the retailer and the reference tables and have
added the field retailer, I have added a non bound label to add qty of
reference numbers orders. I need this bound field to take the qty entered
assign the next reference numbers not sold in the sequence by that number to
this retailer then mark those reference numbers in the reference table field
Sold as yes.

Am I trying to accomplish something that isn't possible? Or is there a way
to do this?
Thanks for your post and sorry to not been more clear.

> > I sell a sequence of a minum of 10 forms to a retailer.
> > When I get the order form from the customer who has purchased the
[quoted text clipped - 10 lines]
>
> Tim F

Reply to this Message

Tim Ferguson - 16 Nov 2004 17:21 GMT

> Am I trying to accomplish something that isn't possible? Or is there
> a way to do this?

There is bound to be a way to do this, but you haven't yet told us what
"this" is. I have thought a bit more about what you are trying to do, and
some ideas came to me:

It's easy enough to keep track of batches of serial numbers sent to
retailers, and similarly easy to see if a returned number corresponds to
the stated retailer. It's also easy to keep track of numbers that already
been submitted.

It's not easy to know what to do when you see a valid number that does not
match the retailer: do you automatically issue a summons, or might the
retailer have sold on some of his coupons? It's also not easy to know what
to do when you see a duplicate: in other words, to know whether it's this
second one that is counterfeit or the first one.

Still, if you want merely to flag a suspicious number, then you could have
a structure like this:

BatchesSold(*InvoiceNum, ToRetailer+, StartNum, EndNum)

Then you can easily run a query like this to validate the retailer

PARAMETERS ThisSerialNum NUMBER,
ThisRetailer NUMBER;

SELECT COUNT(InvoiceNum)
FROM BatchesSold
WHERE StartNum <= ThisSerialNum
AND ThisSerialNum <= EndNum
AND ThisRetailer = ToRetailer;

and something like this to make sure it has not already been sold

PARAMETERS ThisSerialNum NUMBER;

SELECT COUNT(SerialNum)
FROM Sales
WHERE SerialNum = ThisSerialNum;

Needless to say, the first query should return 1 and the second should
return 0 for a satisfactory sale.

Reply to this Message

Janet - 16 Nov 2004 17:49 GMT

Okay you have brought up one point that I hadn't thought of.....how do I know
if this is the serial number the purchased or a counterfit if it is the first
time it has come in.... I will need to work that one out. This one might be
very tricky.

I think I've asked to much all at once and I should start with the part that
seems the most impossible to me...

I have a table retailers...this has information on the retailer only

I have a table reference numbers....it has the following
fields...referenceNo, (I manually enter all reference numbers in this field)
Sold;yes/no, Orderfilled;yes/no and orderID

and a table Orders with the following fields....OrderID, Retailer, referenceNO

I've created a form (frmassignreferenceNo)that has fields
OrderID(autonumber) retailer (drop down list)
order qty (unbound, users enters qty sold to retailer here) and a button
save.

I would like that when the person clicks save the following things happen:
The table reference number will be populated with
the qty number entered into the unbound field would cause the retailer
choosen from the drop down box to be entered into the field retailer by that
qty....could be 10 or more records popuated. It would need to check to see
if the reference number is already in sold/yes, on the first occurance of
the reference number sold field equaling no the next qty of records would be
filled for this table.

next....the records that are filled will need the sold field changed to
yes...
and it will next need to enter the OrderID from the form frmassignreferenceNo.

In essence I need to add more than one record at a time to one retailer....

If there is a better way of doing this please let me know....this is all I
could think of.

Reply to this Message

Tim Ferguson - 17 Nov 2004 17:56 GMT

> I have a table retailers...this has information on the retailer only
>
[quoted text clipped - 4 lines]
> and a table Orders with the following fields....OrderID, Retailer,
> referenceNO

I suggested one schema design...

Having a table for every coupon even before it is sold on is possible, but
my instinct would be to create it when it's returned to you. At that point
it becomes a Sale, before then it's just one of a Batch passed to a
Retailer. Generally speaking, creating a large number of rows in a table
just for the sake of having them is a Bad Idea. In any case, the fact that
coupons 20950 to 20999 were sold to Acme Coupons is One Fact (not fifty
facts) and therefore ought to be recorded in One Field.

In the end, either solution has its advantages and its drawbacks, and only
you know enough about your business needs to evaluate them.

Best of luck

Tim F

Reply to this Message

Janet - 29 Nov 2004 14:33 GMT

yes this is a great suggestion thank you. Here is how I accomplished this
task as I needed, I thought I'd post it in case someone else is wondering how
to add multiple records at one time. Now I just need to figure out how to
add different code so that I can move the database around without a problem.

Private Sub btnsave_Click()
On Error GoTo Err_btnsave_Click
Dim adoConnection1 As ADODB.Connection
Dim adoRecordset1 As ADODB.Recordset
Dim connectString1 As String

'—Create a new connection --
Set adoConnection1 = New ADODB.Connection

'—Create a new recordset --
Set adoRecordset1 = New ADODB.Recordset

'—Build our connection string to use when we open the connection --
connectString1 = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data
Source=C:\Documents and Settings\folder\db.mdb"

adoConnection1.Mode = adModeReadWrite

adoConnection1.Open connectString1

adoRecordset1.Open "tblReference", adoConnection1, , adLockOptimistic

Dim Range As Integer

Range = CInt(Form_frmAssignOrderNo.Text6)

Do While Not adoRecordset1.EOF

If adoRecordset1!ProductSold = False Then

Dim x As Integer

For x = 1 To Range

adoRecordset1!ProductSold = 1
adoRecordset1!OrderID = Form_frmAssignOrderNo.OrderID
adoRecordset1.Update
adoRecordset1.MoveNext

Next x
MsgBox (Range & " Reference Numbers added")

GoTo CloseRex
End If

adoRecordset1.MoveNext

Loop

CloseRex:

adoRecordset1.Close
adoConnection1.Close
Set adoRecordset1 = Nothing
Set adoConnection1 = Nothing

End

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_btnsave_Click:
Exit Sub

Err_btnsave_Click:
MsgBox Err.Description
Resume Exit_btnsave_Click

End Sub

"> I suggested one schema design...

> Having a table for every coupon even before it is sold on is possible, but
> my instinct would be to create it when it's returned to you. At that point
[quoted text clipped - 10 lines]
>
> Tim F

Reply to this Message