#1
  1. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2004
    Posts
    33
    Rep Power
    14

    Question Query multiple tables


    Can anyone help me, I have a database with a number of employee timesheets listing scheduled hours & various rates of overtime. I need to run a query that will sum each employees records by month & display this in a single query. I can query each record individually but cant seem to create a query with all the employees totals in the one query.

    Thanks in advance.

    Larry
  2. #2
  3. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    22
    Use Groupings

    S-
  4. #3
  5. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2004
    Posts
    33
    Rep Power
    14

    Unhappy


    Sorry for my ignorance, can you expand on this answer?

    Thanks.
  6. #4
  7. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    22
    Post a sample DB with you table and smaple data and tell me what results you need and I will show you how to do it.

    S-
  8. #5
  9. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2004
    Posts
    33
    Rep Power
    14
    Soory for the delay.
    I've attached a copy of my test database with 2 timesheets for May.
    What i want to be able to do is run a query that will give me totals for the Scheduled hours, on call allowance, & various overtime rates listed by the Employee name & Month/year in a single query or report
    Attached Files
  10. #6
  11. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    22
    Becuase each timesheet is a seperate table use the following
    SQL for your query


    Code:
    SELECT T.Engineer, Sum(T.[Scheduled hours]) AS [Scheduled hours], Sum(T.[On call]) AS [On call], Sum(T.[Overtime *1/2]) AS [Overtime *1/2], Sum(T.[Overtime *2]) AS [Overtime *2], Sum(T.[Overtime *3]) AS [Overtime *3]
    FROM [Timesheet 2] AS T
    GROUP BY T.Engineer
    UNION
    SELECT T.Engineer, Sum(T.[Scheduled hours]) AS [Scheduled hours], Sum(T.[On call]) AS [On call], Sum(T.[Overtime *1/2]) AS [Overtime *1/2], Sum(T.[Overtime *2]) AS [Overtime *2], Sum(T.[Overtime *3]) AS [Overtime *3]
    FROM Timesheet AS T
    GROUP BY T.Engineer;
    If everything was in one table you would use


    Code:
    SELECT T.Engineer, Sum(T.[Scheduled hours]) AS [Scheduled hours], Sum(T.[On call]) AS [On call], Sum(T.[Overtime *1/2]) AS [Overtime *1/2], Sum(T.[Overtime *2]) AS [Overtime *2], Sum(T.[Overtime *3]) AS [Overtime *3]
    FROM [Timesheet 2] AS T
    GROUP BY T.Engineer

    Good Luck

    S-
  12. #7
  13. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2004
    Posts
    33
    Rep Power
    14

    Thumbs up


    Thanks for your help, that works a treat for totals but i need to show totals by month as well as engineer as each table will hold many months worth of data.
    I know the code is probably simple but I am a novice at SQL.
    Thanks again.

    Larry.
  14. #8
  15. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    22
    Code:
    SELECT Q.Month, Sum(Q.[Scheduled hours]) AS [Scheduled hours], Sum(Q.[On call]) AS [On call], Sum(Q.[Overtime *1/2]) AS [Overtime *1/2], Sum(Q.[Overtime *2]) AS [Overtime *2], Sum(Q.[Overtime *3]) AS [Overtime *3]
    FROM 
    (SELECT T.Date, Format$([date],"mmmm") AS [Month], T.[Scheduled hours], T.[On call], T.[Overtime *1/2], T.[Overtime *2], T.[Overtime *3]
    FROM TimeSheet as T
    UNION SELECT T.Date, Format$([date],"mmmm") AS [Month], T.[Scheduled hours], T.[On call], T.[Overtime *1/2], T.[Overtime *2], T.[Overtime *3]
    FROM [TimeSheet 2] as T) as Q
    GROUP BY Q.Month;

    S-
  16. #9
  17. No Profile Picture
    Contributing User
    ASP Explorer (0 - 99 posts)

    Join Date
    May 2004
    Posts
    33
    Rep Power
    14

    "syntax error in from clause"


    Thanks SBaxter but i am getting "syntax error in from clause" errors with that last code, can you check again for me.
    Sorry to be a pain.
    P.S I'm using access 97, I don't know if that has any relevance.
    Thanks.

    Larry.
  18. #10
  19. No Profile Picture
    Moderator: Access, SQL
    ASP Mastermind (5000+ posts)

    Join Date
    Oct 2003
    Posts
    5,126
    Rep Power
    22
    Try taking the "$" off the format

    S-

Similar Threads

  1. Delete Data From Msaccess Query On Sql Server Linked Tables
    By nouria in forum Microsoft Access Help
    Replies: 3
    Last Post: April 20th, 2004, 04:27 AM
  2. Delete data from multiple tables
    By nsf247 in forum ASP Development
    Replies: 1
    Last Post: March 13th, 2004, 07:06 PM
  3. Crosstable query returns too many records
    By Palsam in forum ASP Development
    Replies: 7
    Last Post: March 12th, 2004, 09:07 AM
  4. Crosstable query returns too many records
    By Palsam in forum Microsoft Access Help
    Replies: 4
    Last Post: March 12th, 2004, 08:56 AM
  5. Query to calculate total of four tables
    By kabir_hussein in forum Microsoft Access Help
    Replies: 1
    Last Post: March 5th, 2004, 11:39 AM

IMN logo majestic logo threadwatch logo seochat tools logo