CODEWITHSUNDEEP
databasems-access
Spaniard89
Spaniard89

Reputation: 2419

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. 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!

Upvotes: 0

Views: 622

Answers (1)

Jeff
Jeff

Reputation: 918

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.

Upvotes: 2

Related Questions