Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Summary report (Access 2000)

    I need help with filitering a report.
    I want to increase the flexibility of my Summary Report i have built entirely with the help of the present forum.
    My summary report shows the sales of the customers per year and per month.It is a marvelous report.
    Is it possible to differentiate between customers who have decreased their sales in 2004 compared with 2003 and also the opposite,the customers who have increased it.
    For example, in the example attached, made by Hans of the present forum,the customer has sold 965 liters in 2993 and 1920 liters in 2004.
    So, the customers has increased the sales and must not be in the first list.
    In short i want to have two lists, one "down" for the decreased sales and one for "up", for the increased sales.
    To this end i must somehow substract the quantities.How can i do it ?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Summary report (Access 2000)

    See attached version.
    Important points:
    - A crosstab query to calculate the yearly total per company.
    - Queries qryIncrease and qryDecrease that use the crosstab query and the existing Query1 to select records for the companies that show an increase/decrease.
    - An additional group box on the form.
    - Additional code in the On Open event of the report to set the record source depending on the selection in the group box.

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary report (Access 2000)

    Thank you very much for your wonderful
    suggestion.I cannot find words to express my admiration to you and also what Access can do.
    In my case my code is rather complicated and i would like to ask you for your advice how shall i proceed.
    I have an sql instead of a query,because it is easier for me to work with 12 affilates.my code is the folloiwng

    Private Sub Report_Open(Cancel As Integer)
    town = Forms![fBenchmark]![Office]
    On Error Resume Next
    Me.GroupFooter3.Visible = (Forms!fBenchmark!grpsummary = 1)
    Me![heading].Caption = Branching

    bas = " SELECT Format([InvoiceDate],'mmmm') AS MonthName, DatePart('m',[invoicedate]) AS

    MonthNumber, customers.CompanyName, Sum([order details].liters) AS SumOfLiters,

    customers.Customerid, orders.invoicedate, customers.city, customers.kindid" & _
    " FROM (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND

    (customers.Customerid = orders.customerid)) INNER JOIN [order details] ON orders.orderid = [order

    details].OrderID " & _
    " WHERE (((orders.paymentid) = True) And ((customers.afid) = " & town & "))" & _
    " GROUP BY Format([InvoiceDate],'mmmm'), DatePart('m',[invoicedate]), customers.CompanyName,

    customers.Customerid, orders.invoicedate, customers.city, customers.kindid " & _
    " ORDER BY DatePart('m',[invoicedate]), customers.CompanyName"
    Me.RecordSource = bas
    End Sub


    SO,in order to use your database,i must forget the sql and create queries for each affiliate.I can do it, but before that i want to ask you , can we do it with sql.If not, then i will make the queries and use your wonderful suggestion.

    Regards

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Summary report (Access 2000)

    It does become more complicated, but you don't need to create separate queries for each affiliate. In the attached version, I have added a combo box to the form, and the queries from the previous version use this combo box. If you leave the combo box blank, all affiliates will be included in the report, and if you select an affiliate, only data for that one will be included.

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Summary report (Access 2000)

    Thank you for your reply. Is it possible to enter the parameter value of a list box instead of combo box? I think so but why
    do i receive errors?I have tried to enter these parameters in the queries and i coulnd.
    Could you please have a look at my database?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 28 Times in 28 Posts

    Re: Summary report (Access 2000)

    You didn't attach a database, so I modified the one I attached previously.

    Open each query in design view.
    Select Query | Parameters.
    If there is a parameter referring to the combo box, you will have to make it refer to the list box instead.

    Note: once you select an item in the list box, you can't "clear" the selection any more, so the option to include all affiliates is not available.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •