Question Best way to set this up

[xirokx]

Hi there,

I need to setup a simple invoice database for a movie store so far I have two tables:

- customers
- invoices

Points:

1) Each customer rents numerous movies each month
2) At the end of each month they are sent an invoice detailing the movies they rented, plus rental cost for that duration
3) after i have entered the individual movies each customer has rented I want to run a "report" (or is it a query?) that will add up the total cost for that customer...I think thats the way it will work?
4) finally, I also want the option to print the report or query, or email it direct to the customer

I have so far setup both tables with the data required but I am stuck on points 3 and 4

Please can you guide me to do this?

I know I am close, just been out of touch with ACCESS so need to familarise myself and know its a breeze for you experts..

Thanks in advance..

 

[John Big Booty]

Why not use an off the shelf accounting package rather than trying to re-invent the wheel There are some good cheap packages that will do what you want, and more, straight out of the box.

 

[xirokx]

primarily because I have the time to set this up myself

It would be good practice to get me back into ACCESS

It will make me feel good when I have done it

so back to my post, can anyone advise how best to do this?

 

[spoole]

Why not use an off the shelf accounting package rather than trying to re-invent the wheel There are some good cheap packages that will do what you want, and more, straight out of the box.

This would be the best option, but lets give it a try.

The easiest way to run a report would be to base it on a select query. Then use the report wizard and select the query you created to design the report.

But before we start playing around with that here are a couple of things to think about:

How do you track the films? i.e where does the data for rental time come from, how do you determine if a title has been received back to the store?

If at the cut at the end of the month a title is still "out" (being rented) you will need to put that on next months invoice.

What happens if you database corrupts and you cant bill anyone?

 

[xirokx]

How do you track the films?

Currently when a customer rents a film, a small piece of paper is completed and left in a rentals box, when they return the film I write the number of days the film was rented and total rental cost

End of the month rentals - so if someone rents a movie on the 30th june, I put then invoice them the following month in July to make my life easy

I would have to backup the database incase of corruption.

I also want to be able to run a query that allows me to look at old customer invoices throughout the year.

Now I realise its not the ideal way of doing it but it works so I am happy with it.

 

[Lightwave]

Number of issues here but I would go about setting this up as follows

I would start with three tables (my own take on suggested fields)

TblFilms
PKID
FilmName
Category
StockStartDate
StockFinishDate
AgeCategory


TblCustomers
PKID
Forename
Surname
Address
DateofBirth
ProofIDconfirmed

TblRentals
PKID
FilmLinkID
CustomerLinkID
RentalDate
RentalReturnDate
Charge

Now to get any monthly invoice for an individual customer it would be a case of creating a select query from the tbl rentals table for each customer ensuring that you select only those records that have a RentalReturnDate within the month in question. You could even not store the charge and get the system to calcate it off the cuff..

There are problems with this set up. You will not have set invoices stored. Just the lines within invoices. In terms of good database design this would be the logical way to do it as it minimises stored information and reduces duplication however in terms of legal requirements for taxation and revenue this would not be suitable. You may be able to get round this by creating pdfs of the reports that create the Invoices and storing these. As the actual underlying information is stored I could see that there shouldn't be any problem in not having information available as invoices could be re-created. The problem is they could be very easily retrospectively altered. But the creation of PDfs would help this. I'm not up on my General Agreed Accouing Policy enough to advise you on this. You may need to seek professional advice on this to avoid getting yourself into trouble.

As you can see designing a system that will do it is not that hard. Designing a system that complies with all aspects of the law is incredibly hard and why the majority of people use off the shelf when dealing with stock / accounting and HR. I still see your reasoning as entirely applicable I myself started out designing systems for exactly the same reason.

Points 3 and 4 are actually fairly technical procedures the exact way in which you do these will depend heavily on the structure have for your tables.

There are lots of articles on
Creating Queries &
Creating Reports

Take it as far as you can and if need be use the search command in here to look up specific problems you hit.

 

[xirokx]

thanks for your advise

There are problems with this set up. You will not have set invoices stored. Just the lines within invoices. In terms of good database design this would be the logical way to do it however in terms of legal requirements for taxation and revenue this would not be suitable.

could I not export each invoice to excel format and then save them in for example "june 2010 invoices" folder and so on so that I can keep records?

Now to get any monthly invoice for an individual customer it would be a case of creating a select query for each customer ensuring that you select only those records that have a RentalReturnDate within the month in question

thats it, once I have the query setup I would then need to change the date each month right so that it corresponds with the current month?

 

[Lightwave]

could I not export each invoice to excel format and then save them in for example "june 2010 invoices" folder and so on so that I can keep records?

Yes you could absolutely I would probably creat pdfs but xl is essentially the same

thats it, once I have the query setup I would then need to change the date each month right so that it corresponds with the current month?

Thats it exactly

Adding up what each person owes could be done in the report

You have a series of lines each relating to a rental
Film name / Start date / Finish Date

The report calculates the charge based on (finish - start) X rate = rental charge
The report then adds all the charges to give a total

 

[spoole]

thanks for your advise



could I not export each invoice to excel format and then save them in for example "june 2010 invoices" folder and so on so that I can keep records?



thats it, once I have the query setup I would then need to change the date each month right so that it corresponds with the current month?

Could he print two invoices of, one to be sent out and keep 1 paper copy in a folder and retain all slips of rental paper to get him round the legal stuff?

 

[Lightwave]

Could he print two invoices of, one to be sent out and keep 1 paper copy in a folder and retain all slips of rental paper to get him round the legal stuff?

Absolutely go old school. Actually think in the UK you have to have paper copies anyway.

 

[spoole]

Is there a barcode on the physical film? (we are talking DVD here?)

You can save yourself a lot of time with one using a simple hand held barcode scanner.

 

[xirokx]

thanks for the guidance, I will continue with my project later this afternoon...

No doubt I may need to return here for some clarity or further guidance

Thanks in advance for your help and support