Loan function

[EMILYTAN via AccessMonster.com]

Can anyone give me a suggestion how to create loan form where user can loan a
thing for example loan 5 pencil and then can return separately for example
return 1 then return 4?

 

[Graham Mandeno]

Hi Emily

Before you design a form, you must first design your database (tables and
relationships)

And before you can design your database, you must thoroughly understand the
system that you are modelling.

Here are a few questions you need to answer:
Are these "pencils" all the same, or do you have several different
"types" of pencil?
Are different pencils of the same type identical, or do they have
titles/catalogue numbers/serial numbers?
Do you have an (effectively) unlimited supply of each type of pencil, or
do you reach a point where you must say "sorry, no more pencils of that type
are available"?

The answers to these questions (and probably many others) have a huge
bearing on how you design your database. Once you have answered them, you
can create tables to store your data and relationships to link the tables.
Only THEN can you start to think about the design of the form.

But the good news is, if you have modelled your data well then the design of
the form is a piece of cake

 

[EMILYTAN via AccessMonster.com]

The pencils are not the same,. They will have pencils, eraser, pen and etc.
Each of the item has a unique serial number to identify them. I will have
unlimited storage, so I don't have to care about the quantity available...
Besides, I would likfe to add a LoanID for each of the transaction that is
uniqeu per person..Just like when search on LoanID, everything related under
that transaction will be viewed...
Are you sure is piece of cake...It make me struggle for a few days to come
out the idea...but at the still blank.....haihz....
Glad to see your reply...

 

[Graham Mandeno]

Hi Emily

OK, so you need a table of ItemTypes, with a primary key (could be an
autonumber), the name of the item type (pencil, pen, eraser, etc), and a
description.

Then you need a table of items, with a primary key (PK) to uniquely identify
each one (this could be another autonumber, or it could be the unique serial
number). This table also requires an ItemType field corresponding to the PK
in the ItemTypes table. You create a one-to-many relationship between
ItemTypes and Items.

Then you need a Borrowers table. Once again this must have a unique PK, and
other information about the borrower: name, address, etc.

Next you need a Loans table. This contains the borrower's PK and the item's
PK, and also the date borrowed, the date due back, the date returned, and
any other data related to that particular loan (for example, fees for the
loan).

Now, items are unavailable for loan if there exists a record in the loans
table with a date borrowed, but no date returned. The converse is true to
find which items *are* available.

For example, if the ItemTypeID for a "Pencil" is 1, then you can get a list
of the available pencils with the following query:

Select ItemID from Items where ItemType=1
and not exists (Select ItemID from Loans
where Loans.ItemID=Items.ItemID and DateReturned is Null);

So, when a borrower requests 3 pencils, 2 pens and 1 eraser, you can select
from the "available" list for each item type and create a "loan" record with
that ItemID and the BorrowerID and the DateBorrowed.

As the items are returned, you select the item from a list of outstanding
loans for that borrower, and mark it as "returned" (which fills in the
DateReturned field).

Once you have created this structure, you have the ingredients for your
cake! Come back here if you need some help with the recipe ;-)

 

[EMILYTAN via AccessMonster.com]

I have created the structure already...haha
But I deleted the item type because I plan to use only the Serial Number to
identify it because admin people are already used to it...haha..
But how am I suppose to mark it...especially I have QuantityOwed attribute,
and LoanedQty?

Hi Emily

OK, so you need a table of ItemTypes, with a primary key (could be an
autonumber), the name of the item type (pencil, pen, eraser, etc), and a
description.

Then you need a table of items, with a primary key (PK) to uniquely identify
each one (this could be another autonumber, or it could be the unique serial
number). This table also requires an ItemType field corresponding to the PK
in the ItemTypes table. You create a one-to-many relationship between
ItemTypes and Items.

Then you need a Borrowers table. Once again this must have a unique PK, and
other information about the borrower: name, address, etc.

Next you need a Loans table. This contains the borrower's PK and the item's
PK, and also the date borrowed, the date due back, the date returned, and
any other data related to that particular loan (for example, fees for the
loan).

Now, items are unavailable for loan if there exists a record in the loans
table with a date borrowed, but no date returned. The converse is true to
find which items *are* available.

For example, if the ItemTypeID for a "Pencil" is 1, then you can get a list
of the available pencils with the following query:

Select ItemID from Items where ItemType=1
and not exists (Select ItemID from Loans
where Loans.ItemID=Items.ItemID and DateReturned is Null);

So, when a borrower requests 3 pencils, 2 pens and 1 eraser, you can select
from the "available" list for each item type and create a "loan" record with
that ItemID and the BorrowerID and the DateBorrowed.

As the items are returned, you select the item from a list of outstanding
loans for that borrower, and mark it as "returned" (which fills in the
DateReturned field).

Once you have created this structure, you have the ingredients for your
cake! Come back here if you need some help with the recipe ;-)

The pencils are not the same,. They will have pencils, eraser, pen and
etc.

[quoted text clipped - 41 lines]

 

[Graham Mandeno]

Hi Emily

You basically need to create a record in your Loans table for each item that
is borrowed.

There are many ways to do this. One is to create a form based on your
Borrowers table with a linked subform based on the Loans table. You can
select an item to be borrowed from an "available" list, and enter the
DateBorrowed and DateDue and any fees or charges in a new record in the
subform.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have created the structure already...haha
But I deleted the item type because I plan to use only the Serial Number
to
identify it because admin people are already used to it...haha..
But how am I suppose to mark it...especially I have QuantityOwed
attribute,
and LoanedQty?

Hi Emily

OK, so you need a table of ItemTypes, with a primary key (could be an
autonumber), the name of the item type (pencil, pen, eraser, etc), and a
description.

