根据MS Access中的月份从票证数据库访问数据 [英] Accessing data from a ticket database, based on months in MS Access
问题描述
尊敬的stackoverflow成员，
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.
公司：
-------------------------------------------
| CompanyID | CompanyName |
-------------------------------------------
| A | AAA |
-------------------------------------------
| B | BBB |
-------------------------------------------
| C | CCC |
-------------------------------------------
| D | DDD |
-------------------------------------------
| . | .. |
-------------------------------------------
| . | .. |
-------------------------------------------
| . | .. |
-------------------------------------------
| Z | ZZZ |
-------------------------------------------
门票：
----------------------------------------------------------------------------------------------------------
| 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 |
----------------------------------------------------------------------------------------------------------
问题是：我想查询机票价格基于几个月。
例如， 2011年3月的TicketID： 001的价格。
有了它，就可以计算出在特定月份售出的门票的月度帐户。
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.
干杯！
推荐答案
您的数据库结构阻止您这样做。票证表甚至没有第一范式。当前，每年新的一年，您需要添加一个新列以显示更新的价格。
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.
您需要创建另一个名为TicketID的表，该表具有ID和TicketType。修改票证表以使其具有以下内容：ID，价格，有效日期。这样您就可以记录这样变化的价格
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
这将使您可以在需要时更改价格，而不必添加额外的列。由于您将日期存储在最后一列中，因此可以使用Access中的内置Date函数来创建查询。
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.
这篇关于根据MS Access中的月份从票证数据库访问数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
