Results 1 to 6 of 6
  1. #1
    Join Date
    Feb 2009
    Posts
    5

    Question Unanswered: Duplicate results from query resulting in Error. Help please!

    I'm trying to write a query which calculates an employee's pay for a given pay period (approx. 15 days) depending on their pay rate for that given pay period. Here's how it's done:

    1) tblContactsPayPeriods stores the employee's 700# (unique identifier) along with their wage & its beginning and end dates. Their current wage will not have an end date, so I built a query to substitute the current date.
    2) tblSalaryAllocations stores the employee's 700# (unique identifier) along with the pay period (e.g., 6/15/2008), the employee's effort (e.g., 70% of their salary/hourly wage), and the total hours worked (if applicable).
    3) A query selects the pay period from tblSalaryAllocations and tests to see if it's between the wage beginning and end dates. If it is, it calculates the employee's total pay. If not, it returns the string "Error". The code for this is:

    WageCalculation: IIf([tblSalaryAllocations]![PayPeriod] Between [qryContactsPayPeriods]![WageStartPayPeriod] And [qryContactsPayPeriods]![WageEndPayPeriod],[qryContactsPayPeriods]![HourlyWage]*[tblSalaryAllocations]![Effort]*[tblSalaryAllocations]![Hours],"ERROR")

    The problem is that the query returns two rows for each pay period. One shows the correct calculation, while the other returns error. I've uploaded two images -- one showing the query design window & the other showing the query's dataset. If anyone has suggestions for what I'm doing wrong, I'd appreciate it.

    Query design - http://i42.tinypic.com/lccr6.png

    Query error - http://i40.tinypic.com/259ial4.png

    Sorry for the wordy post, but I wanted to be clear.

    Thanks!
    Attached Thumbnails Attached Thumbnails qryDesign.png   qryErrorView.png  

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    two rows per employee
    sounds liek a malformed join to me
    what's the sql you are running
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Feb 2009
    Posts
    5
    The SQL Code is below. This is the entire code for the query. Thanks!

    SELECT tblSalaryAllocations.PayPeriod, tblContacts.[700#], [tblContacts]![LastName] & ", " & [tblContacts]![FirstName] AS Name, IIf([tblSalaryAllocations]![PayPeriod] Between [qryContactsPayPeriods]![WageStartPayPeriod] And [qryContactsPayPeriods]![WageEndPayPeriod],[qryContactsPayPeriods]![HourlyWage]*[tblSalaryAllocations]![Effort]*[tblSalaryAllocations]![Hours],"ERROR") AS WageCalculation
    FROM (tblContacts INNER JOIN tblSalaryAllocations ON tblContacts.[700#] = tblSalaryAllocations.[700#]) INNER JOIN qryContactsPayPeriods ON tblSalaryAllocations.[700#] = qryContactsPayPeriods.[700#]
    ORDER BY tblSalaryAllocations.PayPeriod;

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    What's the code for qryContactsPayPeriods?

  5. #5
    Join Date
    Feb 2009
    Posts
    5
    Code for the qryContactsPayPeriods is:

    SELECT tblContactsPayPeriods.[700#], tblContactsPayPeriods.PayType, tblContactsPayPeriods.Salary, tblContactsPayPeriods.HourlyWage, tblContactsPayPeriods.WageStartPayPeriod, IIf([tblContactsPayPeriods]![WageEndPayPeriod] Is Null,Date(),[tblContactsPayPeriods]![WageEndPayPeriod]) AS WageEndPayPeriod
    FROM tblContactsPayPeriods;

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    I'd suggest you make your joins only on the 700# FROM tblContacts

    FROM (tblContacts INNER JOIN tblSalaryAllocations ON tblContacts.[700#] = tblSalaryAllocations.[700#]) INNER JOIN qryContactsPayPeriods ON tblContacts.[700#] = qryContactsPayPeriods.[700#]
    ORDER BY tblSalaryAllocations.PayPeriod;

    my guess is that you have more than one record in tblsalarallocations.

    as said before if you are getting more rows than expected then its usually down to a wrongly formed join clause (or a wrongly formed where clause).

    Im surprised the query designer isn't suggesting the 700# as the default key. are you using RI / relationships atr all in this db?
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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