order totals

  • Thread starter Thread starter pete
  • Start date Start date
P

pete

How can i construct a query/report that gives me sales
totals for a given product number(s)? i thank you in
advance for any help. have a great day all. pete
 
In general, you construct a Totals query, grouping by one
or more fields and calculating sums, averages, counts,
etc. of other fields.

In your case, how you do this depends on how your data is
structured. As an example, I'll assume you have an Order
Details table that has fields such as:

OrderDetailID
OrderNumber (Foreign Key)
ProductID (Foreign Key)
Price
Qty

To total the entire sales by product, create a query based
on this table and select the ProductID field. Add a
calculated field by typing in the Field Row:

ExtdPrice: [Price]*[Qty]

Now, switch to Totals mode by choosing View, Totals. In
the Total row of the new query design grid, change the
value of ExtdPrice to "Sum". Leave ProductID as "Group
By".

If your data is in a different structure and you can't
figure out how to adapt this approach, post your table
structures.

HTH
Kevin Sprinkel
 
kevin,
that worked great, thankyou. Now when i try and have it
show the top ten, it seems to show the top ten ProdID
alphabetically rather than the ten highest sales figures.
any suggestions?
-----Original Message-----
In general, you construct a Totals query, grouping by one
or more fields and calculating sums, averages, counts,
etc. of other fields.

In your case, how you do this depends on how your data is
structured. As an example, I'll assume you have an Order
Details table that has fields such as:

OrderDetailID
OrderNumber (Foreign Key)
ProductID (Foreign Key)
Price
Qty

To total the entire sales by product, create a query based
on this table and select the ProductID field. Add a
calculated field by typing in the Field Row:

ExtdPrice: [Price]*[Qty]

Now, switch to Totals mode by choosing View, Totals. In
the Total row of the new query design grid, change the
value of ExtdPrice to "Sum". Leave ProductID as "Group
By".

If your data is in a different structure and you can't
figure out how to adapt this approach, post your table
structures.

HTH
Kevin Sprinkel
-----Original Message-----
How can i construct a query/report that gives me sales
totals for a given product number(s)? i thank you in
advance for any help. have a great day all. pete
.
Click to expand...
.
Click to expand...
 
Sure. In the Sort Row of your Totals query, select
Descending. By default, without specifying any sort
criteria, it will sort in Ascending order by the first
field in the query.

HTH
Kevin Sprinkel
-----Original Message-----
kevin,
that worked great, thankyou. Now when i try and have it
show the top ten, it seems to show the top ten ProdID
alphabetically rather than the ten highest sales figures.
any suggestions?
-----Original Message-----
In general, you construct a Totals query, grouping by one
or more fields and calculating sums, averages, counts,
etc. of other fields.

In your case, how you do this depends on how your data is
structured. As an example, I'll assume you have an Order
Details table that has fields such as:

OrderDetailID
OrderNumber (Foreign Key)
ProductID (Foreign Key)
Price
Qty

To total the entire sales by product, create a query based
on this table and select the ProductID field. Add a
calculated field by typing in the Field Row:

ExtdPrice: [Price]*[Qty]

Now, switch to Totals mode by choosing View, Totals. In
the Total row of the new query design grid, change the
value of ExtdPrice to "Sum". Leave ProductID as "Group
By".

If your data is in a different structure and you can't
figure out how to adapt this approach, post your table
structures.

HTH
Kevin Sprinkel
-----Original Message-----
How can i construct a query/report that gives me sales
totals for a given product number(s)? i thank you in
advance for any help. have a great day all. pete
.
Click to expand...
.
Click to expand...
.
Click to expand...
 
Back
Top