Results 1 to 7 of 7
  1. #1
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476

    Trying this query from 2-Tables

    2-TABLES:
    TABLE 1: Employees/Payroll
    EmployeesPayrollID
    Mnth/Year
    Day
    Date
    Employees Name
    HRS (Hours worked – Date)

    TABLE 2: Projects/Tasks
    ProjectsTasksID


    EmployeesPayrollIID
    Mnth/Yr
    Day
    Date
    Project Name
    HRS (Hours worked on Project – Date)

    *Tables are joined by “EmployeesPayrollID”

    Why can I NOT run a query showing me both the #-hours from the Employees/Payroll Table & from the Projects\Tasks Table of the same date?

    I tried pulling “Mnth/Yr,” ”Day,” ”Date,”& “HRS” from the Eployees\Payroll Table -&- the “HRS” from the Projects\Tasks Table & selected, “Sum” under both HRS & in return I got no results. Can I get any help?

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Some help:

    Day, Date are reserved words in Access.
    Here's a full list
    http://allenbrowne.com/AppIssueBadWord.html

    It is recommended that you do NOT use special characters or embedded spaces in your field and object names.

    Show us the query you were trying to run.

  3. #3
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    I showed my Query above:
    I tried pulling “Mnth/Yr,” ”Day,” ”Date,”& “HRS” from the Eployees\Payroll Table -&- the “HRS” from the Projects\Tasks Table & selected, “Sum” under both HRS & in return I got no results.

    Ought I change my DAY & DATE Field Names?


  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    Can you post the sql for your query?

    eg
    Code:
    SELECT Reservations.[Room Type], Avg(DateDiff("d",[Arrival Date],[Departure Date])) AS [Avg Length of Stay], Avg(Reservations.[No of Guests]) AS [Avg # of Visitors], Avg(Reservations.[Daily Rate]) AS [Daily Rate]
    FROM Reservations
    GROUP BY Reservations.[Room Type]
    ORDER BY Reservations.[Room Type];

  5. #5
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    Gladly, I went ahead & changed my DAY & DATE Field Names

    SELECT [Daily Tasks].[MNTH/YR], [Daily Tasks].WDAY, [Daily Tasks].TDATE, Sum([Daily Tasks].HRS) AS SumOfHRS1, Sum([Employees/Payroll].HRS) AS SumOfHRS
    FROM [Daily Tasks] INNER JOIN [Employees/Payroll] ON [Daily Tasks].DailyTaskID = [Employees/Payroll].DailyTaskID
    GROUP BY [Daily Tasks].[MNTH/YR], [Daily Tasks].WDAY, [Daily Tasks].TDATE;

  6. #6
    djclntn is offline Competent Performer
    Windows 7 32bit Access 2010 32bit
    Join Date
    Feb 2011
    Posts
    476
    I'm not sure if I sent you what you were asking?

  7. #7
    orange's Avatar
    orange is online now Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,847
    I have mocked up a query using my own tables and data to approximate your situation.

    The query and the table structures are shown in djclntn0.jpg
    The query result and the data in the tables is shown in djclntn1.jpg

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

Similar Threads

  1. Query on 3 tables
    By aveit in forum Access
    Replies: 0
    Last Post: 04-26-2011, 12:01 PM
  2. Definition Tables using a Query?
    By AccessMaccess in forum Access
    Replies: 5
    Last Post: 03-22-2011, 10:01 AM
  3. Query Multiple Tables
    By Tomfernandez1 in forum Queries
    Replies: 1
    Last Post: 02-04-2011, 09:31 PM
  4. Combining Tables by Query
    By jlclark4 in forum Queries
    Replies: 9
    Last Post: 01-25-2011, 12:19 PM
  5. Query -2 tables. Help.
    By zuli007 in forum Queries
    Replies: 1
    Last Post: 04-15-2010, 10:02 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