Data Dependency - Automatic Updation

[Azhar2008 via AccessMonster.com]

Hi all,
I am new to access and databases so I mis lot of concepts that should be kept
in mind while working of some project.
I have two tables Department and Expenses.
Department has the following fields:
ID, Name, Budget

Expenses has the following fields:
ID, InvoiceNo, Department, Budget, Expense, Description, etc.

I have designed a form for Expense table which has a ComboBox populated with
names of department from Department. When we select any department, the
corresponding budget (from Department) is filled up into text box in this
form. These form fields are then saved into Expense table.

Now the problem is that, when there are about 1000 entries in Expense table,
for a given department, and we change budget value in Department table, how
can this budget value can be updated into Expense table?

What is the easiest way of doing that?

This is a sample situation. I ll adjust the method into my project.

I 'll be realy very thankful for any response.
Regards

 

[bcap]

What you have identified here is one of the crucial reasons why you should
never duplicate fields in more than one table. This is called
"denormalisation" and is usually poor database design practice.

For each Expenses record, you have a field which identifies the relevant
department. Why, then, do you need to copy the budget field from the
Department table to the Expenses table? When you need to know a
department's budget, you can simply find it in the Departments table, and
your problem goes away.

I am also concerned that, from your description, the Department field in the
Expenses table might contain the department name, whereas the primarky key
on the Department table is, presumably, the ID field. If so, this is
incorrect: on the Expenses table, the foreign key to the Departments table
should be the ID of a department, i.e. the department's primary key.

Your database design will be less confusing if you give these ID fields
clearer names e.g. department_id and expense_id.

 

[Azhar2008 via AccessMonster.com]

Thanks for Valuable guidance. I ll try to normalize my database and shall
bother you again if I face some problem.
Thanks again

What you have identified here is one of the crucial reasons why you should
never duplicate fields in more than one table. This is called
"denormalisation" and is usually poor database design practice.

For each Expenses record, you have a field which identifies the relevant
department. Why, then, do you need to copy the budget field from the
Department table to the Expenses table? When you need to know a
department's budget, you can simply find it in the Departments table, and
your problem goes away.

I am also concerned that, from your description, the Department field in the
Expenses table might contain the department name, whereas the primarky key
on the Department table is, presumably, the ID field. If so, this is
incorrect: on the Expenses table, the foreign key to the Departments table
should be the ID of a department, i.e. the department's primary key.

Your database design will be less confusing if you give these ID fields
clearer names e.g. department_id and expense_id.

Hi all,
I am new to access and databases so I mis lot of concepts that should be

[quoted text clipped - 25 lines]

I 'll be realy very thankful for any response.
Regards