Need Help in MS Access 2013

[mwaleedgul]

Hi Everyone.

Can anyone please help me?

I have MS Access Database 2013 (ver)

Tbl Fields

Field_1
Field_2
Field_3
I want to calculate these fields like

Field_3/9 then the answer Add/plus in Field_2 (Field_3 should be less then 9 always)
Field_2/20 then the answer add/plus in Field_1 (Field_2 should be less then 20 always)

Please help me..

Regards,

MW

 

[Minty]

Your question doesn't make much sense. Please post up some sample data, e.g. sample values for field 1, and what you expect as results.

In a database you would not store fields 2 and 3 these would always be calculated in a query. What you are currently thinking of is a spreadsheet, not database.

 

[mwaleedgul]

Thanks for your reply sir....
Here is my Question...
1st Row: Value_1 = 30, Value_2 = 12, Value_3 = 6
2nd Row: Value_1 = 15, Value_2 = 11, Value_3 = 3

According to simple calculation the Answer is
Value_1 = 45, Value_2 = 23, Value_3 = 9

But the Ans should be like this.... (As I want to calculate these values)
Value_1 = 46, Value_2 = 4, Value_3 = 0

Calculations Based on formula of land measuring in our area.
If value_3 is >or=9 then (1) add up in Value_2 and the Value_3 should be <9
and If Value_2>or=20 then (1) add up in Value_1 and the Value_2 should be <20

I am using MS Excel for this purpose but I need these conversion in MS Access

Here is my Excel Formula..
A B C
1 30 12 6
2 15 11 3
___________
3 46 4 0

Cell No A3 Formula: =SUM(A1:A2)+QUOTIENT((SUM(B1:B2)+QUOTIENT((SUM(C1:C2)),9)),20)
Cell No B3 Formula: =MOD((SUM(B1:B2)+QUOTIENT((SUM(C1:C2)),9)),20)
Cell No C3 Formula: =MOD((SUM(C1:C2)),9)

I hope you can understand what I want to say...

 

[mwaleedgul]

QUOTIENT and MOD are excel functions are used to add the leftover values in the next selected cells i.e. cell B3 when exceed from the 20 it will add up to A3... I don't know how could I explain ... :-(

 

[mwaleedgul]

Is it possible to add module and call it in a query?

 

[isladogs]

You can't call a module - its a collection of procedures.
You can call a procedure using VBA.

BTW the Mod function exists in Access

 

[arnelgp]

Are there anymore fields aside from field1 to 3. Why are you adding the rows do they have common field that you should add.

 

[Cronk]

The Excel function QUOTIENT is basically integer division.

You could try having the first two lines stored as two records in a table, have a continuous form showing the two records and add text boxes with the respective calculations to show the result.

I've seen lots of instances of people trying to make Excel work like an Access database. This seems like the opposite ie making Access work like a spreadsheet.