z.******@gmail.com wrote:
I am designing an order database at work and am having problems
creating a specific report I'm after.
Basically, each order has an 'order type' and what I would like is a
report which lists the order types and simply gives a number as to the
total amounts of that order type over a certain time period.
I've managed to build a query which gives me details of every order and
their order-type over a certain time, but I cant seem to work out how
to get Access to tot up the amounts of each order type.
The other reports I have are very similar, but with different fields.
I would appreciate any advice
Thanks
Two approaches. One, create a new query. Drag the Order#, Type, and
amount fields to the query. From the menu, select Query/Totals. Make
sure Order#, Type are GroupBy and Sum is under the Amount field. Now
link this query to your report query. This query approach will be
snappy and fast. You can use this field to get Order totals and a sum
of the entire report
Another method is to use DSum() in your report query. It might look
something like
OrderTotal:Dsum("OrderAmt","OrderItms","OrderID = " & _
[OrderID])
Look at your reports. See how you can filter them to get your results
in the SQL statement for the Report's recordsource. Then you can open
you reports with something like
Docmd.OpenReport "Report1",,,"OrderType = 1"