Banquet Hall Rental Database - Custom ID

[Ryanm0085]

I am designing a database for our Fire Company's Rental Hall. I would like to use the "ID" as an "Invoice Number" but include the year. So for the 1'st rental of 2015, I would like the number to be 15-001, the second, 15-002, and so forth using the auto advance each time a new report is entered. Is this at all possible?

[NTC]

possible with custom code. a number can never have leading 0s. the auto increment feature given to you by a database is integers i.e. numbers - so your easiest solution is to live with an autonumber field type 1,2,3,

You'll have a date field anyways and so the 15- is actually redundant and not needed anyways..... but if you want it, and don't want to get into coding - just enter it manually. Just a heads up on an automated approach - what happens when there is a cancellation?? is 15-002 to be reused?...it can get very tricky for an automated solution to behave the way you want.... so just FYI....

[orange]

Further to NTC's comments. Don't embed too much significance in codes. Relational database and Access works very well with atomic fields --that is 1 fact 1 field.
Here is a very good tutorial at RogersAccessLibrary that helps with database concepts and database design. It starts with a description of an issue to be automated and leads you through the process of database design to meet the stated requirements. By spending an hour working through the tutorial you will learn things to help you with your database. (There is a solution included).

Good luck with your project.

[Ryanm0085]

I agree with NTC and thank you orange for ur suggestion.

I am creating this database for a volunteer fire company so I am trying to prevent from having to bring someone in to make this database.

I had a pretty good plan, but looking through this forum im not really sure what to do now. All I really am looking to do is be able to enter the renters information, enter the cost of the rental, and be able to print an invoice out with all the charges. The initial invoice would show what is due on the date of the rental plus and deposits left. then somehow we would need to go back in, make the final adjustments, and print a final invoice. I was thinking about just having an initial form for all the info and cost. Then when the pricing needed to be updated, the operator could go back in and just make the adjustments and just reprint the invoice. Not sure if there is a better way to do this.

The only other thing is there would be options for using the kitchen. if the kitchen is chosen, it would have to show up on an itemized list on the invoice. Other than creating text boxes linked to that data and leaving them blank when not chosen, I am not sure how to accomplish this. It seems like a simple task but I am unsure. I am not really sure what a query is or what it does, but I saw where someone people recommended that.

Id assume the invoice would just be a report designed to look like an invoice. Not really sure how to do the itemized list though. Any help would be greatly appreciated.

[orange]

1. Did you work through the tutorial?
2. Show us your

pretty good plan,

2. Write a plain English description of WHAT your database is going to support. Write the description in plain English, no jargon, and step through each of the things you think your database is about.

eg
We are a small volunteer fire department. We have a hall (size info) that we would like to rent to (who are the proposed clients). Rentals would be for (events, long term...??). Our projected fees would be (rental fee and deposits, conditions). We would have contracts between "the fire chief" and the qualified renter. Renter qualifications need to be identified). The proposed database would record information about the hall, rental contracts, renters, fees, deposits, invoices and would be used to print Invoices. The hall has kitchen facilities including (list of things important to the kitchen that could be of interest to the renter). and bla bla bla...


You might want to sort out things like insurance, injury/liability, damages to the hall while hall is rented. There's more to this than forms and a printed invoice.
Get a detailed plan together if you're going to pursue this.

[Ryanm0085]

Okay. When we rent out our hall , the only type of receipt we have is a hand written receipt. Also, weeks before our rentals, we require a security deposit and then the tenant will settle up the day of the rental.

I want to create a form that the person in charge can use to input all the customers information including name, address, and phone number. Then on the same form, just to make it easy, all the rental info including the flat rate for 4 hours, the price of additional hours and how many, the amount of the required security deposit, how much the tenant is leaving as a rental deposit, and the price if they r using the kitchen.

I have the form pretty well laid out and the main information set up on the table. I am pretty good with using formulas and actually have the table set so when I enter the information regarding costs, extra hours, and all the deposits and stuff, it will actually calculate the balance.

