newbie with a giant task to complete

[Charli]

Hi, I am an office manager for a non-profit organization, and part of that is
our campground and rental cabins. Right now everything is being done by
hand, and I would like to try to create a database program in Access that
would act as a campground/cabin reservation system.

I am great in every microsoft office program, BUT Access. Is there anyone
out there who has the time and the no how, who would not mind mentoring me on
this project. I would truly appreciate the help. I know how I would like it
to end up being, but no clue as to how to get started.

I am willing to do all the work, just need some guidance, advice, and a few
tips

Thanks

 

[tina]

well, if not a "giant" task, you've certainly bitten off a large chunk -
especially for someone who has no experience with Access, and i'm guessing
also no experience with relational design principles. you can certainly
learn how to do the task you've set for yourself, if you're willing and able
to invest the time and effort it will involve, which will be considerable.
to get started off on the right foot, see
http://home.att.net/~california.db/tips.html, focusing first on tips 1 and
2, in that order.

good luck with your project, and come back to the newsgroups anytime you
have specific questions - we're always here.

hth

 

[Graham Mandeno]

Hi Charli

This is a great place to come for "guidance, advice, and a few tips."

If you really want to do this project yourself and learn from the
experience, you are best to post questions here (or perhaps in one of the
other newsgroups such as tablesdbdesign or formscoding) as they arise. That
way, there will always be a willing expert on hand to help and others might
benefit from the exchange.

To get started, you must first design your "data model". IMO, this is the
most important step in building any database application. If you start with
a good model, everything will fall into place, but a bad one will result in
eternal wailing and gnashing of teeth

You need to define the "entities" or "things" that you want your database to
represent. Each entity will be a table in your database. In your case,
these might include:
Guests (the people who come to stay)
Locations (the different camp grounds)
Units (the different rentable entities - cabins and camp sites)
Facilities (cookers, TV, etc)
Bookings (which guests are booked into which units for which nights)

Now, for each entity, you need to decide what "attributes" you need to
store - for example, Guests would include names and contact details, perhaps
also other details such as passport number or gender. These are the fields
in your table. Each entity should also have a "primary key" which is a
unique way of identifying a record in your table. Often there is no easy
"natural" way to specify uniqueness, without joining together a lot of
different fields, so in Access tables it is common to use an "AutoNumber"
field as the primary key.

Now you need to decide how your entities are related to one another. For
example, each location may comprise several units (a "one-to-many"
relationship) so your Units table will include a field for the primary key
value of the Locations table (LocationID). This field is called a "foreign
key". Relations are nearly always between primary keys and foreign keys.

Your Bookings table will have foreign keys for relations to both Guests and
Units.

