By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
431,852 Members | 2,152 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 431,852 IT Pros & Developers. It's quick & easy.

Basic query help needed...

P: n/a
I'm a bit confused on how to use the query builder to create an
expression that will calculate gross revenue per gross unit using the
tables below:

table_1:
Product
PLtype (e.g., "Gross Units" or "Gross Rev")
PLamount

query results:
(Product), (gross rev/gross unit)

I know a pivot table might better handle this, but I'd like to know
how to do this without a pivot. Any suggestions are appreciated...
Nov 13 '05 #1
Share this Question
Share on Google+
2 Replies


P: n/a
John wrote:
I'm a bit confused on how to use the query builder to create an
expression that will calculate gross revenue per gross unit using the
tables below:

table_1:
Product
PLtype (e.g., "Gross Units" or "Gross Rev")
PLamount

query results:
(Product), (gross rev/gross unit)

I know a pivot table might better handle this, but I'd like to know
how to do this without a pivot. Any suggestions are appreciated...


I might select the product and a column summed for units and a column
for summed revenue and group on product.
Select Product, _
Sum(IIF([PLType]="Gross Units",PLAmount,0)) As Units, _
Sum(IIF([PLType]="Gross Rev",PLAmount,0)) As Revenue...

You end up with 3 cols, Product, Units, Rev and you can calculate as you
like.
Nov 13 '05 #2

P: n/a
Thanks! That gets me on the right track...
Salad <oi*@vinegar.com> wrote in message news:<_e*****************@newsread1.news.pas.earth link.net>...
John wrote:
I'm a bit confused on how to use the query builder to create an
expression that will calculate gross revenue per gross unit using the
tables below:

table_1:
Product
PLtype (e.g., "Gross Units" or "Gross Rev")
PLamount

query results:
(Product), (gross rev/gross unit)

I know a pivot table might better handle this, but I'd like to know
how to do this without a pivot. Any suggestions are appreciated...


I might select the product and a column summed for units and a column
for summed revenue and group on product.
Select Product, _
Sum(IIF([PLType]="Gross Units",PLAmount,0)) As Units, _
Sum(IIF([PLType]="Gross Rev",PLAmount,0)) As Revenue...

You end up with 3 cols, Product, Units, Rev and you can calculate as you
like.

Nov 13 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.