Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Sales report details (Access 2000)

    I have a daunting task to make a report for the sales per month subdivided per segment and per type.

    The order should look like that:
    This is an example:
    January
    segment1 segment2
    total sales: 100 tons 80 tons 20 tons

    total sales
    Sales in Segment1 : 80 tons
    In this figure i must list all the sales per TypeID in the table Types.


    In order to help me i shall describe what i have done.
    I have made 2 tables, Segments and Types, with segmentID and TypeID.
    Then, in the table Customers i have added the lookup field TypeID to choose from the table TypeID. In the table TypeID i have a lookup field for the SegmentID in the table Segments.

    I have made a query, with an example of SegmentID = 1 and Customers.TypeID = 1
    Can you tell me am i on the right way?

    Below is my query:

    SELECT Format([InvoiceDate],"mmmm") AS MonthName, DatePart("m",[invoicedate]) AS MonthNumber, Sum([order details].liters) AS SumOfLiters
    FROM Segments, Types, (customers INNER JOIN orders ON (customers.Customerid = orders.customerid) AND (customers.Customerid = orders.customerid)) INNER JOIN (products INNER JOIN [order details] ON (products.Productid = [order details].ProductID) AND (products.Productid = [order details].ProductID)) ON orders.orderid = [order details].OrderID
    WHERE (((orders.paymentid)=True) AND ((customers.afid)=1) AND ((Year([invoicedate]))=2003) AND ((Segments.SegmentID)=1) AND ((customers.TypeID)=1))
    GROUP BY Format([InvoiceDate],"mmmm"), DatePart("m",[invoicedate]);

    With the above code i can get a nice report enumerating the totals for each month.
    But how can i subdivide these figures ? Does anybody has a database of a similar task in order to learn how can i do this task ?

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

    Re: Sales report details (Access 2000)

    If I understand your goal correctly, you don't need to design complicated queries to accomplish it. Access reports have extensive grouping and sorting capabilities.

    Create and save a simple selection query that selects the records you want to report on. Don't make it into a Totals query, and don't bother about sorting the data. Next, activate the Reports tab of the database window, and click Create Report using Wizard. Select the query you created as record source, and experiment with the grouping and sorting options the Wizard presents.

    If you want to look at advanced reports, you can download sample databases from Microsoft (recommended):
    ACC2000: Microsoft Access 2000 Sample Reports Available in Download Center
    Sample Applications Download

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Posts
    254
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Sales report details (Access 2000)

    Thank you very much. Working with the report itself was much easier than with the totals query

    regards

Posting Permissions

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