设计数据库时遇到的问题
我的任务是在 MS ACCESS 中设计一个应用程序数据库。在我的数据库中有 5 个表：
- 主
- 现金
- 支票
- 详细信息
- 月份（显示一年中的月份）
这里我将主表作为父记录，其他 3 个现金、支票和详细信息是主表的子项。
以下是主表中的字段
Lt no Name Regfee AssessmentYear April May June .......... March
最后 12 个字段是财务中的月份，以金额为值。
这些值应通过查询从支票/现金表中填充。
cheque LTno **month** chqueno date bank **amount** are fields.
在此检查表中，特定月份的金额将填充到相应月份的主表中。我要做什么查询。
期待您的宝贵建议。
I am in a mission to devise an application database in MS ACCESS. In my database there are 5 tables:
- Master
- Cash
- Cheque
- Detail
- Month (displays month in a year)
Here I have made Master as parent record and 3 others Cash, Cheque and Detail are children to Master table.
Here are the fields in master table
Lt no Name Regfee AssessmentYear April May June .......... March
The last 12 fields are months in a financial which takes amount as value.
These values should be populated from cheque/cash table through a query.
cheque LTno **month** chqueno date bank **amount** are fields.
In this cheque table amount for a particular month is to be populated on master table for the corresponding month. What query do I make.
Expecting your valuable suggestions.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
由于数据库没有标准化，您将不得不进行非常复杂的查询来更新表。您必须进行十二次看起来几乎相同的更新，或者进行一个巨大的查询，执行几乎相同的操作十二次。
最好将月份值从主表中移出并移入单独的表中，其中月份是表中的字段而不是字段名称。然后向其中添加记录就很容易了：
As the database is not normalised, you will have to make a very complicated query to update the table. You have to either make twelve updates that look almost the same, or a huge query that does almost the same thing twelve times.
It would be better to move the month values out of the master table and into a separate table where the month is a field in the table instead of a field name. Then it would be easy to add the records to it:
我也做了类似的事情，只不过我包括了信用卡单据、现金购买以及支票。这是基本的簿记。
由于多种原因，我选择有一个名为“almanac”的表，其中每个日期都有一行（记录）。有一些列（字段）用于表示星期几、一年中的月份等。我用 VB 编写的一些代码片段填充了该表。即使有 10 年的日期，表中也只有大约 3,653 行。然后，我对该表使用简单的联接，将事务数据减少为按月汇总的数据。我也可以通过其他方式进行总结。
查询很简单，我直接用图形查询界面就完成了。但是，我的摘要的行数是您的摘要的十二倍，每行只有一个月。
当我想要以主表的格式布置数据时，我会使用以下两种工具之一：MS Access 中的交叉表查询工具或 MS Excel 中的数据透视表工具。它们都非常强大，但枢轴工具更灵活。我必须在 Excel 中安装一个名为 MS query 的插件才能从 Excel 查询数据库数据。这可能是我正在使用的版本的一个功能。
这与您的框架有很大不同，是否使用它是您的选择。这对我来说效果很好。
I do something similar, except that I include credit card slips and cash purchases as well as checks. It's basic bookkeeping.
For a variety of reasons, I chose to have a table called "almanac" with one row (record) for every date. There are columns (fields) for such things as day of the week, month of the year, and so on. I populate this table with a little code fragment written in VB. Even with ten years worth of dates, that's only about 3,653 rows in the table. I then use simple joins with this table to reduce transaction data to data that's summarized by month. I can summarize in other ways, too.
The query is so easy that I just did it with the graphical query interface. However, my summary would have twelve times as many rows as yours, with only one month in each row.
When I want data laid out in the format of your master table, I use one of two tools: the crosstab query tool in MS Access, or the Pivot Table tool in MS Excel. They are both very powerful, but the pivot tool is more flexible. I had to install an add in called MS query in Excel in order to query database data from Excel. That may be a function of the version I'm using.
This is very different from your framework, and it's your choice whether to use it. It's worked well for me.