dbase design q...

[burnside]

dbase design q

i have a list of expenditure items.
i want to be able to put in different month targets and expenditure for each item.
at the moment i have a table that has each item listed with a column for each months target and each months spending.
there are 24 columns for each item.
is this best design?

 

[ZmrAbdulla]

This is EXCEL sheet design. What you will do if you reach to the 25th month?
Do something like this

A Master table with expenditure items.

ExpID(PK)(AutoNumber or defined)
ExpName (Text)
More...

A Subtable for month/target/exp
RecID (PK) (AutoNumber)
SubExpID(FK) (Linked to Master table ExpID)
MonthID (Date)
ExpTarget (Currency)
ExpSpent (Currency)

If you want to enter each days expenditure then you have to modify/split this table.
hope this helps

Zameer Abdulla
_{Jack of Visual Basic Programming, Master in Dining & Sleeping}
Visit Me

 

[lespaul]

read up on database normalization

Fundamentals of Relational Database Design

Leslie

 

[willir]

Tracking expenditures is very easy -- you just need a transaction table. The tough part is reporting.

tblTransaction
TransactionID - primary key
TransDate - date field
TransAmount - currency

You then need to add to it, depending on how you want to report / categorize the transactions.

For example, G/L account would change the design...

tblGLAccount
GLID - primary key
GLAccount

tblTransaction
TransactionID - primary key
TransDate - date field
TransAmount - currency
GLID - primary key to tblGLAccount

So, how do you want to track expenditures?

As referenced by Leslie's referenced document and Zameer, you do not need use a different field for each month. By using the date field, you report for each period...

SELECT ...
ORDER BY Format(TransDate, "yyyy-mm")

Richard

 

[burnside]

thanks very much for all your answers