Loan Database

[ndxo]

Hi, for my A2 Levels i need to create an access system. I would like to be able to do a loan database based on a real client, and if i can get what they want it will be great. however my knowlege of stock control is poor.
Here is a basic outline of what i want to do;
Be able to manage stock levels

• Know what equipment is out on loan
• Each equipment has a different serial number, i dont want to create loads of records for the same item e.g. Nikon D200 each camera has a different serial number.
• Dont want anything about suppliers e.g. where the stock is coming from.
• Store little information about the customers.
• When they bring the equipment back be able to simply and automatically add it back on.
• Be able to select more than one item for a customer to loan out.

It would be much apreciated if someone can help me
Thank you

 

[CBrighton]

What exactly do you need help with?

 

[ndxo]

i would like help with how to set up a stock control database.

 

[CBrighton]

As an A2 level student I assume you have some knowledge. You should approach it the same as any other database.


But I meant specifically, are you looking for help with the table structure? Perhaps you are asking for advice on the best way to mark a record as out on loan as opposed to available for loan?


"How to set up a stock database" would be a long, long discussion, so I am trying to find out exactly what you are unsure about so people can give you the help you need.

 

[ndxo]

i have a code which can actually manage the stock levels, so i am okay with that. What would be useful is a way adding the loaned stock item quickly without actually having to basically rebook it back in as this is what my IT teacher said that i will have to do.

 

[CBrighton]

ok.

In order to advise you on the returning process we need to know about the loaning process (e.g. is it a yes/no field in the main table which says if an item is "in" or "out", or is the record moved to another table while "out", etc.

Basically we need to know how the data is before the item is returned and how it should be after it is returned, then we can help create a process which takes you from one to the other.

 

[ndxo]

The code that i am using, allows the stock to be slelected in the booking table, a new record is then created for every loan that i have. And the stock levels are measured by using two figures; how many you have and how many are out on loan and you basically calculate how many they have at this moment in time.

 

[CBrighton]

Based on what you have said, when a loaned item is returned all that has to be done is the record in the booking table being either edited (e.g. a yes/no field which states whether the loan is current) or deleted (but this way there is no historic record of items loaned).

I'd guess an ActiveLoan field (yes/no) or a ReturnedDate (date/time) field in the booking table is the best bet, I'd favour the date myself. That way all you need to do is alter the availability calculation to only look at items in the bookings table without a historic return date (as anything returned no longer effects the availability).

 

[ndxo]

Thanks for your help i'll try this solution and see what happens. Thanks again