[Newbie Question] - Storefront forms and the issues they present

[Peavers]

Hello

I'm pretty new to Access. I need to just get through this one setup and then I never plan to use access again but I am rather stuck :banghead:. I get the feeling I might be missing a core concept somewhere. I come from a Java background, so went into access thinking of tables as classes and queries as methods. I think this was my biggest mistake...

Anyway, onto the question.


First question:

The database is for a video store.

How do I go about automatically updating a stock table each time a video is rented out and then returned? This all needs to be form driven...

I have read a lot about this, and seems that I shouldn't keep this information in a table but rather just use queries when needed. My issue with this though is I need to be able to tell how many of one item I have in stock, how many I SHOULD have (account for missing items) and how many are currently rented. Can all this be done with queries and no table holding information?

I have further questions but think I will keep this post to one at a time to keep me on track.

I have included my database in a zip file. I know its bit of a mess at this stage but bare with me!


Cheers for any advise in advance.

 

[PaulO]

You'll need a combination of Tables and Queries, plus of course some Forms and Reports

You'll need a MASTER Videos Table (Serial; Title; Category/Genre; StockedSinceDate; etc) that logs Master Details of each and every video. I know you have multiple copies of each Video but (if you haven't already) you'll need to assign unique Serial numbers to each video and print/stick this code onto the video case in the same way that Libraries do with books.

When a Video is booked out, this is logged in a RENTALS table (Serial; Borrower; OutDate; RentalAmountPaid; Comments; InDate). When the Video is returned the Indate is completed.

By Querying the MASTER and RENTALS Table you should be able to ascertain, for example:-

Total Number of Videos held, by Title
Videos currently out on Rental
Number of Rentals achieved per Video (most/least popular)
Non-Returned (Lost) Videos
Top Borrowers
Worst Borrowers (most often late)
etc