Results 1 to 7 of 7
  1. #1
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    Years income and spend on a graph

    Hi guys

    i am having problems with a query that i will be using of a chart in access 2013.

    i have2 query's called income by year and spend per year

    Income per year
    SELECT [Account Transactions].[Entry Date], [Account Transactions].[Total Payment]FROM [Account Transactions] INNER JOIN Categories ON [Account Transactions].Category = Categories.ID


    WHERE ((([Account Transactions]![Money In])=-1))
    ORDER BY [Account Transactions].[Entry Date];

    spend per year
    SELECT [Account Transactions].[Entry Date], [Account Transactions].[Total Payment]
    FROM [Account Transactions] INNER JOIN Categories ON [Account Transactions].Category = Categories.ID
    WHERE ((([Account Transactions]![Money out])=-1))
    ORDER BY [Account Transactions].[Entry Date];

    each of these query's work and i am able to make a bar graph from each query that represent the figures correctly.

    what i can't seam to do is to have these query's create 1 graph that shows both the income and the spend by year, for example income would be green, spend would be red.

    if anyone could give me some pointers i would really appreciator it.
    Steve

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    I suspect the queries need common field(s) to join on. Do you want the graph by category by year? Those are not aggregate queries. What is TotalPayment? Is EntryDate mmddyyyy value?

    I analyze graph issues best when I can work with data. If you want to provide db, follow instructions at bottom of my post. Identify objects involved.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402

    Hi June7

    I have attached my database.

    i have refined my query's as i did not require the categories in it. but i still have the same issue.

    the account transactions table holds all the money in and out

    2 x query's income by year and spend by year
    2 x forms with graphs on yearly income and yearly spend

    what i am trying to do without luck is to combine both the forms into 1 form that has both the income and spend by year on the same graph.

    any help would be most appreciated.

    this is a 2013 database if that's OK

    Many Thanks

    Steve
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    The MoneyOut and MoneyIn fields would be better as one field. I would use a text field called TransType with In or Out values (or Inc, Exp). Then a CROSSTAB could be the RowSource for graph:

    TRANSFORM Sum([Account Transactions].[Total Payment]) AS [SumOfTotal Payment]
    SELECT Year([Entry Date]) AS Yr
    FROM [Account Transactions]
    GROUP BY Year([Entry Date])
    PIVOT [Account Transactions].[TransType];


    Advise no spaces in naming convention.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    Hi June

    Thanks for the reply, i thought that charts made from crosstabs was not supported under 2013 or have i got that wrong.
    many thanks for the tip will have ago with that.

    many thanks

    steve

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,822
    2013 dropped the Pivot Table/Chart option for viewing forms and tables/queries. Building a CROSSTAB query as RowSource for a graph is something else.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    sdel_nevo is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2013
    Location
    Gloucester, UK
    Posts
    402
    fantastic, many thanks
    you were great

Please reply to this thread with any new information or opinions.

Similar Threads

  1. calculation of income tax
    By chavez_sea in forum Database Design
    Replies: 14
    Last Post: 05-05-2013, 05:55 AM
  2. Replies: 6
    Last Post: 02-29-2012, 03:13 AM
  3. Rent income stream
    By liles in forum Access
    Replies: 2
    Last Post: 01-24-2012, 08:41 AM
  4. Replies: 3
    Last Post: 01-10-2011, 10:31 AM
  5. Income and outcome
    By Leszek in forum Access
    Replies: 3
    Last Post: 01-07-2009, 11:49 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums