Results 1 to 10 of 10
  1. #1
    Join Date
    Apr 2008
    Posts
    3

    Unanswered: Using JOINS in Access

    Hey, I'm pretty used to writing SQL, but I'm having some difficulty when migrating over to Access to create reports, etc. I've created an ODBC connection to SQL Server 2005, and my particular server. Basically, I have a database that tracks IT employees, current projects, and hours logged on those projects, among other things. Here's what I want to do: Create a query that displays ALL employee names and the project they are working on. If an employee have is not working on any projects then display that individual as having ‘No Project’.

    The only problem is that these fields are located on separate tables. I'll post the DB code from SQL if that will help:

    Code:
    CREATE TABLE project_employee (
      employee_id               INT,
      employee_name             VARCHAR(40),
      employee_hire_date        DATETIME,
      employee_termination_date DATETIME,
      employee_billing_rate     INT,
      CONSTRAINT project_employee_pk
        PRIMARY KEY (employee_id)
    );
    
    CREATE TABLE project (
      project_id              INT,
      project_name            VARCHAR(40),
      project_budget          INT,
      CONSTRAINT project_pk
        PRIMARY KEY (project_id)
    );
    
    CREATE TABLE project_hours (
      project_id              INT,
      employee_id             INT,
      time_log_date           DATETIME,
      hours_logged           INT,
      dollars_charged         INT,
      CONSTRAINT project_hours_pk
        PRIMARY KEY (project_id, employee_id, time_log_date),
      CONSTRAINT proj_hours_fkto_projectemployee
        FOREIGN KEY (employee_id) REFERENCES project_employee,
      CONSTRAINT proj_hours_fkto_project
        FOREIGN KEY (project_id) REFERENCES project
    );
    
    INSERT INTO project
      (project_id, project_name, project_budget)
      VALUES (1001, 'Corporate Web Site',1912000);
    INSERT INTO project
      (project_id, project_name, project_budget)
      VALUES (1002, 'Year 2000 Fixes',999998000);
    INSERT INTO project
      (project_id, project_name, project_budget)
      VALUES (1003, 'Accounting System Implementation',897000);
    INSERT INTO project
      (project_id, project_name, project_budget)
      VALUES (1004, 'Data Warehouse Maintenance',294000);
    INSERT INTO project
      (project_id, project_name, project_budget)
      VALUES (1005, 'TCP/IP Implementation',415000);
    
    
    
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (101, 'Jonathan Gennick','15-Nov-1961',null,169);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (102, 'Jenny Gennick','16-Sep-1964','5-May-1998',135);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (104, 'Jeff Gennick','29-Dec-1987','1-Apr-1998',99);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (105, 'Horace Walker','15-Jun-1998',null,121);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (107, 'Bohdan Khmelnytsky', '2-Jan-1998',null,45);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (108, 'Pavlo Chubynsky','1-Mar-1994','15-Nov-1998',220);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (110, 'Ivan Mazepa', '4-Apr-1998','30-Sep-1998',84);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (111, 'Taras Shevchenko', '23-Aug-1976',null,100);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (112, 'Hermon Goche', '15-Nov-1961','4-Apr-1998',70);
    INSERT INTO project_employee
      (employee_id, employee_name, employee_hire_date,
       employee_termination_date, employee_billing_rate)
      VALUES (113, 'Jacob Marley', '3-Mar-1998','31-Oct-1998',300);
    
    
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1001,101,'01-JAN-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1003,101,'01-JAN-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1005,101,'01-JAN-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1002,101,'01-FEB-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1004,101,'01-FEB-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1001,101,'01-MAR-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1003,101,'01-MAR-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1005,101,'01-MAR-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1002,101,'01-APR-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1004,101,'01-APR-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1001,101,'01-MAY-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1003,101,'01-MAY-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1005,101,'01-MAY-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1002,101,'01-JUN-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1004,101,'01-JUN-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1001,101,'01-JUL-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1003,101,'01-JUL-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1005,101,'01-JUL-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1002,101,'01-AUG-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1004,101,'01-AUG-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1001,101,'01-SEP-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1003,101,'01-SEP-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1005,101,'01-SEP-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1002,101,'01-OCT-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1004,101,'01-OCT-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1001,101,'01-NOV-1998',3.00,507.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1003,101,'01-NOV-1998',5.00,845.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1005,101,'01-NOV-1998',7.00,1183.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1002,101,'01-DEC-1998',1.00,169.00);
    INSERT INTO project_hours (project_id, employee_id, time_log_date, hours_logged, dollars_charged)
                VALUES (1004,101,'01-DEC-1998',3.00,507.00);
    Here's what I attempted when using the SQL view in the Query Design:

    Code:
    SELECT 
      EMPLOYEE_NAME , ISNULL(PROJECT_NAME ,'No Project') 
    FROM 
      wco5002_210s_PROJECT_EMPLOYEE PE
    INNER JOIN 
      wco5002_210s_PROJECT_HOURS PH ON PH.EMPLOYEE_ID = PE.EMPLOYEE_ID
    INNER JOIN 
      wco5002_210s_PROJECT P ON P.PROJECT_ID = PH.PROJECT_ID
    I'm getting an error:
    Code:
    <Message> in query expression <expression>. (Error 3075)
    Any help or suggestions about how to do this?

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    SELECT
    EMPLOYEE_NAME , ISNULL(PROJECT_NAME ,'No Project')
    FROM
    wco5002_210s_PROJECT_EMPLOYEE PE
    INNER JOIN
    wco5002_210s_PROJECT_HOURS PH ON PH.EMPLOYEE_ID = PE.EMPLOYEE_ID
    INNER JOIN
    wco5002_210s_PROJECT P ON P.PROJECT_ID = PH.PROJECT_ID

    that seems a real mishmash

    Code:
    SELECT EMPLOYEE_NAME , iif(ISNULL(PROJECT_NAME),'No Project',Project_NAME)) as PROJNAME FROM    wco5002_210s_PROJECT_EMPLOYEE AS PE
    INNER JOIN  wco5002_210s_PROJECT_HOURS ON wco5002_210s_PROJECT_HOURS .EMPLOYEE_ID = PE.EMPLOYEE_ID
    INNER JOIN 
      wco5002_210s_PROJECT ON wco5002_210s_PROJECT.PROJECT_ID = wco5002_210s_PROJECT_HOURS .PROJECT_ID

    you've tried to allocate a table alias without using the as <aliasname>

    eg
    as PROJNAME FROM wco5002_210s_PROJECT_EMPLOYEE AS PE

    im not to sure of the syntax for the alias on joins so ive removed them

    you are also doing something odd with the isnull construct so Id sugest replacing it with the iif, or just select the project_name, and handle the null problem in the user interface

  3. #3
    Join Date
    Apr 2008
    Posts
    3
    I've tried implementing it as you suggested:

    Code:
    SELECT 
      EMPLOYEE_NAME as Employee, iif(ISNULL(PROJECT_NAME),'No Project',Project_NAME) as Project 
    FROM   
      wco5002_210s_PROJECT_EMPLOYEE AS PE
    INNER JOIN  
      wco5002_210s_PROJECT_HOURS ON wco5002_210s_PROJECT_HOURS.EMPLOYEE_ID = PE.EMPLOYEE_ID
    INNER JOIN 
      wco5002_210s_PROJECT ON wco5002_210s_PROJECT.PROJECT_ID = wco5002_210s_PROJECT_HOURS.PROJECT_ID
    But I'm still getting the error message in Access, I believe it's still 3075 stating I'm missing an operator. Any suggestions?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ok Id sugest you take out the IIF


    SELECT EMPLOYEE_NAME, PROJECT_NAME FROM wco5002_210s_PROJECT_EMPLOYEE AS PE
    INNER JOIN wco5002_210s_PROJECT_HOURS ON wco5002_210s_PROJECT_HOURS.EMPLOYEE_ID = PE.EMPLOYEE_ID
    INNER JOIN wco5002_210s_PROJECT ON wco5002_210s_PROJECT.PROJECT_ID = wco5002_210s_PROJECT_HOURS.PROJECT_ID

  5. #5
    Join Date
    Apr 2008
    Posts
    3
    Yea I tried that, still getting that error.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    in which case
    check your table names
    check your column names
    it could be with the way access forms joins, you may need to use "left join".... in place of "inner join"

    try with one join, then both

    seee what the current SQL looks like in the query designer

  7. #7
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Quote Originally Posted by wco5002
    a query that displays ALL employee names and the project they are working on. If an employee have is not working on any projects then display that individual as having ‘No Project’.
    employee table LEFT JOIN to your project table and you can use the Access equivalent of Coalesce() to replace the NULL values with your literal text.

    Job's a good'un
    George
    Home | Blog

  8. #8
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Mark - ISNULL() is equivalent to NZ() in SQL Server (George - I think you can only use COALESCE with JET if you up the ANSI compatibility level which IME is very rare). OP - ISNULL() in JET SQL returns a boolean indicating if the input is null or not.

    Also, the AS keyword in SQL Server for aliases is optional - it is not in JET.

    Finally - I don't write SQL for Access because it is a massive PITA but guys - doesn't this query need the obligatory JET excessive bracketicising?
    Code:
    SELECT 
      EMPLOYEE_NAME as Employee, NZ(PROJECT_NAME,'No Project') as Project 
    FROM   
      (wco5002_210s_PROJECT_EMPLOYEE AS PE
    INNER JOIN  
      wco5002_210s_PROJECT_HOURS ON wco5002_210s_PROJECT_HOURS.EMPLOYEE_ID = PE.EMPLOYEE_ID)
    INNER JOIN 
      wco5002_210s_PROJECT ON wco5002_210s_PROJECT.PROJECT_ID = wco5002_210s_PROJECT_HOURS.PROJECT_ID
    ????
    Testimonial:
    pootle flump
    ur codings are working excelent.

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pootle flump
    Mark - ISNULL() is equivalent to NZ() in SQL Server (George - I think you can only use COALESCE with JET if you up the ANSI compatibility level which IME is very rare). OP - ISNULL() in JET SQL returns a boolean indicating if the input is null or not.

    Also, the AS keyword in SQL Server for aliases is optional - it is not in JET.

    Finally - I don't write SQL for Access because it is a massive PITA but guys - doesn't this query need the obligatory JET excessive bracketicising?
    the bracketing is optional in JET, I always remove the brackets in the odd occasion I use the Query designer, just ot make it legible to me...
    being away from Access for a while made me very circumspect about using the aliases on the join as I couldn't remember the exact syntax. with tables names such as "wco5002_210s_PROJECT_HOURS ON wco5002" I could see why table aliases were a 'good' idea

    the isnull/nz function does return a boolean value, but it didn't make sense to me how it was being used in the OP. It struck me that the OP was confused about what he wanted to return.

    now we know that what he wants is a left join then the isnull/nz function is redundant as the correct choice of join brings across the correct value in project_name.

    roll on the weekend.....
    Last edited by healdem; 05-01-08 at 06:02.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by healdem
    the bracketing is optional in JET, I always remove the brackets in the odd occasion I use the Query designer, just ot make it legible to me...
    Amen brother...when it comes to SELECT, WHERE, GROUP BY etc but...they aren't optional in the FROM clause though right?....

    ISNULL() is a totally different function in Access and SQL Server. Stupid IMHO but that's what we live with. The way the OP was using it was the SQL Server syntax (logically the same as your Iif statement, or NZ()). I think the OP's grasp of SQL is fine (LEFT JOIN excepted) - (s)he's just struggling with the move to JET syntax.
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

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