Accessing data from a ticket database, based on months in MS Access

Dear stackoverflow members,

I am creating an access database for ticket selling prices. Since the tickets prices changes in yearly, I want to create the database to access ticket prices base on Months.

But the problem is how can i query the database to retrieve ticket prices based on a particular month? I have also attached a image of my sample database. I have also uploaded the database in text format.

Company:

  -------------------------------------------
    |     CompanyID      |    CompanyName     |
    -------------------------------------------
    | A                  | AAA                |
    -------------------------------------------
    | B                  | BBB                |
    -------------------------------------------
    | C                  | CCC                |
    -------------------------------------------
    | D                  | DDD                |
    -------------------------------------------
    | .                  | ..                 |
    -------------------------------------------
    | .                  | ..                 |
    -------------------------------------------
    | .                  | ..                 |
    -------------------------------------------
    | Z                  | ZZZ                |
    -------------------------------------------

Ticket:

    ----------------------------------------------------------------------------------------------------------
|         ID         |     TicketType     |        2010        |        2011        |        2012        |
----------------------------------------------------------------------------------------------------------
| 001                | 3 months           |             $50.00 |             $55.00 |             $68.00 |
----------------------------------------------------------------------------------------------------------
| 002                | 2 Weeks            |             $10.00 |             $11.50 |             $13.10 |
----------------------------------------------------------------------------------------------------------
| 003                | Group ticket       |             $30.00 |             $32.00 |             $35.00 |
----------------------------------------------------------------------------------------------------------
| 004                | Night ticket       |              $7.00 |              $9.00 |             $11.00 |
----------------------------------------------------------------------------------------------------------
| 005                | 1 Day              |              $3.00 |              $5.00 |              $8.00 |
----------------------------------------------------------------------------------------------------------
| 006                | 1 Week             |              $8.00 |             $15.00 |             $12.00 |
----------------------------------------------------------------------------------------------------------
| 007                | 1 month            |             $19.00 |             $22.00 |             $25.00 |
----------------------------------------------------------------------------------------------------------
| 200                | Fun ticket         |             $11.00 |             $12.00 |             $14.00 |
----------------------------------------------------------------------------------------------------------
| AAA                | 001                |                    |            $100.00 |          $5,500.00 |
----------------------------------------------------------------------------------------------------------

The Problem is: I want to access the ticket prices base on months. For example, The price of TicketID: "001" in "March 2011". With it, monthly account base on tickets sold on a particular months will be accounted.

The fields are listed in year and creating it in months would lead to massive data redundancy or troublesome for future updates.

Any help would be very much appreciated. Thank you very much in advance.

Cheers!

Answers


The structure of your database is preventing you from doing so. The ticket table isn't even in First Normal Form. Currently, every new year you would need to add a new column to show the updated pricing. This is much more easily accomplished by adding rows, not columns.

You need to create another table called TicketID, which has ID and TicketType. Modify your Ticket table to have the following: ID, price, date_effective. This will allow you to record the changing pricing like this

    1,50,1/1/2010
    1,51,2/1/2010
    1,52,8/15/2010
    1,55,1/1/2011

This will allow you to change the price whenever you need without having to add an extra column. Because you are storing a date in the last column, you can use the built in Date functions within Access to create your query.


Need Your Help

Where in windows registry the console width is stored?

console windows-xp registry width

My default console width is 80, but when I look into HKCU\Console there isn't a name that has this value. Only one that has supposedly to do with with is: WindowSize but it has value of 0x190050, t...

Is it safe to use os.environ.setdefault?

python dictionary environment-variables python-os

From my ipython shell, I see a method setdefault in os.environ but it is not documented. http://docs.python.org/library/os.html#os.environ. Is it documented somewhere else?