Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2013
    Posts
    2

    Unanswered: Query showing latest note for each project

    So I am going to see if I can describe my problem succinctly enough that someone can help me.

    I am trying to run a query based on two tables.

    The first is a project table, currently I have access pulling the following fields from the table

    ProjectNum ProjectName ProjectStatus and ProjectPhase

    ProjectStatus indicates whether the project is open or closed, and the ProjectPhase indicates wither it is in design or in construction administration

    I was able to setup a query that only showed open projects and sorted them by ProjectPhase and then ProjectNum from earliest project to latest project

    That part worked fine, however the problem is when I try to implement the second table.

    The second table is a ProjectNotes table it has ProjectNum InputDate and Note fields.

    What I would like to do is to display the latest note inputted for each project into the first query.

    I have tried researching this as much as possible before posting this, most solutiones are byzantine in nature and not working.

    One solution I tried was adding the Note field from the ProjectNotes table to the first query I made, then turning on the Totals section and selecting Last for the Notes field. However when i do this any projects that don't have a note for them on the ProjectNotes table become excluded from the query.

    I am pretty new to Access so use small words Any help would be much appreciated. Thanks for your time

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    what you could do is use a sub query to find the latest post
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2013
    Posts
    2
    Thanks for the tip! Sub queries look very useful I defiantly will try them out. I eventually got it to work. I took the first query that found the open orders and their status and then added that inputdate and note field from my ProjectNotes table. I then added the Total section and set the Note and InputDate to Last. Finally to get the InputDate and Note fields not to restrict the data output by the query I changed the join properties to

    "2: Include ALL records from 'ProjectT' and only those records from 'ProjectNotesT' where the joined fields are equal"

Posting Permissions

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