calculation query

[5rgj]

I'm not a complete novice at databases and I could certainly do this in Excel but cannot
seem to get it to work in Access:
I have a query that sums values for products the results of which look like this (though there are actually >100 sum columns):

Pname___Asum____Bsum_____Csum
----------------------------------
p1_______ 1 ______ -5 ______ 25
p2_______ 3 ______ -9 ______ 0
p3_______ 0 ______ -100 ____ 1


I have a table with multipliers that I want to apply to the respective columns:

id ____ ABC _____ multi
------------------------
1 _____ A _______ 1.2
2 _____ B _______-1
3 _____ C _______ 0.25


I want to multiply the values in the columns of the query by the respective multi value in the table and then sum the colums for each row.

The calculation would look like this - the brackets are only to make it easier to read.

p1: (1*1.2)+(-5*-1)+(25*0.25)
p2: (3*1.2)+(-9*-1)+(0*0.25)
p3: (0*1.2)+(-100*-1)+(1*0.25)


The result I'm after is something like this:

Pname ___ Value
----------------
p1 _______ 12.45
p2 _______ 32.40
p3 _______ 100.25


I'd be most grateful for any clues on how to achieve this.
Thanks

 

[James Deckert]

what's not working. list your sql.
You should be able to put a field in your query fieldname: ([asum]*1.2)+([bsum]*-1)+([csum]*0.25)...
Or you could build the query dynamically in VBA using a loop. I don't know of any function that will sum across columns.

if the multiplyby values are in a field you can use the field names.

 

[plog]

though there are actually >100 sum columns

Why? That's not how database are to work. You should have rows of sums, not columns. I fear your tables have been set up improperly.

A, B and C shouldn't be prefixes to column names (Asum, Bsum, Csum), but should be values in your table. If you have that, you can think JOIN your multiplier table to the field that has those values and get the appropriate multiplier value and then use the SUM function to get your total like you want.

 

[5rgj]

what's not working. list your sql.
You should be able to put a field in your query fieldname: ([asum]*1.2)+([bsum]*-1)+([csum]*0.25)...
Or you could build the query dynamically in VBA using a loop. I don't know of any function that will sum across columns.

if the multiplyby values are in a field you can use the field names.

Thanks for the reply James.
I had tried what you suggested but I'd rather not hard code the multipliers as they can potentially change.
So the sql I came up with, which failed with a syntax error, was an attempt to put a select in the calculation:

SELECT SumQuery.Pname,
([Asum]*(SELECT MultiTable.multi FROM MultiTable WHERE (((MultiTable.id)=1)))) AS Expr1
FROM SumQuery, MultiTable
GROUP BY SumQuery.Pname, ([Asum]*(SELECT table.multi FROM table WHERE (((table.id)=1))));

Looking at the sql now it seems to me that the group by clause looks wrong, perhaps the whole thing is being complicated by the use of an aggregate query as the source. I'd prefer to avoid creating a table with the calculated values - however if I'm going to commit that sin I could carry on and add a calculated column to it... though I'm not sure that would help with the multipliers.
Unfortunately I cannot see how the table and the query can be joined as the common feature is a column in the query but a row in the table.
If at all possible I would also prefer to avoid VBA as I haven't a clue how to code it.
Thanks

 

[5rgj]

Why? That's not how database are to work. You should have rows of sums, not columns. I fear your tables have been set up improperly.

A, B and C shouldn't be prefixes to column names (Asum, Bsum, Csum), but should be values in your table. If you have that, you can think JOIN your multiplier table to the field that has those values and get the appropriate multiplier value and then use the SUM function to get your total like you want.

Hi plog, thanks for the feedback.

There are feature which have effects (A, B, C...>100) these effects, which vary according to the feature, have numeric values.

FeaturesTable (static):
Feature _____ A _____ B _____ C
--------------------------------
a ___________ 1 ____ -1 _____ 0
b ___________ 3 _____ 0 _____ 10
c ___________ 1 _____ 1 ____ -2
d ___________ 0 _____ 1 _____ 100
e __________ -2 ____ -1 ____ -50
f ___________ 1 ____ -20 ____ 0


Every pname has features, which can change, potentially >1000 but usually <150