The relationship between Units and Facilities is "many-to-many", because one
unit can have several facilities and one facility can be offered by several
units. To model a many-to-many relationship, you need a third table called
a "junction table". This table (let's call it "UnitFacilities") has two
foreign keys, one for a UnitID and one for a FacilityID.

Some of your tables will be "reference tables". These are simple tables
whose contents are fairly static, and their purpose is to list possible
options for the values in a foreign key field. Often they will have only
two fields - a "code" or ID number, and a name or description. Your
Facilities table is a reference table. Other possible reference tables are
UnitType (cabin or camp site) and BookingStatus (tentative, confirmed,
cancelled, taken, paid).

This will probably give you enough to chew on for the moment. Don't
hesitate to come back soon!

 

[Ray C]

Hi Charli
My first reaction is "do you realy want to do this"? I am passably OK with
some of the different methods of programming and have a requirement to
computerise our hire company which, like yourself is currently running on a
paper basis. I thought Great, I will learn how to do it in Access. That was
12 months ago and the task just gets bigger and bigger. Admittedly, I can
spend days or weeks without doing any work on it but I probably would not
start the task again.
The discussion group is a fantastic help but you are still looking to climb
Everest.

Best of Luck
Ray C

 

[Charli]

You guys have been really helpful, and I think I know where to start
now....in answer to your questions, Yes, I really want to do this, and I
don't plan to have it done before the next season, so I have plenty of time.

I am curious though, can I include an interactive map of the campground in
the application?

and can I use the application online as well?

and if I create it with Access2003, does the computer I run it on have to
have that version as well, and can I create this as a stand alone application?

Ok, that is enough questions for now, again thanks for the great starter
ideas!!

 

[Graham Mandeno]

Hi Charli

You guys have been really helpful, and I think I know where to start
now....in answer to your questions, Yes, I really want to do this, and I
don't plan to have it done before the next season, so I have plenty of
time.

That's great! Best of luck!

I am curious though, can I include an interactive map of the campground in
the application?

I'm not sure what you mean by an "interactive map". You can put an image
control on an Access form and use its MouseMove and MouseDown events to find
the X/Y coordinates on the image where the mouse is being moved or clicked.
I suppose you could then write some code to look up the cabin or campsite
that is being pointed to and pop up details about facilities etc.

and can I use the application online as well?

Access *databases* (actually Jet databases) do not really work very well
over a wide-area network. However, your Access *application* could be a
front-end to an ODBC data source which is located on a web server.

and if I create it with Access2003, does the computer I run it on have to
have that version as well, and can I create this as a stand alone
application?

To run an Access application, you must be on a computer with *some* form of
Access installed.
There is a "run-time" version of Access which is available for royalty-free
distribution if you have the "Access 2003 Developer Extensions", which comes
with Visual Studio Tools for Office (VSTO). You can find out more about it
here: http://office.microsoft.com/en-au/assistance/HA011208861033.aspx

Ok, that is enough questions for now, again thanks for the great starter
ideas!!

You're welcome

 

[Charli]

Graham

Your advice has been the absolute best, and I already have a great start on
my tables. I am wondering, and I know it is a lot to ask, but would you
possibly consider allowing me to send what I have so far occasionally and
check it over for me? I really want to do a good job on this and have it
work properly before I install it at work. I was unemployed for quite some
time and these people gave me my dream job just when I was almost out of
hope, so I want to do this for them and I want it to knock their socks off.
:::smile:::

If that is too much to ask, I understand, but I hope I can still come to you
for advice, I find it easier to get answers from one person rather than
several, as different people might give me different answers and confuse me.

Thanks again for all the help so far!!
Charli

 

[harperwork18]

Hi Charli,
You've gotten some great advice. People are so helpful in these Access
forums. My suggestion is that when you have some tables, post to the
Access group for table design and ask what people think of your data
model. That can be very helpful--I've received some great help myself.
I am no relational database expert, so I don't think I would be of use,
but certainly as I grow in my knowledge I will give back what help I've
received.
Good luck to you,
Harper

 

[Graham Mandeno]

Hi Charli

Occasionally I invite people to send me a database to look at, but this is
usually only as a last resort when I've failed to grasp their description of
a problem.

Harper is right - you are much better off posting a synopsis of what you are
doing in a newsgroup (DON'T attach your entire database!!) and that way you
can get diverse comment and opinions from many experts, and also others
benefit from your experiences.

 

[Charli]

Hi Graham, I am looking for some direction here and hoping you can help.
First let me outline a couple of things. The non-profit organization I work
for is a homeowners association, and part of the associations common grounds
is three cabins to rent, a campground which includes three shelters, a
pavilion, boats for rent, a recreation hall and a pool.

Property owners, their families, and their guests are the only ones who can
use these facilities. The application that I want to create should be able
to book reservations for all of the above, keep track of payments,
confirmations, cancellations, and deposits, as well as check in and out
dates. I also need to be able to have it tell me that something is already
booked for a particular time/date if I am trying to book it again for someone
else. I need to be able to type in a guest or property owners name and find
out if that particular guest or property has had problems in the past and
have been banned from the facilities for abuse.

I would also like to be able to have a report to give to maintenance and
security each week, that tells them who is renting what and when. A report
that can tell me what our rental sales have been for each entity (cabins,
campground, shelters, pavilion, etc.)

So far, I have created the following tables and entered some of the data

Guests (those who will be using the facilities)
Property Owners (those who sponsor the guests)
Location (cabins, shelters, campground)
Unit (the different cabins, shelters, and camp sites)
Facility (pavilion, boats, pool, rec hall, that can be rented as well)
Reservation (to enter the reservation information in to)
Reservation Status (to see or update a reservation)

First can you tell me knowing the information as I outlined it above, what
forms would I need to create, and what queries I will need to create and how
I should proceed from there?

I know this is a lot to ask, but you were so helpful in telling me what
tables I should create, it was great the way you outlined it all, the
relationships and everything, if you could do that again with the queries and
forms, I may be able to soon start showing the forum what I have to begin
tweaking.

One last question, what are the uses for macros and modules, and how does
one go about creating them. So far, I will admit to be heavily dependent on
the wizards, but there don't seem to be any for these items.

Thanks again, your help so far has been invaluable to me

Charli