A difficult taskin Access

[eman]

Introduction:
We are a Non-Governmental Organization (NGO) operating in Somalia. We supply tickets to the needed, our benefictiories, who are mostly war-affected people.
They then use those tickets to buy Food and Non-Food items.
The tickets are therefore in two types: Food and Non-Food.
We receive about 5000 tickets at a time. Those tickets are used to purchase the food items or non-food items. When the tickets run out, we get new more
tickets supplies again in a set of 5000.
The tickets has the following:
- Every ticket has a number in the form of 00000x
- Each ticket has a cash value on it
- On each ticket it is written Food or Non-Food (they cannot use the Food ticket to buy Non-Food Items and the other way around).
- Every ticket used is returned to us by the vendors. We then pay the cash to the vendors. A ticket is used only once.
- We keep all the used tickets for our records.
- Depending on the size of the family, the heads of some families may get 50 to 200 tickets.
- The tickets have five states: In-stock, Issued, Damaged, Stolen, Used.
Automating our work:
We currently do all these transactions manually. We therefore want to set up a Microsoft Access Database that will do the following:
* Record all the tickets when we receive them from our supplier
* Find out where any of the tickets is at any time by searching for the ticket nr in the database.


* When the used ticket is returned, we want to move it to the table called "tblUsedTickets"
* When the ticket is damaged, we want to record it in the table called "tblDamagedTickets". This goes for the other states.
* We want to know how many tickets have been isued to any particular person or group of families over any period of time. For instance, if Bill Gates is the
head of the family that receives tickets from us, how many tickets has he received between date XXX and YYY? How many tickets he still has in his position,
etc? How many tickets has he damaged (for instance, washing his trouser with the tickets in that pocket, etc). Of course, we will only know that Bill Gates
has used a ticket when the vendor returns it to us for the cash.
Our main problems in this Database are:
- How to record the huge number of tickets in the database (creating multiple records at once).
- How to record huge number of tickets for Bill Gates.
* If I am creating one or two tickets for him, I can patiently do it. But if I am creating 100s of tickets for him, then I will have to use a technique that
will create them at once. The good news is that a family heads receives a set of tickets in a squuential order. For instance, tickets #1-200 will be issued
to Bill Gates.
However, if I must be able to find out to whom ticket nr sasy, 5 was issued or a set of tickets, say, 17-30 was issued.
* The same with creating the tickets when they come.
Can anyone help me with the above problem? Please let me know if you want me to make it more clearer if it is not already clear.
Thanks,
Eman

[weekend00]

You need some simple codes to record a set of tickets.

for example, for new tickets, use 2 text box to type in beginning ticket number (text box name "beginNumber") and number of tickets(name "numOfTickets"), then click on a button (name "NewTicket"), in the click event of this button, write following codes:

private sub NewTicket_click()
    dim i as long
    for i=0 to NumberOfTickets
        currentdb.execute "insert into TicketTable ( TicketNumber, state) values ( " & (beginNumber + i) & ", 'In Stock')"
    next
end sub

When the tickets are issued to Bill, just update the state of the tickets.

[eman]

Does this mean that if for instance I want to create 50 tickets that begins with ticket nr 00001, I can use the above code to create those tickets in the TicketsTable automatically when I click on the button "New Tickets"?

[weekend00]

yes. but you need to modify the codes to fit in you database. e.g. the field names, tablenames, textbox names etc.

[eman]

You seem to be well good with MS Access. I will design it and send it for you to look at it. Thanks a whole lot.

[weekend00]

All right. Just post you database .zip file here, in 2003 or earlier version.