Multiplying grouped data

  • Thread starter Thread starter sajonara
  • Start date Start date
S

sajonara

Hi,
I have a query with two grouped columns and in third one i'd like to get the
product of grouped data(numbers). Is there a possibility of multiplying
grouped data ?(this is not an option among Sum, Min, Max and others). If not,
is there some other way to do it?
Thanks
 
Do not use the alais names but the orignal math like this --
SELECT Sum(Aaron.Qty) AS SumOfQty, Sum(Aaron.Sales) AS SumOfSales,
Sum([QTY])*Sum([Sales]) AS Sum_Multiplied, Sum([QTY]*[Sales]) AS
Multiplied_Summed
FROM Aaron;
 
it still doesn't solve my problem, because i'd like to multiply data within 1
column - first column is a grouping column and in second one i'd like to
multiply grouped data from that column

"Arvin Meyer [MVP]" je napisal:
In the third column use the Expression:

Product: [Column1]*[Colums2]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

sajonara said:
Hi,
I have a query with two grouped columns and in third one i'd like to get
the
product of grouped data(numbers). Is there a possibility of multiplying
grouped data ?(this is not an option among Sum, Min, Max and others). If
not,
is there some other way to do it?
Thanks
Click to expand...


.
Click to expand...
 
What do you mean by "a grouping column"? Can you show a sample of what your
data looks like, and the result you're hoping to achieve?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sajonara said:
it still doesn't solve my problem, because i'd like to multiply data
within 1
column - first column is a grouping column and in second one i'd like to
multiply grouped data from that column

"Arvin Meyer [MVP]" je napisal:
In the third column use the Expression:

Product: [Column1]*[Colums2]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

sajonara said:
Hi,
I have a query with two grouped columns and in third one i'd like to
get
the
product of grouped data(numbers). Is there a possibility of multiplying
grouped data ?(this is not an option among Sum, Min, Max and others).
If
not,
is there some other way to do it?
Thanks
Click to expand...


.
Click to expand...
Click to expand...
 
sql is : SELECT Sheet1.[Vrsta VP], Sum(Sheet1.Kolicina) AS SumOfKolicina
FROM Sheet1
GROUP BY Sheet1.[Vrsta VP];


What I'd need is to get the Product of 'Kolicina' instead of Sum. So that
for each of 'Vrsta VP' values it would return group product of 'Kolicina'

"Douglas J. Steele" je napisal:
What do you mean by "a grouping column"? Can you show a sample of what your
data looks like, and the result you're hoping to achieve?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



sajonara said:
it still doesn't solve my problem, because i'd like to multiply data
within 1
column - first column is a grouping column and in second one i'd like to
multiply grouped data from that column

"Arvin Meyer [MVP]" je napisal:
In the third column use the Expression:

Product: [Column1]*[Colums2]
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

Hi,
I have a query with two grouped columns and in third one i'd like to
get
the
product of grouped data(numbers). Is there a possibility of multiplying
grouped data ?(this is not an option among Sum, Min, Max and others).
If
not,
is there some other way to do it?
Thanks


.
Click to expand...
Click to expand...

.
Click to expand...
 
Hi Ken,

unfortunately this function writing goes beyond my momental knowledge on
this subject. What you described is exactly what I need, but now I'm
wondering where to paste the function that you wrote. I would be more than
thankfull if you can help me with this one.
Regarding data types, 'Vrsta Vp' is Text and 'Kolicina' is Number

Regards,
Amer

"KenSheridan via AccessMonster.com" je napisal:
 
Ok, I tried the following:

I saved your function under Modules and ran the Query but it returned :
Compile error - user-defined type not defined

Don't know if thats the right way to do it (save the function under
Modules). If yes, what about the error reported?


"sajonara" je napisal:
 
It works! Man, you saved me a lot of trouble, and kinda opened my eyes into
Sql world that seems like an interesting thing to take a deeper insight.
I'll try to use this function on other queries, hopefully with success...in
case that you have a lack of Access chalenges, you can find my questions here
probably ;)

If someday you decide to visit Slovenia, you have a beer or three on my
account ;)

Thanks!
 
Ok, I bumped into a problem. Now I'd like to choose another grouping column
instead of 'Vrsta VP', which is in another table ('Sheet2') and is called
'Instrument' (also Text type). I tried to modify the function but without
success (query now has 2 tables and I can't put it in sql function).

Help?

thanks

"sajonara" je napisal:
 
So far, data were all from one table. Now I'd like to have the product of
same column as before, only grouped by a different column which is from
another table(Sheet2). The relation between two tables is one-to one
('Instrument'(Sheet1) to 'Instrument2'(Sheet2))

For example: 1st case was 1 table (Sheet1)- grouping column 'Vrsta VP' and
'Kolicina' as product.
2nd case is 2 tables: Sheet1 and Sheet2, related with Instrument (all rows
from Sheet1 and only those from Sheet2 that are equal to Sheet1). Now
grouping column is from Sheet2 ('Issuer') and product of 'Kolicina' is from
Sheet1.
So query should return product of 'Kolicina' from Sheet1 grouped by 'Issuer'
from Sheet2.

Can it be done within one function or is it necessary to create two?



"KenSheridan via AccessMonster.com" je napisal:
The way the ProductOfKolicina function is written it handles only the
specific columns from the Sheet1 table, so you'll need to create a separate
function of a different name to handle the column in the other table by
changing the table and column names in the function as necessary.

You should then be able to call both functions as separate computed columns
in the query which includes the two tables. You don't sat how these tables
are related, however. If the relationship is one-to-many you'll get the
values from each row of the table on the 'one' side (the referenced table)
for each instance of a matching row in the table on the 'many' side (the
referencing table). This should not affect the product values returned in
the computed columns, however, as the functions will act independently for
each row returned. You'll get repeated values of the product from the
referenced table, but the value will be the correct one in each case.

If the tables are not related a UNION ALL query might be a more appropriate
solution. This in effect tacks the rows returned by one query onto those
returned by another, so if one query returns 20 rows and the other 30, the
UNION ALL of them would return 50 rows. Whether this is a suitable approach
depends on just what you want from the query when you include both tables.

The first thing to do is to create the second function for handling the data
from Sheet2 and then test it in a simple query which uses Sheet2 only. If
that works then you can either JOIN the tables in a query or create a UNION
ALL query which combines the results from both tables. Before I can advise
you on which of these ways to go, however, I'll need a fuller explanation of
just what you want the query to return. As usual a few dummy rows from each
table and how you'd want these to be reflected in the query's result table
would be the best way of showing us what's required.

Ken Sheridan
Stafford, England
Ok, I bumped into a problem. Now I'd like to choose another grouping column
instead of 'Vrsta VP', which is in another table ('Sheet2') and is called
'Instrument' (also Text type). I tried to modify the function but without
success (query now has 2 tables and I can't put it in sql function).

Help?

thanks

"sajonara" je napisal:
It works! Man, you saved me a lot of trouble, and kinda opened my eyes into
Sql world that seems like an interesting thing to take a deeper insight.
Click to expand...
[quoted text clipped - 31 lines]
is there some other way to do it?
Thanks
Click to expand...
Click to expand...

--
Message posted via AccessMonster.com


.
Click to expand...
 
Back
Top