Question Access database help, multiple copies

[Sanci]

I have problem with my access database "Video Rental Store", so if anyone can help me, I would appreciate it. Here it goes: One movie have few copies, how to rent copy, ant put it back when it is returned?:banghead: Than you!!

 

[Pat Hartman]

Three of the tables you need to do this are:
tblProducts:ProductID (autonumber, primary key)
Title
Producer
DateReleased
Media (DVD, CD, VCR)
etc.
tblProductItems:
ItemID (autonumber, primary key)
ProductID (FK to tblProducts)
InserviceDate (date this copy was purchased)
CustomerID (FK to tblCustomer)
RentedDate (date this copy was rented)
RemoveFromServiceDate(date this copy was removed from rental stock)
tblRentalHistory:HistoryID (autonumber, primary key)
ItemID (FK to tblProductItems)
CustomerID (FK to tblCustomer)
RentedDate
ReturnDate
DamagedYN

tblProducts identifies each individual recording and tblProductItems identifies instances of a product. Many titles require having multiple copies in stock to keep up with demand. tblRentalHistory tracks the rental history of each Product Item. Your rental form will search for the product and have a subform that shows available copies (tblProductItems.CustomerID is null). You could also show rented copies with an estimated return date. The rental process updates the CustomerID and rentedDate in tblProductItems.

The return process inserts a row into tblRentalHistory including the CustomerID and rentedDate from tblProductItems as well as the return date. You would also mark an item as damaged if necessary and that will take it out of service. The return process then updates the tblProductItems record to set CustomerID and RentedDate to null to make the item available again for rental.

The concept is that the actual item record always contains the information regarding the current rental status and the history table is used for reporting.

 

[Sanci]

So one movie would be (in case there are 3 copies) put 3 times in tblProductItems, but only once in tblProducts?

I see why there are customer_id and rented_date, so I can see who rented movie and when,and maybe when it will be returned.

Can I put Returned or not (Yes/No) field somewhere, so if returned it can set customer_id and rented_date to null, and can I create form(like RentalRecord) that will update all this fields?

Thank you for you help!

 

[Pat Hartman]

You don't need a field in the table that holds ReturnedYN. That is a process in your code. When the user clicks a returned button or unbound checkbox, your code creates the history record and removes the current rental information from the items table. So, when a physical copy is out on loan, the record for it contains the customerID and out date. When it is returned, you copy the rental info to the history table and erase it from the item table and yes, I would do this with a form.

 

[Sanci]

Ok, I understand you. I could build a form for RentalRecord where I would have field with customer_id and customer_name and movies he/she rented. Next to every rented copy I could put button return, and delete button(if copy is damaged, so I can delate it from table). So delete button is simple to create with wizard, but button return I have options code builder, macro builder, and expression builder. Could I do this with expression builder? It's seems to me easiest way, and I don't have experience with codes, and macros.

I'm sorry to keep bothering you, I really appreciate your help!