Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2012
    Posts
    1

    Unanswered: Assistance with Pivot Graph

    Hello Everyone!

    I'm trying to build a database for a small company to record its expenses and then generate reports to analyse the expenditure. I'm having trouble with one report that I want to do. I'd like the report to compare time periods as specified by the user and present the data on a graph.

    For example, the data is structured as follows:

    Date -- Expense Classification -- Amount

    Now, i'd like the user to be able to select two time period through a query and then plot these on a graph to show the comparison of expenses grouped by expense classification on the report.

    So, for example the query will allow the user to say, choose to compare the following period (Period 1) 01-04-12 to 15-04-12 and (period 2) 15-04-12 to 30-04-12. The report should now arrange the data so it is grouped by expense classification (examples include utility bills, stationery expenses, petty cash expenses, general expenses etc.) and provide the total amount spent as per each classification in both time periods.

    Can someone please help me out with this. I've tried using cross-tab queries but I can't limit the data to show only the periods I want. I've tried Union queries and still don't get the result I need. I've tried building report based on several different queries as well and can't get the graph to plot the data properly.

    Any help on this would be appreciated.

    Thanks
    Last edited by uberkoen; 07-11-12 at 14:46.

  2. #2
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    Never mind.
    Last edited by Sam Landy; 07-15-12 at 19:19.

  3. #3
    Join Date
    May 2004
    Location
    New York State
    Posts
    1,178
    I've tried using cross-tab queries but I can't limit the data to show only the periods I want.
    Have you tried a WHERE clause in the query? You can make a combo box to select a period length (e.g. one week, two weeks, 10 months, or anything else). Another cb to select period1 begin, and another cb for period2 begin.

    The second column of the the 'period length' cb would be the first 2 arguments of the DateAdd() function.

    As in WHERE YourDateField Between <the value of 'period1 cb'> and DateAdd(<the value of column 2 here>,<the value of 'period1 cb' here>). If period1 begins 4/1/12, period2 begins 4/15/12, and the selected period is two weeks, the final WHERE clause would look like
    Code:
    WHERE (YourDateField Between #4/1/2012# and #4/14/2012#) Or (YourDateField Between #4/15/2012# and #4/28/2012#)
    I will allow you the freedom to learn how to build the SQL statement to get that format. Just remember that you must have the hash marks (#) to delimit your date constants.

    Read up on the DateAdd() function in the Help file; also, how to program multiple columns in combo boxes.

    We'll be here to help if needed.

    Good luck,

    Sam
    Last edited by Sam Landy; 07-17-12 at 19:04.

Posting Permissions

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