Results 1 to 3 of 3
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Cornwall, England
    Posts
    393
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Query Hell (access2000)

    I am trying to write a query that will support a report for our accountants. i need to give staff details (name, hours worked, hours spent on each project, salary cost) by project. i have however run into a brick wall. i have a normal select query getting name etc from staff details table, hours worked data from a history table and project details from a projects table.
    something like this:
    NAME Total hours project hours
    fatherjack 100 56
    <<next project group>>
    fatherjack 100 25

    i need to exclude some time from the history table as it relates to sickness, toil, maternity leave etc and should not appear in the total hours figure. to do this i have constructed this line :
    Adjusted Total Hours: Sum(IIf(Right([number],3) In (980,920,983,984,985,986,987,988,989,990,991,992,9 93,994),0,([hours]))) which i figure should total the hours per person where the project code is not in the listed codes. i get an erro message though which complains about the other fields in the query ...
    "you tried to execute a query that does not include the specified expression <<fieldnames listed here>> as part of an aggregate function"

    to resolve this i clicked on the Totals button in the toolbar and this helped until i wanted to introduce a third table to the query. i need to have the project code in the report. adding a new field of projcode, because it has a group by property i believe, returns this sort of data...
    NAME Total hours project hours
    fatherjack 56 56
    <<next project group>>
    fatherjack 25 25

    which than makes rubbish of the calculations in the report to combine the salary cost with the project hours to determine the cost of each project.

    heres hoping someone can help!

  2. #2
    3 Star Lounger
    Join Date
    Nov 2001
    Location
    Sydney, New South Wales, Australia
    Posts
    216
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Hell (access2000)

    You could try breaking the process down into stages. That is, create a stage one query to do the first part of the work ie extracting records, then create a second query based on the first to do the calculations, and so on. If this seems feasible, it's a good idea to name them appropriately eg 'qryStaffDetails_Stg1', 'qryStaffDetails_Stg2'.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Query Hell (access2000)

    I don't know if this will help, but...

    I've been doing a lot of work with our timekeeping system as well. We have hundreds of "activity codes." What I've done is put them in a lookup table and set one field of the lookup table as a way to decide whether the code is the type of hours I want to include. So if the activity code is for sick leave, then the corresponding field is admin. If the activity code is for client contacts, then the corresponding field is Work-client.

    When I run the query, I join my hours table to my lookup table, and if I want to get all Work related hours, I set the value Where ActivityType Like "Work*". Then I can write my query something like Select Project Number, Sum([Hours]) from tblActivityLookup Inner Join tblHours on tblActivityLookup.ActivityCode=tblHours.ActivityCo de Where ActivityType Like "Work*" . This actually is less complicated in the design grid.

    The best part of this is if you want to keep all the activity types in the query, you can run a crosstab query on activityType and then get all of the hours by type and by project number.

    Or did I miss the point???

    HTH,

Posting Permissions

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