Go Back   Access World Forums > Microsoft Access Discussion > Tables

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-20-2017, 01:54 AM   #1
mwaleedgul
Newly Registered User
 
Join Date: Oct 2017
Posts: 18
Thanks: 13
Thanked 0 Times in 0 Posts
mwaleedgul is on a distinguished road
Smile Need Help in MS Access 2013

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

mwaleedgul is offline   Reply With Quote
Old 10-20-2017, 02:19 AM   #2
Minty
AWF VIP
 
Minty's Avatar
 
Join Date: Jul 2013
Location: UK - Wiltshire
Posts: 6,435
Thanks: 165
Thanked 1,738 Times in 1,707 Posts
Minty is a jewel in the rough Minty is a jewel in the rough Minty is a jewel in the rough
Re: Need Help in MS Access 2013

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.
__________________
If we have helped please add to our reputation - click the scales symbol on the left, tick 'I approve' and leave a comment.

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
Minty is offline   Reply With Quote
The Following User Says Thank You to Minty For This Useful Post:
mwaleedgul (10-21-2017)
Old 10-20-2017, 12:24 PM   #3
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Need Help in MS Access 2013

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
The Following User Says Thank You to Pat Hartman For This Useful Post:
mwaleedgul (10-21-2017)
Old 10-21-2017, 12:23 AM   #4
mwaleedgul
Newly Registered User
 
Join Date: Oct 2017
Posts: 18
Thanks: 13
Thanked 0 Times in 0 Posts
mwaleedgul is on a distinguished road
Re: Need Help in MS Access 2013

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 is offline   Reply With Quote
Old 10-23-2017, 09:30 AM   #5
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Need Help in MS Access 2013

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-23-2017, 11:23 PM   #6
mwaleedgul
Newly Registered User
 
Join Date: Oct 2017
Posts: 18
Thanks: 13
Thanked 0 Times in 0 Posts
mwaleedgul is on a distinguished road
Re: Need Help in MS Access 2013

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 offline   Reply With Quote
Old 10-24-2017, 12:04 PM   #7
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Need Help in MS Access 2013

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.

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 10-25-2017, 04:26 AM   #8
mwaleedgul
Newly Registered User
 
Join Date: Oct 2017
Posts: 18
Thanks: 13
Thanked 0 Times in 0 Posts
mwaleedgul is on a distinguished road
Re: Need Help in MS Access 2013

Is it possible to add module and call it in a query?
mwaleedgul is offline   Reply With Quote
Old 10-25-2017, 04:35 AM   #9
isladogs
High Noon Moderator
 
isladogs's Avatar
 
Join Date: Jan 2017
Location: Somerset, UK
Posts: 11,052
Thanks: 115
Thanked 3,020 Times in 2,747 Posts
isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold isladogs is a splendid one to behold
Re: Need Help in MS Access 2013

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
__________________
If this answer has helped, please click the Thanks button and/or click the 'reputation scales' symbol on the left.

Colin (Mendip Data Systems)
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


Website links:
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
,
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.


I know that you know that we both know nothing

Last edited by isladogs; 10-25-2017 at 09:43 AM.
isladogs is offline   Reply With Quote
Old 10-25-2017, 04:38 AM   #10
arnelgp
error reading drive A:
 
arnelgp's Avatar
 
Join Date: May 2009
Location: somewhere out there
Posts: 8,569
Thanks: 68
Thanked 2,745 Times in 2,630 Posts
arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice arnelgp is just really nice
Re: Need Help in MS Access 2013

Are there anymore fields aside from field1 to 3. Why are you adding the rows do they have common field that you should add.
__________________
"Never stop learning, because life never stops teaching"
arnelgp is offline   Reply With Quote
Old 10-25-2017, 08:44 AM   #11
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,271
Thanks: 15
Thanked 1,595 Times in 1,515 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Need Help in MS Access 2013

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.
__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Old 11-01-2017, 10:40 PM   #12
Cronk
Newly Registered User
 
Join Date: Jul 2013
Posts: 2,152
Thanks: 3
Thanked 470 Times in 463 Posts
Cronk will become famous soon enough Cronk will become famous soon enough
Re: Need Help in MS Access 2013

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.

Cronk is offline   Reply With Quote
Reply

Tags
database , field , iif , tbl

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Question MS Access 2013 Data Link in Excel 2013 BrandonM30 General 2 11-21-2016 10:20 AM
Send Tasks from Access 2013 to Outlook 2013 Leerobo Forms 3 04-26-2016 06:49 AM
Custom Menu Bar Won't Display In Access 2013, Access 2013 JGravesNBS Modules & VBA 1 04-05-2014 05:33 PM
Question Package 2013 Access db with 2013 runtime, standalone application? Nina09 General 0 01-02-2014 01:09 AM
[SOLVED] Access 2013 update: February 12, 2013 Rx_ General 0 03-07-2013 12:47 PM




All times are GMT -8. The time now is 08:25 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World