1. This site uses cookies. By continuing to use this site, you are agreeing to our use of cookies. Learn More.

Advanced report for checking targets

Discussion in 'MS Access' started by Arest, Jul 29, 2009.

  1. Arest

    Arest New Member

    Joined:
    Jul 29, 2009
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    I have a deadline till Friday and really hope that you could help me. Sorry if I did something wrong..
    Problem: I was asked to create a report. I have a table with projects (“projects”) and a table with targets (“targets”). Every project has a date, value and a type (two types). “Targets” has: month, target value and type. The idea is to create a report which shows if the target was achieved for each month. And if there are no projects in one of the months then the report should display “0”.
    Table “Projects”
    ProjectID, Project name, Customer, Date started, Value, Type
    Table “Targets”
    TargetID, Month, Target, Type (For example, for April target for funding project was £1000)
    The output should look like on the picture attached.
    I have no idea if it possible to implement using access (how to connect these 2 tables..). But I was asked to do it in access since the information about all the projects is already there.
    Picture description (the act val – value of the project, the target value – value from the targets table).
    I will be very happy to hear all the suggestions and advices.
    Thank you!
    img529.imageshack.us/img529/1879/52466453.jpg
     
  2. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Hi Arest,

    What a name!!! Did someone arrest you OR you arrest someone :)

    It would be great if you attach the access file here? (with dummy data)

    Also , In table Projects, only Date Started is there, where is Date Stopped?
    I believe you want to extract month from this date and then compare with the Targets table. Correct?

    Also, in the image, what's the diff between Type 1 and Type 2?
     
  3. Arest

    Arest New Member

    Joined:
    Jul 29, 2009
    Messages:
    2
    Likes Received:
    0
    Trophy Points:
    0
    Thank you for the reply :)
    You are right, I just want to compare the targets with actual value.. I attached a dummy query.
     

    Attached Files:

  4. shabbir

    shabbir Administrator Staff Member

    Joined:
    Jul 12, 2004
    Messages:
    15,283
    Likes Received:
    364
    Trophy Points:
    83
    What type of file is accd file ?
     
  5. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    That's MS Access 2007 File, accdb
     
  6. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Hi Arest, you got pretty close.

    First tell me, in the table Targets, why did you put the data type for month as text?

    See the updated file with below changes.
    1] Changed Data Type of Month_ in Targets to Number (for this I had to delete the relationship)
    2] In the query "This is where the problem..." I removed the table Projects and added the Query "Projects Query"
    3] Added a join in Months column of both
     

    Attached Files:

  7. nimesh

    nimesh New Member

    Joined:
    Apr 13, 2009
    Messages:
    770
    Likes Received:
    20
    Trophy Points:
    0
    Occupation:
    Oracle Apps Admin
    Location:
    Mumbai
    Home Page:
    Arest: Did you check the updated file that I attached in the last post?
     

Share This Page