Need Help in MS Access 2013 (1 Viewer)

mwaleedgul

Registered User.
Local time
Today, 08:18
Joined
Oct 20, 2017
Messages
18
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

AWF VIP
Local time
Today, 15:18
Joined
Jul 26, 2013
Messages
10,355
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

Super Moderator
Staff member
Local time
Today, 11:18
Joined
Feb 19, 2002
Messages
42,986
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

Registered User.
Local time
Today, 08:18
Joined
Oct 20, 2017
Messages
18
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

Super Moderator
Staff member
Local time
Today, 11:18
Joined
Feb 19, 2002
Messages
42,986
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

Registered User.
Local time
Today, 08:18
Joined
Oct 20, 2017
Messages
18
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

Super Moderator
Staff member
Local time
Today, 11:18
Joined
Feb 19, 2002
Messages
42,986
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.
 

isladogs

MVP / VIP
Local time
Today, 15:18
Joined
Jan 14, 2017
Messages
18,186
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
 
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 23:18
Joined
May 7, 2009
Messages
19,175
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

Super Moderator
Staff member
Local time
Today, 11:18
Joined
Feb 19, 2002
Messages
42,986
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

Registered User.
Local time
Tomorrow, 02:18
Joined
Jul 4, 2013
Messages
2,770
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.
 

Users who are viewing this thread

Top Bottom