A tenant is required to leave a $100 deposit before the actual day of the party. So what I am looking for, is when we initial put all the information in, I need it to print out an invoice that would have a complete break down of charges showing the deposit that was made, the security deposit that is left, and the remainder of the balance.

Then, the day of the party, we will need to go back in and show that it was paid and the security deposit returned. I am pretty good all the way up until I need to go back in and make the adjustments. My original plan was to enter everything in using the form for the initial deposits. Then, on the day of the party just go back into the same form for that event and update the payment and have a button to print the word "Final" or something like that on the invoice.

The invoice seems pretty cut and dry. Our information, their information, a complete break down of what the costs are, and records of how much they have paid. I also need to figure out that if they don't rent the kitchen it doesn't show up on the invoice. The way I was thinking was creating text boxes tied to each description and cost on the invoice. Basically if they aren't renting it, it will still show up but the price will be $0.00 or something like that. This would be the way I would do it only cause I am not really sure how else. So on the form, it would say kitchen yes/no, if they choose yes, it would show up on the invoice with a price. If they chosoe no, it would not. I am not sure if there is an easier way to do this. I figured just stacking text boxes on text boxes to look like a spread sheet.

This is just the start but really all I need right now. I need to get this together so we can start processing our hall rentals. As far as insurance and that stuff, they are required to provide their own or buy 3rd party. Nothing is paid to us for that. Damage, however, might be something I would look at down the line.

[orange]

1. Did you work through the tutorial?

When you design a database - or a house, or an airplane, or a shopping center.. You start with plan. Initially it may just be an artist's concept/a picture of the proposed "product". Then you focus on your priorities ---the MUST HAVE items/parts and get to define these in detail. You are creating an overall blueprint. It's just that some areas are more detailed than others. But all of the pieces are identified such that all fit in the general plan/blueprint- you build based on the priorities, but you have allowed for the parts that will be developed later.

Perhaps there will be a 3 storey house, but you don't build a bungalow, then decide to put on a second then 3rd storey later. It's in your initial plan. Same with the airplane, it needs wheels and brakes and wings and they all should be part of the blueprint even if some details are vague before you start to build...

Good luck.

[June7]

Invoice design as you describe won't work, not without complicated queries and/or code. Invoice should be based on records. If there is no record to show '0.00 charge for kitchen' then there is no data for the invoice.

[Ryanm0085]

I don't want to take up everyones time. I just read the tutorial and have a pretty good understanding of relationships now ad a better understanding on what I need to do. I am going to sit down and write up a complete and full description of the data needed and the processes needed. I will post it when its done. Thank you everyone for your help so far.

[orange]

Good luck. It can be an iterative process. Start from 30,000 ft, then progressively add detail.

Remember - Nobody ever sat down and wrote the final version on their first attempt.

[Ryanm0085]

