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.

 

[Pat Hartman]

You need to put your validation code in the Form's BeforeUpdate event.

If you write the calculations as we would expect to see them, it will be easier to interpret.

 

[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...

 

[Pat Hartman]

Sorry, I tried to look at this but I rarely use Excel and so I am not familiar with Excel functions and without research I don't know what QUOTIENT and MOD are. I could do this if I had to but I don't have the time. But just by looking at the functions, I can tell that if you transferred this data directly to an Access table without redesigning it, you have repeating groups which violate first normal form. Do some reading on normalization - First, Second, and Third normal forms. You probably won't need to go further.

The difference between Excel and Access (and other relational databases) is that columns are different attributes like name, address, saleDate, HireDate, BirthDate. They are not instances of the same data ie Jan, Feb, Mar, etc.

Repeating groups are stored as a many-side table so that each row is a single instance of an attribute so Jan would be on one row and Feb on another, etc. The Foreign Keys in the many-side table link the records to the parent table so you what entity you are talking about.

You may need to back up and review your database design before you can continue.

 

[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 ... :-(

 

[Pat Hartman]

You're probably going to need someone who is expert in both products to give you definitive instructions. In general, this is not something you will do in a query. It sounds like something you have to do as you add each transaction.

 

[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.

 

[Pat Hartman]

The Access MOD operator (it's not a function) returns the remainder of a division and I don't think that is what Excel is doing if I can believe the example data.

 

[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.