Please suggest me

[Kutty]

I am planning to make a small software for calculating mess and displaying
for at least 5 members.

Table A
Fields: number,month_name

Table B
Fields: numbe,member_name, advance, spent, days, due_before_minus,
pay_to, amount, expense_per_day

number : in both table should be there and "Table A" number will be primary
key.

advance : in this field there is line total
spent : in this field there is line total

expense_per_day : =advance_line_total/spent_line_total
pay_to : =IF(advance+spent)>due_before_minus,"PAY TO MEMBER","PAY TO
MESS"
Instead of "PAY TO MEMBER" let the system select the name of the member from
the field "name".

In case there is better than this idea please suggest.

In case there is better idea to get advance line total and spent line total
please suggest.

Should you have any question please inform me.

 

[John Vinson]

I am planning to make a small software for calculating mess and displaying
for at least 5 members.

Table A
Fields: number,month_name

Is number the month number, 1 to 12? If so, you don't need this table
at all - Access handles date conversions already, using the Month()
and Format() functions.

Table B
Fields: numbe,member_name, advance, spent, days, due_before_minus,
pay_to, amount, expense_per_day

number : in both table should be there and "Table A" number will be primary
key.

Number is then a non-unique foreign key in TableB? What's the primary
key of TableB?

advance : in this field there is line total
spent : in this field there is line total

expense_per_day : =advance_line_total/spent_line_total
pay_to : =IF(advance+spent)>due_before_minus,"PAY TO MEMBER","PAY TO
MESS"
Instead of "PAY TO MEMBER" let the system select the name of the member from
the field "name".

In case there is better than this idea please suggest.

Storing derived data such as this in your table accomplishes
three things: it wastes disk space; it wastes time (almost
any calculation will be MUCH faster than a disk fetch); and
most importantly, it risks data corruption. If one of the
underlying fields is subsequently edited, you will have data
in your table WHICH IS WRONG, and no automatic way to detect
that fact.

Just redo the calculation whenever you need it, either as a
calculated field in a Query or just as you're now doing it -
in the control source of a Form or a Report textbox.

You should store ONLY the base data - the advance, the amount. ANY
calculated fields should simply be recalculated as needed, and should
not be stored anywhere, in any table, unless there is a very good
reason to do so.

John W. Vinson[MVP]

 

[Kutty]

I put the "number" field for just to connect Table A and Table B.
Other field named "month" will show January, February, March and so on.
Please explain to me about Month( ) and Format ( ). How it works?
What you mean by derived data?
You mentioned about base data "advance" and "amount".
What about "spent" field?
How can I calculate line totals? Can you give me better idea about this? I
mean "advance_line_total" and "spent_line_total" fields.

Would appreciate your great favor.