Okay here goes nothing.
Our hall rentals really don’t require that much informationso I’m thinking it should be a pretty easy setup with a little guidance.
The first step is getting the customers information.I am thinking this should have its own tableand obviously a form to fill it out.Theinformation would include First Name, Last Name, Street Address, City, State,Zip, Phone Number, and email.I am notreally sure what the Primary Key Id would be.Maybe a customer ID number or something like that as it would bepossible to have more than 1 person with the same name.
The next thing we would do is meet with the customer, goover all the rental information and provide the customer with a quote.The rentals are broken down as follow:Minimum 4 hour price ($350), extra hour price ($100), DJ Rack ($50).That is really all the charges that can beacquired with a rental. Also important to the rental is the date and time ofthe rental, as well as how the customer is paying(Cash, Credit, Check).Wealso require a $100 rental deposit which is subtracted from the overallcost.This saves the customers date andinsures they have the hall that day.Also,we require a $150 security deposit that is refunded at the end of the rental ifall is well.
On the first contact, I would like to enter the customerscontact information in and save it in case they decide to rent again fromus.Next, I would like to open a form tofill out a “Quote.”The quote wouldinclude all the customers contact information, the date of the rental, thestart time and finish time, the total hours, the 4-hour price, the cost ofadditional hours if any, the amount of the deposit left (which would be 0 sinceit is just a quote), and the $50 if they are renting the DJ Rack.I would like this to be able to print as aninvoice and be saved in case they decide to go through with the rental. At thetop somewhere I would have a status that says QUOTE.
Now, if the customer agrees with the rental, I would like tomake the quote active.At this time wewould have to take a $100 deposit from the customer to save the date. We Iwould need to print out an invoice that has the updated total showing they leftthe deposit and the remainder that’s due on the day of the rental.The status would now show: ACTIVE.
If the customer decided not to accept the quote and turndown the rental, after a month or so I could just go back in and delete inmanually.
Now on the day of the rental or a few days before, we acceptthe remainder of the balance, plus now we accept the $150 securitydeposit.So now the invoice should showStatus: FINAL, and show that we owe $150.
And finally, after the rental has concluded and we returnthe security deposit, show a updated balance of $0 and show the status: CLOSED.It would be nice at this time to be able toenter any acquired damage costs and have it subtract from the security deposit.Say they do an estimated $100s worth ofdamage, it would show Damage - $100 and then a balance of $50 to be returned.
I am trying to make this as easy as possible. 1. For myselfto be able to make changes and not overwhelm myself, and 2. To make it easierfor other members to conduct hall rentals on their own.
My thought is to have a Customer Table with all the customerinfo, a Price Table to have all the costs of the rentals.Having a price table would be nice so wecould go in and update if we decide to raise the price.We would just have to make sure it onlyupdates future rentals.
As far as going from quote, active, final, and closed, Iwould assume i would create a table for each containing all the sameinformation: Customer info, pricing info, date, time, deposits.It would start out in the quote table, thenwhen accepted by the customer, move to the active table, and so on until itreaches the final.
As far as contracts, insurance and other stuff like that, I amnot worried about it right now.My mainconcern is to keep track of money in/out and be able to provide receipts forall transactions.I have some basicknowledge with access but no where near what I probably need to pull thisoff.If I can get help laying out andplanning this database, I think I will be able to make adjustments andadditions where needed.Thank you allagain for all your help.Being a volunteerfire company really makes it hard to fund funding to hire a professional orfind someone to come in and do this for us.
Ryan

[orange]

Good stuff.

Here is a free model that deals with renting a community center. I think it will give you a basis for your data model/database. Use the parts you need and don't feel everything in that model represents your requirement. It is a generic model to help developers see the tables and relationships that could be involved. Your requirements will determine what your model will contain.

Did you work through the tutorial ?

There is a graphic here that shows how Quote, Order and Invoice can be represented as a "type of" Statement.

Good luck.

[Ryanm0085]

Thanks orange. Yes I did work through the tutorial. I started a chart and am working on relationships. Thank you for your help!!!

[orange]

Glad to help. Good luck with your project.

Here's a list of things you may want for reference.


Principles of relational Design http://forums.aspfree.com/attachment...2&d=1201055452
Entity Relationship Diagramming http://www.rogersaccesslibrary.com/T...lationship.zip

Video tutorials(Langer):
If you are quite good at learning by Watching/Listening
rather than reading.

http://www.youtube.com/watch?v=IiVq8M5DBkk Logical data modeling

http://www.youtube.com/watch?v=BGMwuOtRfqU Candidate key

http://www.youtube.com/watch?v=ZiB-BKCzS_I Normalization

http://www.youtube.com/watch?v=pJ47btpjAhA Normalization example

http://www.youtube.com/watch?v=q3Wg2fZENK0 1st Normal form

http://www.youtube.com/watch?v=vji0pfliHZI 2nd Normal form

http://www.youtube.com/watch?v=HH-QR7t-kMo 3rd Normal form

http://www.youtube.com/watch?v=q1GaaGHHAqM E_R Diagramming

https://www.youtube.com/watch?v=lXAG...6FE5448948D9B4 E-R Diagramming Price and Quantity

Complete set of tutorials on Acc2010.
https://www.youtube.com/playlist?lis...FoilxbUY0yUqZP