Question Can I perform math on columns in Access?

[GrievousAngel]

hello all,

i bet this has been asked b4 >

how can i perform basic math on Access db columns? i need to multiply a constant by the present column value ($).

the kicker is also only doing partial rows, i.e. some rows X .96, some rows x .99, etc.

if you could hi-lite rows/columns, then do math would be the ticket!

BTW: how on earth could MS build access without this basic function? they want you to buy Excel and import/export!

thanks,
billy

 

[dcb]

Is there criteria that decides what gets multiplied by what?

There are a few ways to get to the same result - not sure the highlight method is the way to go - dont you want to show us an example of your data so that its easier to understand?

 

[Kryst51]

It sounds to me like you want to do calculations at the table level, is this true? If so, you are certainly thinking of Access like an excel spreadsheet. I am sure that what you want to do can be done. I would probably build a form bound to the table, with a listbox that allows multiselect, and a button that is coded to multiply by a selected constant. Actually a combo box with the constant selection, and a list box giving the rows you want to use. How do you want to display the results? In a Report? In another Listbox?

 

[GrievousAngel]

some rows need x constant others by different constant within the same column and table.

i am wanting to do doing this outside of an appz that uses Access in order to save time and reduce errors.

does this answer your question?

thanks,
billy

 

[GrievousAngel]

yes at the table level. no test criteria.

thanks,
billy

 

[Rabbie]

BTW: how on earth could MS build access without this basic function? they want you to buy Excel and import/export!

thanks,
billy

Just because an Access table looks like a spread sheet does not mean it is a spreadsheet.

Access are different tools to do different jobs. Excel is a spreadsheet program and Access is a relational database program. They are not the same

I would advise you look up Update Queries in Access Help as a possible way of resolving your problem.

 

[GrievousAngel]

yep, like an EXcel SS. this seems it would be a common request, right?

is this hard to do? why would MS drop this function?

is someone saying to manually use a query based on that table, manually selected the row/column? i could use a calculator and a mouse while in Access also . . . that would be slow.

so not many real options out there i guess?

thanks,
billy

 

[GrievousAngel]

i understand SS and relational DBs are different for different uses, etc. but . . .
why not have this functionality if needed. surely it would not be hard to include.

is common for all relational DBs on the market? it sure seems this feature would make a much more useful tool. actually i do not see how a professional DB could be used in the real world without math! even word processors can basic math.

anyways . . .

thanks,
billy

 

[Kryst51]

i understand SS and relational DBs are different for different uses, etc. but . . .
why not have this functionality if needed. surely it would not be hard to include.

is common for all relational DBs on the market? it sure seems this feature would make a much more useful tool. actually i do not see how a professional DB could be used in the real world without math! even word processors can basic math.

anyways . . .

thanks,
billy

Accept that it doesn't do that. It doesn't work that way. You can accomplish what you want, yes it takes more work to set up but will save you time later. Please follow Bob's suggestions.

 

[GrievousAngel]

no point - just asking for some help.

how could i do this?

multiply a selected column by a constant within a table.

is this functionality asked about much? i need this to use coming in the back door of an appz that uses Access. the appz allows such if you know how. its not locked.

thanks,
billy

 

[Kryst51]

no point just asking for some help.

how could i do this?

thanks,
billy

No Point? Suggestions have been provided, Sites have been given for you to peruse to have a better understanding of Access and what it does. There is plenty of help available here. But if you don't understand the basics, the solution to your problem will not makes sense. Read up on normalization. I don't know what the site that Bob posted says, but I am sure that it helps explain what purpose a DB serves, it will also clear up why the tables do not act like an excel spreadsheet. Come back here with questions about what you are reading, then hopefully you can get to a place where the solution to your problem is viable. Good luck, the time spent at the beginning to learn is completely worth it.

 

[GrievousAngel]

i don't mean to cause issues with this. sorry for that.

i am a novice user but i do use Access & Excel often. they are both great tools. i was wondering why MS Office (note the word 'Office') would not allow this at an entry level user, i.e. students, small offices, small businesses, etc. not just for 'programmers & script artist'.

this request probably fits in 'it can be done but its a lot of work' kinda thing. i appreciate that. i was never attacking Access - i was just surprised that its not easily done. that's all. maybe in Office 2010 right?

thanks for you help,
later,
billy

 

[dcb]

maybe in Office 2010 right?

NO If they do they will have moved away from the essence of Database

Remember that Excel is Flat
A relational db is ....

Spend some time learning how to do this - It will save you hours in the future

 

[Kryst51]

i don't mean to cause issues with this. sorry for that.

i am a novice user but i do use Access & Excel often. they are both great tools. i was wondering why MS Office (note the word 'Office') would not allow this at an entry level user, i.e. students, small offices, small businesses, etc. not just for 'programmers & script artist'.

