1. Registered User
Join Date
Feb 2009
Posts
5

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!

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
two rows per employee
sounds liek a malformed join to me
what's the sql you are running

3. Registered User
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. Moderator
Join Date
Mar 2009
Posts
5,442
What's the code for qryContactsPayPeriods?

5. Registered User
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;

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
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?

#### Posting Permissions

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