PnameTable (dynamic):
Pname___Feature
---------------
p1 ______ a
p1 ______ b
p1 ______ c
p2 ______ d
p2 ______ e
p2 ______ f
p3 ______ c
p3 ______ f
p3 ______ a


The main purpose of the dB is to calculate the totals for each effect (A, B, C) for each Pname.

SumQuery:
Pname ____ Asum _____ Bsum _____ Csum
-------------------------------------
p1 ________ 5 _________ 0 ________ 8
p2 _______ -1 _______ -20 _______ 50
p3 ________ 3 _______ -20 _______ -2


The new requirement is to aggregate all of the totals into one figure but with each total weighted (multiplied) according to its relevence to the overall value. For example a negative value for effect 'B' is 'good' and therefore should be added rather than subtracted from the overall value.

MutiTable (dynamic):
id ____ Effect_____ multi
------------------------
1 _____ A _________ 1.2
2 _____ B _________-1
3 _____ C _________ 0.25


Using the SumQuery and MultiTable I want be able to do this:

OverallQuery:
Pname ____ Overall
--------------------
p1 ________ 8 ___________ i.e. ___ (5*1.2)+(0*-1)+(8*0.25)
p2 ________ 31.3 _________ i.e. ___ (-1*1.2)+(-20*-1)+(50*0.25)
p3 ________ 23.1 _________ i.e. ___ (3*1.2)+(-20*-1)+(-2*0.25)

It may well be that the dB does not have the best design but, unfortunately, I cannot now change the FeaturesTable or PnameTable.
I'd appreciate any further suggestions

 

[plog]

There are feature which have effects (A, B, C...>100) these effects

This is your issue. What's so special about features that they became values in your table? But effects got to be their own columns? Your data is improperly structured. This is the correct structure for your FeaturesTable:

Feature, Effect, EffectValue
a, A, 1
a, B, -1
a, C, 0
b, A, 3
b, B, 0
etc.

You get your data into that structure and your query becomes trivial. It's a simple JOIN between your MutiTable and your FeaturesTable

 

[5rgj]

This is your issue. What's so special about features that they became values in your table? But effects got to be their own columns? Your data is improperly structured. This is the correct structure for your FeaturesTable:

Feature, Effect, EffectValue
a, A, 1
a, B, -1
a, C, 0
b, A, 3
b, B, 0
etc.

You get your data into that structure and your query becomes trivial. It's a simple JOIN between your MutiTable and your FeaturesTable

Yes I see your point.

The structure of the FeaturesTable is a consequence of the project originally being attempted in Excel and the table being taken from a worksheet more or less unchanged. I guess the number of columns should have been a warning sign at the time but it has not been too much of an issue, until now.

The structure you're suggesting would create a table with >12,000 rows (1200 features each with over 100 effects) but I guess some of the queries would be easier to write.

I appreciate that your advice is to restructure the data, however, the dB is already in use and a complete redesign may be out of the question, so is there another less than ideal solution?

Thanks

 

[plog]

Less than ideal solutions are easy to think of.

1. Hardcode those functions you detailed in your first post into a query (p1: (1*1.2)+(-5*-1)+(25*0.25))

2. Move back to Excel.

3. Move back even further and pen and paper it.

4. Average all your multipliers and just use that average for everything and call it close enough.

5. Build a some sort of Lake House (https://en.wikipedia.org/wiki/The_Lake_House_(film)) time travelling mail box and leave a letter for your past self warning of a bad structure.

Seriously, restructure now, you will only be facing more issues relating to this in the future.

 

[5rgj]

Less than ideal solutions are easy to think of.

1. Hardcode those functions you detailed in your first post into a query (p1: (1*1.2)+(-5*-1)+(25*0.25))

2. Move back to Excel.

3. Move back even further and pen and paper it.

4. Average all your multipliers and just use that average for everything and call it close enough.

5. Build a some sort of Lake House time travelling mail box and leave a letter for your past self warning of a bad structure.

Seriously, restructure now, you will only be facing more issues relating to this in the future.

OK point taken restructuring it is... though I have to say I'm tempted by the Lake House idea. If I could pull it off it could have a number of useful applications!

Thanks again for your help