this request probably fits in 'it can be done but its a lot of work' kinda thing. i appreciate that. i was never attacking Access - i was just surprised that its not easily done. that's all. maybe in Office 2010 right?

thanks for you help,
later,
billy

If you want the tables to act like a spreadsheet use, a spreadsheet. You clearly don't understand what Access is for. I doubt that you have been using Access correctly. If you are not willing to take the suggested steps, I don't think anyone is going to be willing to help you. (See This thread for tips on what is expected of you, this one is also good) The stuff we are asking you to do is beginner level. It is the very beginning of learning access. If you don't even realize the purpose and are not willing to learn it, you won't be able to use Access effectively ever.

 

[gemma-the-husky]

what access does is applies a set of rules to every item in a dataset - so say the dataset corresponds to your entire table - it doesnt have to be - it could be a selected number of rows - a subset of the table - but the rules will apply to every item in the dataset just the same

so you have a query that takes your table, and transforms a value by multiplying it be a factor, say 0.8

so if you want to treat different rows differently, then there must be some common lilnk between the rows that need ot be treated differently

so say each row has a field called "Mytype"

now you can say

if mytype = 1 then use .95 as a multiplier
if mytype = 2 then use .90 as a multiplier
etc

so the easiest way to deal with this is to have another table with all these mytype values, and multiplier values, and join your tables together - so that the relevant multiplier is available to the data. Now the issues you get are concerned with designing and making your database robust - eg making sure that there is a valid multiplier for evey possible value of the mytype field.


access is far more reliable and easy to use if these BULK queries can be used - now you can arrange to process the rows a line at a time, if its necessary (look at recordsets) but this is more complex, and more prone to errors

 

[adboughton]

No Point? Suggestions have been provided, Sites have been given for you to peruse to have a better understanding of Access and what it does. There is plenty of help available here. But if you don't understand the basics, the solution to your problem will not makes sense. Read up on normalization. I don't know what the site that Bob posted says, but I am sure that it helps explain what purpose a DB serves, it will also clear up why the tables do not act like an excel spreadsheet. Come back here with questions about what you are reading, then hopefully you can get to a place where the solution to your problem is viable. Good luck, the time spent at the beginning to learn is completely worth it.

People ... I have a lot of empathy with Angel on this question of maths in Access, not from ignorance of databases but because database people can be blinded by the science of relational structures and so on to wider needs in the user /programmer community to legitimately combine maths with databases. I began as a mainframe programmer and used large scale business intelligence solutions that do maths from databases, as developed by IBM's Scientific & Technical division, for precisely the reason that spreadsheets and even basic databases are ineffectual tools for maths but databases are often ideally required to do maths. Do not be bigoted about this question. It is demonstrably incorrect to assert that databases should not be maths capable or that spreadsheets are valid tools for mathematical modeling in business or technical applications. It is this very blind spot that spawned the expensive, under-performing Business Intelligence software world. Awful mess. Look up Kx.com and see where maths and databases can lead.

 

[adboughton]

NO If they do they will have moved away from the essence of Database

Remember that Excel is Flat
A relational db is ....

Spend some time learning how to do this - It will save you hours in the future

DCB, this is incorrect. Excel is not an adequate tool for many roles and is much overused, and there are many applications that can and do benefit from computational databases in a far more appropriate way that spreadsheets. It would be more helpful if database developers would lose the inappropriate and dogmatism about what a database "is" or "is not" and and assist rather than harass people who raise this question. Spreadsheets are often an awful tool for structured mathematical analysis, databases are very much more often an ideal framework. What do you think Business Intelligence tools are if not a maths enabled relational databases?

 

[Anakardian]

I think the learning curve is triping everything up.

From my own experience Word has a pretty flat learning curve in the beginning It may become steeper but most users will not go there.
Excel has a start that is a bit steeper but it soon flattens out again unless you keep using new functions.
Access on the other hand starts out vertical and so far I have not really noticed it flattening out although it has become less steep at my current knowledge level.

Being good at one of these programs gains you nothing and in fact being good at excel actually seems to be more of a hindrance than an advantage.

 

[Simon_MT]

The only difference between a spreadsheet is that you can explicitly multiple a cell with a value. With Access you need to create a rule so that in a algebraic structure

IIf([condition],True,False) or IIf(Condition1],True,IIf(Condition2],True,False))

if a=b then c x .96 else c x .99. Needless this is syntaxically correct just in case Bob is reading this.

If you determine a=b then all the rows can be calculated because you rule has been established.

Simon

 

[tabildskov]

Yes you can in Access 2010 or greater. It might be possible in earlier version but that I am not sure of.

The key is the Expression "Builder" and this can be found in the Query Design Tool. Below are a few screen grabs that might be of help to find it and use it.







Good Luck,

Tracy