new here - question about "cell" based formula

[alpha754293]

Hi there. I'm new here. It's been a REALLY long time since I've last used Access (we're talking like back in Access '97 days) and my present job is asking me to do some really crazy advanced stuff with it because we're trying to port a tool that we've been using that was written in Excel over to Access because we're an international, global company and the volume of data is causing pain/heartache for Excel nowadays.



So a little background - the problem comes from an Excel background so I am likely and apt to use a lot of "Excel" terminology, which may not be (and probably isn't) correct or the most appropriate - but since I am unfamiliar with what the Access equivalent would be called, ergo; it is what it is.

So, in the Excel spreadsheet; we have a row, with a bunch of cells that has number. And on one row, we will do math on it (let's say we will multiply those numbers together) A * B * C.

Then on the row right beneath that, we will ADD them instead (so it would be A + B + C).

It is my understanding that you can run queries in Access that will calculate it for the entire column, but how do I do it if each of the resultant record within a column needs to have different formulas (because it's got different stuff happening to it).

Is there a way to do that and if the function isn't "native" or "exposed" to what I can see/use (meaning like, I won't find it either in the ribbon or by left-clicking or right-clicking), is there another way through maybe like SQL commands that will give me much greater flexibility or maybe even through VB/VBA/macros?

I am hoping that this question makes sense and help will be greatly appreciated because it is an important piece of the puzzle.

Thank you.

[June7]

Would help if you provide data example. Why would you multiply 3 fields for one record and then the next record add the 3 fields? If it is the same kind of data in both records, it should be treated the same.

[alpha754293]

Would help if you provide data example. Why would you multiply 3 fields for one record and then the next record add the 3 fields? If it is the same kind of data in both records, it should be treated the same.

I can't comment on the specific reason or the application of that, but it has to do with "we're doing different things with the data (that's conditional based on certain characteristics of the data)".

So, the easiest way that I can describe it might be like this:

Column A, Row 1 = 4
B1 = 9
C1 = 16
and I would want D1 = A1*B1*C1
(so if they're squared numbers, they, in turn, gets multiplied together)

A2 = 2
B2 = 3
C2 = 5
(first three prime numbers > 1)
so if they're a continuing sequence of prime numbers, I want to add them together such that D2 = A2+B2+C2.

And the data in row 2 exists in the same table as the data in row 1.

(It's a bit of an arbitrary/generalized example designed to investigate/look at how I can write different formulas in the fourth column of the two records whereby I have different calculations going on. I realize that if I do it by field, then I could write it in such a way that column E = the additive property, and column F = the multiplicative property, but in what will be my full implementation, it will create a LOT of extra data that I don't/won't ever need; so I am trying to avoid doing it that way and then just running a query, cherry picking the results that I DO want; and instead, trying to put some intelligence into Access so that it can do this kind of "cells-may-have-different-math-formulas" math without having to basically do like a Monte Carlo solution.)

And the third row could be subtractive, i.e. A3=3 , B3=5, C3=7, D3=A3-B3-C3.

Is there a way either through native Access or through the SQL "commands" (I might have all the wrong terminology, so please do forgive my ignorance, and dummy questions) - so that I can make it do different calculations whereby the fourth field is generically called "Result" but the calculation that it produces is different for each record within the "Result" field?

I hope that makes sense.

If it is still confusing, I'll create a same Excel file that I'll upload, and then ask to see if people can help me convert it into an Access database.

(My "real" project is a LOT more difficult, a LOT more complex, and a LOT more data.) I'm just trying to understand the general/governing principle/theory/idea behind how I would implement it and then I'd have to take what you guys teach me, and try to apply it to my actual problem.

Thanks.

[June7]

Calculations can have conditional expressions, example - a calculation that would apply to each row:

[field2] * IIf([field1]="A", 0.10, 0.05)

However, if you want Access to aggregate records only if the value is prime, how would Access 'know' the value is prime? Access does not have native function for calculating prime. Requires custom function http://stackoverflow.com/questions/1...vba-excel-2003

Apply criteria to filter records that are desired in the aggregation
SELECT Field3, Sum(field2) AS SumField2, Avg(field2) AS AvgField2 WHERE Field1="A" GROUP BY Field3;

OR

create fields with expressions that conditionally aggregate value
SELECT Field3, Sum(IIf([field1]="A",[Field2],0)) AS SumField2, Avg(IIf([Field1]="A",[Field2],0)) AS AvgField2 GROUP BY Field3;


Consider building a report that uses Grouping & Sorting functionality with aggregate calcs in group footers. This will allow display of detail records as well as summary calcs. The calcs from both query examples can be done in report.

[alpha754293]

Calculations can have conditional expressions, example - a calculation that would apply to each row:

[field2] * IIf([field1]="A", 0.10, 0.05)

However, if you want Access to aggregate records only if the value is prime, how would Access 'know' the value is prime? Access does not have native function for calculating prime. Requires custom function http://stackoverflow.com/questions/1...vba-excel-2003

Apply criteria to filter records that are desired in the aggregation
SELECT Field3, Sum(field2) AS SumField2, Avg(field2) AS AvgField2 WHERE Field1="A" GROUP BY Field3;

OR

create fields with expressions that conditionally aggregate value
SELECT Field3, Sum(IIf([field1]="A",[Field2],0)) AS SumField2, Avg(IIf([Field1]="A",[Field2],0)) AS AvgField2 GROUP BY Field3;


Consider building a report that uses Grouping & Sorting functionality with aggregate calcs in group footers. This will allow display of detail records as well as summary calcs. The calcs from both query examples can be done in report.

I just picked prime numbers in order to illustrate that it's different. Access doesn't need to know that they're prime. I'm just making up rules in order to generate the numbers for A, B, and C.

It could be anything as far as I'm concern. It can be random, it can be non-random, it can be structured, it can be sequential - the number data doesn't matter.

What matters is how do I write different calculation formulas corresponding to each of the records separately.

I've attached a comma separated text file (which you can import into Excel if you really want to) in order to see the example of what I mean by having different formulas.

I couldn't get it to actually SAVE the Excel formulas into the CSV, so I added the calculation formula that produces the result into the comments column (which does not need to be imported into Access, but is here for your reference only so that you can reconstruct the Results column as an Excel formula and which then I am hoping that it can be ported to Access some way (which is the part where I'm stuck on).

Maybe that would help. I don't know how else I can describe the problem without giving too much away.

Thanks.

[ssanfu]

I made an Excel sheet
Attachment 14174

Then I made a dB that has the same values. I used a UDF to calculate the values.

This is a very simplified method. The number of fields to perform an operation is fixed at 3. There are ways to be able to have one record add 5 fields and in another record multiply 3 records.

You could use VBA to do some or all of the calculations. Open various record sets and perform math operations. I have one subroutine that is 30 printed pages long - lots of calculations..

Open the attached mdb (Form1) and compare the numbers in the form to the picture. Change the numbers in the dB to the numbers you posted in the text file.

[June7]

As demonstrated in examples, need some parameters (rules) to build conditional expressions. Here is the sample data you provided. What rules say record 1 formula is A*B*C, record 2 formula is A-B+C, and record 3 formula is A-B-C?

Data1	Data2	Data3
2	3	5
14	16	18
223	456	-392

If there were another field with a formula code (1, 2, 3), then conditional expression could be:

Switch([code]=1,[Data1]*[Data2]*[Data3], [code]=2,[Data1]-[Data2]+[Data3], [code]=3,[Data1]-[Data2]-[Data3])

or

Choose([code], [Data1]*[Data2]*[Data3], [Data1]-[Data2]+[Data3], [Data1]-[Data2]-[Data3])