Then you need a table of items, with a primary key (PK) to uniquely
identify
each one (this could be another autonumber, or it could be the unique
serial
number). This table also requires an ItemType field corresponding to the
PK
in the ItemTypes table. You create a one-to-many relationship between
ItemTypes and Items.

Then you need a Borrowers table. Once again this must have a unique PK,
and
other information about the borrower: name, address, etc.

Next you need a Loans table. This contains the borrower's PK and the
item's
PK, and also the date borrowed, the date due back, the date returned, and
any other data related to that particular loan (for example, fees for the
loan).

Now, items are unavailable for loan if there exists a record in the loans
table with a date borrowed, but no date returned. The converse is true to
find which items *are* available.

For example, if the ItemTypeID for a "Pencil" is 1, then you can get a
list
of the available pencils with the following query:

Select ItemID from Items where ItemType=1
and not exists (Select ItemID from Loans
where Loans.ItemID=Items.ItemID and DateReturned is Null);

So, when a borrower requests 3 pencils, 2 pens and 1 eraser, you can
select
from the "available" list for each item type and create a "loan" record
with
that ItemID and the BorrowerID and the DateBorrowed.

As the items are returned, you select the item from a list of outstanding
loans for that borrower, and mark it as "returned" (which fills in the
DateReturned field).

Once you have created this structure, you have the ingredients for your
cake! Come back here if you need some help with the recipe ;-)

The pencils are not the same,. They will have pencils, eraser, pen and
etc.

[quoted text clipped - 41 lines]

example
return 1 then return 4?

 

[EMILYTAN via AccessMonster.com]

Hi Graham,

I have created a structure to get the loan form. Thanks to your idea.

In the main form (Borrow) I have ItemNum , Quantity Loaned, and quantityowed
(quantity loaned - quantity returned)

Then I created another subform which has the transaction for the particular
item being loaned.
I have quantity returned here.

Problem is, how am I supposed to get the calculated quantityowed keep on
deducting while the quantity returned is in the subform?
Hate dealing with subform....haha

Hi Emily

You basically need to create a record in your Loans table for each item that
is borrowed.

There are many ways to do this. One is to create a form based on your
Borrowers table with a linked subform based on the Loans table. You can
select an item to be borrowed from an "available" list, and enter the
DateBorrowed and DateDue and any fees or charges in a new record in the
subform.

I have created the structure already...haha
But I deleted the item type because I plan to use only the Serial Number

[quoted text clipped - 58 lines]

 

[Graham Mandeno]

But... hang on! You said that every item has a unique serial number. I
took this to mean that items are indivisible. Do you mean that someone can
borrow a "pencil" with serial number "XYZ" and return it in pieces, one
piece at a time???

Perhaps we should dispense with the stationery analogy and you should tell
us what it really is that people are borrowing!
--


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

I have created a structure to get the loan form. Thanks to your idea.

In the main form (Borrow) I have ItemNum , Quantity Loaned, and
quantityowed
(quantity loaned - quantity returned)

Then I created another subform which has the transaction for the
particular
item being loaned.
I have quantity returned here.

Problem is, how am I supposed to get the calculated quantityowed keep on
deducting while the quantity returned is in the subform?
Hate dealing with subform....haha

Hi Emily

You basically need to create a record in your Loans table for each item
that
is borrowed.

There are many ways to do this. One is to create a form based on your
Borrowers table with a linked subform based on the Loans table. You can
select an item to be borrowed from an "available" list, and enter the
DateBorrowed and DateDue and any fees or charges in a new record in the
subform.

I have created the structure already...haha
But I deleted the item type because I plan to use only the Serial Number

[quoted text clipped - 58 lines]

example
return 1 then return 4?

 

[EMILYTAN via AccessMonster.com]

Ok...is because it will have its own code. For example similar item with
similar code.
So, in main form, I will have the loan quantity with the quantity owed.
So, in subform will be the return transaction. Every time the quantity is
being returned it will deduct from the main form in the quantity owed....
But how to deduct from that?

But... hang on! You said that every item has a unique serial number. I
took this to mean that items are indivisible. Do you mean that someone can
borrow a "pencil" with serial number "XYZ" and return it in pieces, one
piece at a time???

Perhaps we should dispense with the stationery analogy and you should tell
us what it really is that people are borrowing!


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

[quoted text clipped - 29 lines]

 

[Graham Mandeno]

OK, so you DO need an ItemTypes table.

Let's say (sticking with the stationery analogy) that someone borrows five
pencils and two pens. The pencils are item numbers 1,3,4,6 and 8. The pens
are item numbers 5 and 9.

By counting the number of Loan records for that borrower with no
DateReturned, you can tell how many items are outstanding - 7.

By using another query that joins Loans with Items and grouping by ItemType,
you can count the number of each type of item outstanding - 5 pencils and 2
pens.

If the borrower returns pencils #3 and #6 and pen #9, then this query will
automatically give the updates totals: 3 pencils and 1 pen.

There is no need to update the "loan quantity" and the "quantity owed"
because those totals can be calculated from the other data.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

Ok...is because it will have its own code. For example similar item with
similar code.
So, in main form, I will have the loan quantity with the quantity owed.
So, in subform will be the return transaction. Every time the quantity is
being returned it will deduct from the main form in the quantity owed....
But how to deduct from that?

But... hang on! You said that every item has a unique serial number. I
took this to mean that items are indivisible. Do you mean that someone
can
borrow a "pencil" with serial number "XYZ" and return it in pieces, one
piece at a time???

Perhaps we should dispense with the stationery analogy and you should tell
us what it really is that people are borrowing!


Graham Mandeno [Access MVP]
Auckland, New Zealand

Hi Graham,

[quoted text clipped - 29 lines]

example
return 1 then return 4?