how to set up inventory db with person db and checkout inventory s

[Guest]

What is the best way to set up the following info. We have an inventory db
which consists of item#, name,s/n etc. Then we have a db of people (names,
addresses etc) which we also use to tract hours worked. We would like to be
able to link these two so that when someone wants to check out equipment we
can pull the info from both db to set up equipment checkout and return. Would
also like to be able to then print out a form with the list of items on it
for the person to sign.

Is this a simple process or does a program have to be written for it as
someone suggested to me? I would think access could do this.

Thanks for your help.
Kay

 

[Albert D.Kallal]

What you need to have is add a booking table.

DateTimeStart DateTimeEnd EmplyoeeID InventoryID


The above is all you need. You then build a sub form in the employee form
(or a button to launch the booking form).

That form would allow you to enter the start Date + time (since you might
just book for a after noon for example). And a End Date+time. So, a person
could book from 2 pm Thursday to 11 am Friday.

The trick in a booking system is to only store the start and end date of the
booking. And, of course what inventory item (could be a combo box that
selects the inventory item, but stores the id).


And, to prevent collisions, the logic here is quite simple:


A collision occurs when:


RequestStartDate <= EndDate
and
RequestEndDate >= StartDate


The above is thus a rather simply query, but if any collision occurs, the
above will return records..and you simply don't allow the booking. In other
words, since we NEVER allow booking with a collision, then the above simply
statement will work for us.


dim strWhere as string
dim dtRequeestStartDate as date
dim dtRequestEndDate as date


dtRequestStartDate = inputbox("Enter start Date")
dtRequestEndDate = inputbox("Enter end date")


strWhere="#" & format(dtRequestStartDate,"mm/­dd/yyyy") & "# <= EndDate" & _
" and #" & format(dtRequestEndDate,"mm/dd­/yyyy") & "# >= StartDate"


if dcount("*","tableBooking",strW­here) > 0 then
msgbox "sorry, you can't book
....bla bla bla....


The above is just an example, and I am sure you would build a nice form that
prompts the user for the booking dates. However, what is nice here is that
the simple condition above does return ANY collision....

And, by simply deleting a record, you can cancel a booking, and not a bunch
of hard to write updates, and counters, and flags etc. needs to be set. So,
essentially, to book, or un-book, you just add, or delete booking records
from your booking table. As mentioned, if you use a date+time field, then
you can book for a few hours, a few days...for a few weeks..and you don't
have to change the above code logic.

So, building the forms and interface will take some work..but to check
in/out inventory items and attach them to a employee is rather simple, and
only a few lines of code.

 

[Guest]

Thank you, I will try that. Sometimes I think I know enough about access to
be dangerous but I can set up a booking tble and go from there.

Thank you.