Problem while Designing a Database

Question

Hello,

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.

Answer 1

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:

insert into MasterMonths (LTno, month, value)
select LTno, month, sum(amount)
from cheque
group by LTno, month

Answer 2

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.