How should I do this........

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a Loan Closet DB. If an item was taken from the loan closet, how can
I have the quantitiy of that item decreased from the total quantity and
vis-versa. Is that accomplished in a Query or is it a funtion used with
forms and subforms? I not sure how to attack this. Thank You.
 
How you do this depends on your table structure and how you are processing
the information. What tables do you have that are affected by this change?
What is the record source for your form?
 
I have the Client_tbl and Item_tbl (a list of items in the loan closet)
*Should I put the quantities in this table or create a new one? And a
Item_Info_tbl (characteristics about the items. ie. Code, In, Out, Needs
Repair, and File. I have the main form with the client info and a sub form
with a dropdown box to choose the items and Code and checkboxes for In, Out,
Needs Repair, and File
Thanks for your ideas.
 
Does each item have it's own record in Item_tbl. What I mean, if you have 2
widgets, Is there one record that describes widgets that share a Code
with an on hand quantity or are there two records, one for each widget and
each widget has it's own Code?

In which table do you keep track of who has a widget on loan? Conversly,
how do you know which Clients have widgets on loan?

Typically, if all widgets share a code, then you would use a combination of
3 tables
Client_tbl - Info about clients
Iten_tbl - Info about items (includings quantity on hand)
ClientItem_tbl - A Join table that will tell you who has which items on loan

Client would logically be a many to many relationship to Items because 0 to
many clients may have 0 to many Items on loan.
ClientItem resolves the many to many. Client_tbl is one to many to
ClientItem_tbl and Item_tbl is one to many to ClientIten_tbl.

In the case where each widget has its own record, then you will want two
tables, ItemHeader_tbl and Item_tbl. The ItemHeader describes what a widget
is and the Item describes a specific widget. They would share a Widget Code,
but would each have a unique Widget Serial Number. So ItemHeader_tbl would
be one to many to Item_tbl. Client_tbl would still be one to many to
Item_tbl, but there would be no need for the ClientItem_tbl.

As to quantities. In the first case, quantities would be in Item_tbl. There
would be a Total Quantity that would show how many widgets there are. There
would be an Available Quantity which would be Total Quantity - the number on
loan - number held back for repair, reserved, or what other reasons one item
might not be available. So each time a client takes a widget out on loan,
you would subtract 1 from Available Quantity and when it is returned you
would add 1 to Available Quantity. If a widget were disposed of, you would
subtract 1 from total quantity and 1 from available quantity. If a new
widget were acquired, you would add 1 to both.

Hope this helps. If you have questions, please post back.
 
WOW! You know your stuff.................... Really appreciate your help.
As it stands now, in the Items_tbl each record is a medical item by name.
The Item_Info_Tbl is where I store info from a form and subform. It has
item, Code (4 different codes chosen by a dropdown box) and check boxes for
in, repair, returned. The Client_Tbl has all the client info. I haven’t
done anything
with the total quantities yet because the original DB was just going to be a
DB of what client had what out. Then they gave me the total quantities and
decided they want an inventory DB instead. So it’s probably best to start
the whole DB over from scratch, don’t you think? I understand your math
logic and that’s exactly what I want this DB to do but how are the
calculations done. Do I have to know SQL or VBA? Should I have a Client
form and subform for that client what he has on loan? If I have the
checkboxes, can the quantities be decreased and added depending if the
checkbox is checked or not.
How is this accomplished? It sounds like I might be making this harder than
it is.
Sorry for my ignorance in this. Thanks much!
 
It isn't really that hard. I would suggest a Check Out form. On this form
you would select the client. The record source would be Client_tbl. I would
have two subforms. One that would be based on a table I described as the
ClientItem_tbl so I could add a record to show the client is taking out the
item. It would also show all items he has out. The other subform would be
based on the Item_tbl where you could select the item the client wants to
check out. Then, you would update the quantity on this form. Which quantity
you update will depend on how you are doing the other calculations. I would
probably have an On Loan quantity, and add 1 to that. It would probably take
a little code to manage updating the On Loan quantity, but not that
difficult. Perhaps putting the calculation in the main form when you select
the item. The should be a text box displaying the current On Loan quantity
for the item in the item subform. So the calculation would be in the After
Update event of the control (I suggest a combo box) where you select the
item. It would be something like:

Me.subfItem.txtOnLoanQty = Me.subfItem.txtOnLoanQty + 1
 
Back
Top