Starter question

[martijn1986]

Hi everyone,

To start off I'm completely new to access but after making a lot of lists via excel with all kinds of formulas I wanted to try to build something in Access.
Specifically I'm looking to create a database to keep track of theme park visits.

What am I looking for exactly and what do I want to keep track of?:

- date of the visit
- Rides done per visit
- Shows done per visit

Create report on the following metrics:
- visits per month
- visits per year
- total rides done YTD
- total shows visited YTD
- rides per month
- Shows per month
- rides per year
- shows per year
- avg rides per visit
- avg shows per visit

I have been playing around a bit but haven't been able to find the correct setup yet and now I'm kinda stuck on where to begin and how to set this up.

I would love to have to following tables (not sure if this is the wisest setup to start with though):
- theme parks
- rides
- shows
- visits

Anyone got some tips or a suggested setup I can use?

Sorry for being all noob on this I haven't been a rock at databases and SQL in the past but trying to learn and get better understanding on how access works.

Thanks in advance

[madpiet]

No, don't feel silly for asking questions! It's a whole lot better than spending a zillion hours building something that doesn't work. If you don't understand how relationships etc work, then databases can be hard.

Okay, to answer your question. If I'm reading your question correctly (and I may not be, so correct me if I'm wrong), you have several "things" you're tracking:

Theme Parks,
Visits To theme Parks, .... Each Theme Park can be visited (by you) more than once, so ThemePark--(1,M)--Visit
Shows at the Theme Parks, Each Theme Park can have many shows (and each show is only at that theme park on that date), ThemePark--(1,M)--Show
Rides at the Theme Parks. ThemePark--(1,M)--Rides (well, that one's debatable... depends if a the same ride can exist as multiple parks, so maybe ThemePark--(M,M)--Ride (one theme park can have many rides, and each Ride MAY exist at multiple theme parks).

It's crucial to get this part sorted out (I do it on a white board or on paper... after doing it for like 20 years, I can do a lot of it in my head, but I like to draw the relationships on a white board or similar. It just helps me figure out gotchas before I start building something that won't work.) I've fixed lots of databases that don't work - you don't wanna do that.

Okay, after you get that part sorted, and you think it's right (almost wrote "write" LOL), build just the tables and add relationships between them then add like 2-3 records per table. Then query them to see if you get what you expect to.

but definitely do the diagrams etc first. If you don't you could be doing a lot of painful rework later.

[Micron]

Do you understand normalization? If not, you must as it is the most important factor among many important factors. Perhaps this will help.
http://www.accessforums.net/showthre...773#post521773

[mike60smart]

Hi
Is it just you that is making the Visits to Theme Parks?

[madpiet]

The only difference (structurally) between "only you" and more than one person/group is that you'd need another table for People

Person--(1,M)--Visit--(1,M)--GoesOnRide

If only one person is making the visits, the "Person" table (if it were there) would have only one record.

[martijn1986]

For now it will be only me visiting the theme parks and it will mainly be 1 park for now. Most likely within a couple of years more parks will be added when I saved up enough to go on a theme park trip for starters throughout Europe.

Normalization heard of it, but that was around 15 years ago at school


So, if I sum up correctly the advice would be to start making the following first:

- Create an overview of what relations there are.

Relations are like:
- 1 person can visit 1 or more parks
- 1 visit per date
- 1 visitor can be visiting a park more than once but on different dates
- 1 park can have one or more rides
- 1 park can have one or more shows
- Per visit I can do 1 or more rides
- Per visit I can do 1 or more shows

These are all I can think off, or did I forget anything?

[CJ_London]

Do shows travel? I.e. same show different parks?

[xps35]

A picture is worth a thousand words, so I put together a design.

Note that I did not create separate tables for rides and shows. There is only an attractions table and for an attraction you can specify whether it is a ride or a show. This makes the design simpler and especially more flexible. If a park comes up with a different type of attraction, you do not have to create extra tables. All you do is add an attraction type to the relevant table.

