Results 1 to 6 of 6
  1. #1
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162

    Query to lookup relative field from table selection

    Im not too sure if I will word this correctly but here goes... A user selects a job and assigns hours to that job, a single user can select multiple jobs and assign multiple hours to multiple jobs. I am trying to sum those hours on a per job basis which wouldnt be difficult but the field name within the job is relative to the selection made. So they could select "washing up" from the list but in the activity table washing up relates to WS001 in the washing up table and its WS001 that I am trying to tally and display not the washing up field.



    Hope this makes sense? Any help is appreciated - thanks.

  2. #2
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Nedd more information. It's obvious, but can't you group by the job code in the activity table and sum the hours and link to the jobs table to get the job description?

  3. #3
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Ok so if you can imagine if I run a query on the table which holds the results from the form I'll get the wrong field displayed in the Project column (which is a different table). The result I get is column I have visable in the form and the time assigned to that. What I would actually like to display is the row corrosponding to the project field (the project table has 3 rows) and base the output on that.

    Is this any clearer? Probably not

  4. #4
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Not clear! Now you are mentioning another field called Project, how does job relate to project? When the user enters the hours for the job, is the job part of a project? Do you want hours per job or hours per project?

  5. #5
    shabbaranks is offline Competent Performer
    Windows 7 32bit Access 2007
    Join Date
    Oct 2011
    Posts
    162
    Apologies, I inititially used washing up etc to try and simplify my example - hence why I moved from washing up/jobs to projects.

    I'll try again, the code is grouped in the project table. The project table has 3 columns ID1, ID2 and ID3

    The selection made in the form is ID1 and I am trying to run a query which uses the selection of ID1 to determin ID3. There is no relationship between the table which holds the forms selection and the table which holds the project info - which may be my downfall?.

    So in the end a user applies 3 hours to ID1 which is admin but I need to output admins ID3 as the amount of hours in the query. Is this making sense now?

  6. #6
    aytee111 is offline Competent At Times
    Windows 7 32bit Access 2010 32bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Maybe it is the design of the tables which is causing you (and me!) confusion. All tables should be joined (except in very rare occurrences) and also names should be meaningful (that will help with troubleshooting). So the three ID fields on your project table should have nice names, such as ProjectID, JobID, etc.

    I assume your form is bound to a table? That table should be joined to projects and jobs etc. If I have got it right, then a project has jobs (there's a link) and a job has hours. That might make it easier to identify what you are trying to get totals for.

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

Similar Threads

  1. table lookup / null field values, help!
    By benjammin in forum Forms
    Replies: 3
    Last Post: 07-27-2011, 01:56 PM
  2. Replies: 0
    Last Post: 03-15-2011, 07:35 AM
  3. Relative Data from a TABLE
    By hashjax in forum Access
    Replies: 1
    Last Post: 02-09-2011, 02:57 PM
  4. Replies: 3
    Last Post: 12-28-2010, 10:43 PM
  5. Replies: 0
    Last Post: 04-17-2008, 09:24 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