Basic query help needed...

[John]

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...

[Salad]

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.

[John]

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.