[martijn1986]

Hi Peter,

Thanks for the feedback and proposed relationship setup.
Sorry for my late respsonse but my work got the better of me for the past month so very little time to work on this small private project.

I've created a test DB in Access and setup the relationships as propsed (see TB_DB_1)
Also filled in some values in the tblPark to start with (see TP_DB_2)

SInce this all very new and I want to make sure to follow the best practices and prevent me from running into things later on or overcomplicate stuff:

If I want to add an attrraction that has multiple options. Lets say a coaster has 2 different trains or a ride has different seatings that have different outcomes of the ride. How do you suggest I hdnale these?
Do i add them as seperate values (as seen in TP_DB_4) in the Attraction Table or should I create another table and link that as well?

See attached files for a quick overview of the inital setup as made now.

[mike60smart]

You need a related table to tblAttraction where you store 1 or more options for Each Attraction

[xps35]

To do this neatly, you would need to add an extra table. You link an options table to the attraction table.

Note that the relationships between the tables look different in my picture than in yours. That's because you don't enforce relational integrity in relationships. You should. This ensures that your database remains consistent. For example, you cannot add an attraction with a ParkID that does not exist in the tblPark table if you do. In principle, this is possible in your database.

[martijn1986]

Hi both,

Thanks again for the tips. The visualization really helps to be honest.
I have now made the changes and took the liberty to start filling the base tables with the data I think should be placed there. See images below. Hope I understood the field links well enough and didn't make any mistakes there.

If this is all okay I guess next up I can start with the forms to fill data?
By reading up a bit asnd looking at my expectations, I think I would need to create the following forms:

frmAddPark - to add a new park to tblPark
frmAttracxtionType - to add an attraction type to tblAttractionType?
frmAddAttraction - to add a new attraction to tblAttraction (maybe this one also needs to link to tblRideOption to be able to add an option if necesssary? Or should I create a separate form for this?)

Is it wise to all link these to one form or is that only needed wneh you need to add all of the above?
Never used forms in access before so not sure what the options and possible limitations are.


And I guess I would also need a form to register the actual visit of a park and the attractions on that day.
Possibly frmVisits which links to both tblVisitPark and tblVisitAttraction?


How to do this? How to approach these things and how to make a form that combines 2 or more tables?

[xps35]

A form for AttractionType can be a simple continuous form.

For the parks you could have both a continuous form (jus a list of parks) and a detail form (data of 1 park).
On the detail form, make a subform to maintain the attractions of a park.

The ride options is a bit more tricky. You can have a ride options sub-subform on the attraction subform of the park detail form, but I think that is hard to read. You are therefore also likely to make mistakes when entering the data.
An other option is to create a button on each line of the attraction subform that will open an attraction detail form on which you have a ride options sub-form.

For visits you can add a second subform to the park detail form.
But you will also need a detail visit form with a subform for the visited attraction-ride-options.
That is the most tricky part I think. You have to make sure you can only add visited attraction-ride-options of the park visited.
A second tricky part is that you only store the RideOptionID. It is easier to select a ride option if you can do so in stages. So you first choose an attraction (only applicable attractions) and then a corresponding ride option. That is quite possible, but I would have to think carefully about the exact design.

[martijn1986]

Hi Peter,

I think for the rides I maybe could do without the rideoptions table.
Maybe below images help on explaining what my end goal would be. I now keep track of all my stats in an Excel file. It's not complete yet but it gives a nice idea on the data I would like to keep track of perhaps.
These are the main things I want to keep track of.

[Micron]

When lookup tables are used to provide combo lists with options, one method is to use the NotInList method, which avoids having to use a form. It is a bit trickier to manage though. Having a form would be easier to add list options. Once I had about 4 list tables to manage so I made a form for each and put them all on one form as subforms so that they would all open at once.

You said this was a private db. If you're the only user and adding list options is going to be a rare thing, simply adding records to tables is an option I would entertain. This would apply only to park and attraction type tables.