Results 1 to 11 of 11
  1. #1
    Join Date
    Mar 2009
    Posts
    120

    Red face Unanswered: Two Tables and a Calculation

    Good Morning,
    I am having some trouble with a query and tables.
    I have two tables, Invoice and Clock. Both tables have hours and dollars in them. I am using the Invoice table to calculate an Average Rate.
    I am pulling that average rate into a query for the Clock hours to calculate Dollars however, I don't have the same Job Codes in both tables at the moment.
    Since I don't have the Job Code, the Average Rate calculation isn't making one from the Invoice table and so my Clock Query isn't pulling in the Job Codes that do not have an Average Rate and they are not printing on the reports.
    Is there a way to make the Average Rate calculate for missing Job Codes even though they are not in the Invoice Table currently?
    I tried using the Join Types but got the Ambiguous outer join error.
    i am rather stuck on this one. Syntax in Access is very tricky.
    Any help is appreciated.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    please show the query that got the error
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2009
    Posts
    120

    Query

    here is a screen shot of the query I am using.
    Attached Files Attached Files

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CHI Brian View Post
    here is a screen shot of the query I am using.
    thanks, but i was hoping you would go to SQL View and copy/paste the text of the query

    also, your original post said two tables, but there appear to be more than two in that diagram
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2009
    Posts
    120

    SQL View

    SELECT [Z-Period].Period, [Z-Period].Month, [A-Agency Clock Hours].[Full Month], [A-Agency Clock Hours]![Lawson #] & "-" & [Z-Account Unit]![Description] AS [Clock Dept], [A-Agency Job Codes].ID, [A-Agency Clock Hours]![Job Code] AS [Clock Job Code], Sum([A-Agency Clock Hours]![Hours]) AS [Clock Hours], Sum([A-Agency Clock Hours]![Hours])*Sum([A-Agency Average Rate]![Avg Rate]) AS [Estimated Dollars], [A-Agency Average Rate]![Avg Rate] AS [Inv Avg Rate], [A-Agency Clock Hours]![Hours]/[Z-Period]![Tot Hrs] AS [Clock FTE]
    FROM ([A-Agency Job Codes] INNER JOIN ([Z-Period] INNER JOIN ([Z-Account Unit] RIGHT JOIN [A-Agency Clock Hours] ON [Z-Account Unit].[Lawson #] = [A-Agency Clock Hours].[Lawson #]) ON [Z-Period].[Full Month] = [A-Agency Clock Hours].[Full Month]) ON [A-Agency Job Codes].ID = [A-Agency Clock Hours].[Job Code]) INNER JOIN [A-Agency Average Rate] ON ([A-Agency Clock Hours].[Lawson #] = [A-Agency Average Rate].[Lawson #]) AND ([A-Agency Job Codes].ID = [A-Agency Average Rate].ID)
    GROUP BY [Z-Period].Period, [Z-Period].Month, [A-Agency Clock Hours].[Full Month], [A-Agency Clock Hours]![Lawson #] & "-" & [Z-Account Unit]![Description], [A-Agency Job Codes].ID, [A-Agency Clock Hours]![Job Code], [A-Agency Average Rate]![Avg Rate], [A-Agency Clock Hours]![Hours]/[Z-Period]![Tot Hrs];

    yes there are several tables involved. I should have stated that. I am using the Main 2 tables Invoice and Clock, but there is a query that Pulls the Invoice Dollars and Hours and calculates the Average Rate by Job Code, which is a table as well.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CHI Brian View Post
    Is there a way to make the Average Rate calculate for missing Job Codes even though they are not in the Invoice Table currently?
    here's your query, reformatted from the default Access pukefest --
    Code:
    SELECT [Z-Period].Period
         , [Z-Period].Month
         , [A-Agency Clock Hours].[Full Month]
         , [A-Agency Clock Hours]![Lawson #] & "-" & [Z-Account Unit]![Description] AS [Clock Dept]
         , [A-Agency Job Codes].ID
         , [A-Agency Clock Hours]![Job Code] AS [Clock Job Code]
         , Sum([A-Agency Clock Hours]![Hours]) AS [Clock Hours]
         , Sum([A-Agency Clock Hours]![Hours])*Sum([A-Agency Average Rate]![Avg Rate]) AS [Estimated Dollars]
         , [A-Agency Average Rate]![Avg Rate] AS [Inv Avg Rate]
         , [A-Agency Clock Hours]![Hours]/[Z-Period]![Tot Hrs] AS [Clock FTE]
      FROM (
           [A-Agency Job Codes] 
    INNER 
      JOIN (
           [Z-Period] 
    INNER 
      JOIN (
           [Z-Account Unit] 
    RIGHT 
      JOIN [A-Agency Clock Hours] 
        ON [Z-Account Unit].[Lawson #] = [A-Agency Clock Hours].[Lawson #]
           ) 
        ON [Z-Period].[Full Month] = [A-Agency Clock Hours].[Full Month]
           ) 
        ON [A-Agency Job Codes].ID = [A-Agency Clock Hours].[Job Code]
           ) 
    INNER 
      JOIN [A-Agency Average Rate] 
        ON (
           [A-Agency Clock Hours].[Lawson #] = [A-Agency Average Rate].[Lawson #]
           ) 
       AND (
           [A-Agency Job Codes].ID = [A-Agency Average Rate].ID
           )
    GROUP 
        BY [Z-Period].Period
         , [Z-Period].Month
         , [A-Agency Clock Hours].[Full Month]
         , [A-Agency Clock Hours]![Lawson #] & "-" & [Z-Account Unit]![Description]
         , [A-Agency Job Codes].ID
         , [A-Agency Clock Hours]![Job Code]
         , [A-Agency Average Rate]![Avg Rate]
         , [A-Agency Clock Hours]![Hours]/[Z-Period]![Tot Hrs];
    there doesn't seem to be any calculation involved

    i'm not sure i understand the part about "missing Job Codes even though they are not in the Invoice Table currently"

    i'm afraid in order to help you further, i'm going to need you to explain the one-to-many relationships involved in the joins, i.e. which table has the PK and which table has the FK, for every join in your FROM clause

    you can start things off by explaining why you're using a RIGHT JOIN
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2009
    Posts
    120

    Confused

    I am not a database guru so am not real sure what you are asking from me.

    I don't know why i am using a right join. The data base made that itself.

    The tables involved are:
    Invoice
    Clock
    Department
    Job Code
    Names
    Period


    Invoice, Clock are joined to Department & Job Code via Lookup fields.
    Invoice is also joined to Names.

    In this situation, the tables in the query are: Clock, Department, Job Code as well as a query called Average Rate.

    The query takes the Hours from Clock, sums them by Departmetn and Job Code it also takes the Avg Rate field from the Average Rate Query.

    The issue is this. The Clock table has a job code of Tech with hours. The Invocie table does not have that job code listed as of yet, we haven't received the invoice for that one. So when the Average Rate query calculates the average rate it doesn't calculate that job code.
    So when I pull the Clock Hours from the table and attempt to calculate Estimated Dollars, I am missing some departments because they do not have an Average Rate in the Average Rate Query.

    I hope this makes sense.

  8. #8
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CHI Brian View Post
    I hope this makes sense.
    yup

    try this --
    Code:
    SELECT period.Period
         , period.Month
         , clock.[Full Month]
         , clock.[Lawson #] & "-" & 
           unit.[Description]          AS [Clock Dept]
         , codes.ID
         , clock.[Job Code]            AS [Clock Job Code]
         , SUM(clock.[Hours])          AS [Clock Hours]
         , SUM(clock.[Hours]) * 
           SUM(avgrate.[Avg Rate])     AS [Estimated Dollars]
         , avgrate.[Avg Rate]          AS [Inv Avg Rate]
         , clock.[Hours] / 
           period.[Tot Hrs]            AS [Clock FTE]
      FROM (((
           [A-Agency Clock Hours] AS clock 
    INNER
      JOIN [A-Agency Job Codes] AS codes
        ON codes.ID = clock.[Job Code] 
           )
    INNER 
      JOIN [Z-Period] AS period
        ON period.[Full Month] = clock.[Full Month]
           )
    INNER 
      JOIN [Z-Account Unit] AS unit
        ON unit.[Lawson #] = clock.[Lawson #]
           )
    LEFT OUTER 
      JOIN [A-Agency Average Rate] AS avgrate
        ON (
           avgrate.[Lawson #] = clock.[Lawson #]
       AND avgrate.ID = codes.ID
           )
    GROUP 
        BY period.Period
         , period.Month
         , clock.[Full Month]
         , clock.[Lawson #] & "-" & unit.[Description]
         , codes.ID
         , clock.[Job Code]
         , avgrate.[Avg Rate]
         , clock.[Hours]/period.[Tot Hrs]
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by r937 View Post
    you can start things off by explaining why you're using a RIGHT JOIN
    Because the query builder you adore so much <3s them!
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Mar 2009
    Posts
    120
    I have no clue how to do what your saying. I dont know SQL at all. I use the regular Query builder in Acess. I found a way around it in most cases, so thank you just the same.

  11. #11
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by CHI Brian View Post
    I have no clue how to do what your saying.
    you don't know how to try running my query?

    start a new query, select something from some table, then switch to SQL View

    erase whatever you see (it'll start with the word SELECT, right?), and then paste in the SQL i gave you, and run it

    let me know how it goes
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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