By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,363 Members | 3,622 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,363 IT Pros & Developers. It's quick & easy.

Having trouble getting an inline SQL Query to compare

P: 9
I've got to fix a system that was basically handed to me. As it currently functions we have four tables as such:

1) Project Hours (includes EmployeeID, ProjectID, Date, Hours)
2) Payrates (includes EmployeeID, Payrate) <-- NOT VISIBLE TO EMPLOYEES
3) EmployeeIDs (includes ID, First, Last, ExpenseCategory)
4) ExpenseCodes (includes ExpenseCategory, ExpenseCode)

Our queries are setup such that they can calculate how much we've spent on a project by joining all the tables and calculating Hours*Payrate.

Problem: Anytime someone gets a new pay, they need a new ID, and some way to tell, on the form where they enter hours, what name to put on which days worked. (Sometimes employees will wait until the end of the 2-week pay period, or longer, to enter their hours to a specific project.) This is rather cumbersome and annoying.

I've setup a table to keep track of different pay rates per person with the following attributes:

PayRateCodes:
UID - unique ID
EmployeeID
Rate - a dollar amount
BeginDate
EndDate

When we run the query to generate the table which summarizes the cost of the project out of the Project Hours table, I'd like it to look-up, for each record, the appropriate Rate in the PayRateCodes table by comparing the ProjectHours.Date to PayRateCodes.BeginDate and PayRateCodes.EndDate. (Complication, to make things easy on the accounting person, I've allowed EndDate to be blank. If it is so, I'd like the Date to be considered less than it.)

Pseudo-SQL Code:

SELECT ProjectHours.EmployeeID, ProjectHours.ProjectID, ProjectHours.Date, ProjectHours.Hours, (SELECT PayRateCodes.Rate WHERE PayRateCodes.EmployeeID = ProjectHours.EmployeeID AND PayRateCodes.BeginDate < ProjectHours.Date < ProjectHours.EndDate) as Rate1, Rate1*Hours as CostExpr FROM [Massive Inner Join Here based on table relationships] WHERE ProjectHours.ProjectID = "12".

I know that doesn't work. I'm hoping it explains what I want to do. If this can be done just within a query, that would be preferable, since adding a form to the mix might scare everyone here.
Jan 11 '07 #1
Share this Question
Share on Google+
13 Replies


NeoPa
Expert Mod 15k+
P: 31,494
2) Payrates (includes EmployeeID, Payrate) <-- NOT VISIBLE TO EMPLOYEES
Is this relevant to the question at all?
(Complication, to make things easy on the accounting person, I've allowed EndDate to be blank. If it is so, I'd like the Date to be considered less than it.)
I don't understand what you're trying to say here. Less than what? Null?
Ah, maybe I've sussed it - do you mean 'Date()' rather than 'the Date'?

I'll try to look at this and see what I can come up with later.
Jan 12 '07 #2

NeoPa
Expert Mod 15k+
P: 31,494
Angwe,
As you have access to your database, try creating the query you're thinking of in the design view window of a new query.
When you have it as close as possible to how you want it then post the SQL here (View / View SQL) along with the Querydef name. Then we don't have to do so much work on the simple stuff that Access can do very well for you.
BTW I wouldn't use a subquery here at this stage unless absolutely necessary. It seems it can more easiley done with a simple join.
Jan 12 '07 #3

P: 9
Angwe,
As you have access to your database, try creating the query you're thinking of in the design view window of a new query.
When you have it as close as possible to how you want it then post the SQL here (View / View SQL) along with the Querydef name. Then we don't have to do so much work on the simple stuff that Access can do very well for you.
BTW I wouldn't use a subquery here at this stage unless absolutely necessary. It seems it can more easiley done with a simple join.
A join would be the way to go, but the column "Date" in the table "ProjectHours" is a single date. The join would have to be based on that date being either:

1) within the range of BeginDate to EndDate in the PayRateCodes table (PayRateCodes.BeginDate < ProjectHours.Date < PayRateCodes.EndDate)

OR

2) at least greater than the BeginDate if the EndDate is blank. (PayRateCodes.BeginDate < ProjectHours.Date AND PayRateCodes.EndDate isBlank)

I want a join based on a comparison, not on any direct relationship between the Project Hours table and the PayRateCodes table, and I'm not sure how to do that with Access.
Jan 22 '07 #4

P: 9
Here's how far I've been able to get it to work.

TestQuery1 builds a new table TestTable by executing the following SQL:
Expand|Select|Wrap|Line Numbers
  1. SELECT [Project Hours].Project, EmployeeIDs1.[Expense Category], EmployeeIDs1.FirstName, [Project Hours].Date, [Project Hours].Hours, EmployeeIDs1.ID INTO TestTable
  2. FROM (EmployeeIDs1 INNER JOIN [Expense Codes1] ON EmployeeIDs1.[Expense Category] = [Expense Codes1].[Expense Category]) INNER JOIN [Project Hours] ON EmployeeIDs1.ID = [Project Hours].[Employee ID]
  3. WHERE ((([Project Hours].Project)="61"));
  4.  
That gives me a table with all the relevant info for any particular project from out of the Project Hours table.

As a second step, I want to go through each of these records and build a table that adds the appropriate pay rate as a column (and then, eventually, multiplies the hours by the payrate, but I can figure out how to do that, once I can get the payrate correct).

Here is TestQuery2:

Expand|Select|Wrap|Line Numbers
  1. SELECT TestTable.*, PayRateCodes.Rate
  2. FROM TestTable INNER JOIN PayRateCodes ON TestTable.ID = PayRateCodes.EmployeeID
  3. WHERE (((([PayRateCodes].[BeginDate]<[TestTable].[Date]<[PayRateCodes].[EndDate]) Or (([TestTable].[Date]>[PayRateCodes].[BeginDate]) And ([PayRateCodes].[EndDate]="")))));
  4.  
However, I have run this for one particular project that spans a payrate for the testing employee but it only assigns the first payrate, it does not assign the proper payrate for the dates after the pay rate change.

It seems like I can't make the JOIN conditional.

Hope this clears things up.

-R
Jan 22 '07 #5

P: 9
New version of the second query. I didn't realize that access will futz with the SQL you've written if you aren't careful.

Expand|Select|Wrap|Line Numbers
  1. SELECT TestTable.*, PayRateCodes.Rate
  2. FROM TestTable INNER JOIN PayRateCodes ON ((TestTable.ID=PayRateCodes.EmployeeID) AND ((PayRateCodes.BeginDate<TestTable.Date<PayRateCodes.EndDate) OR ((TestTable.Date>PayRateCodes.BeginDate) And (PayRateCodes.EndDate=""))));
  3.  
It still doesn't work, but now I think the Join is properly formatted.
Jan 22 '07 #6

P: 9
Attempting to follow MS's suggestions for conditional Joining (which I finally found in the damn help) I now have the following:

Expand|Select|Wrap|Line Numbers
  1. SELECT TestTable.*, PayRateCodes.Rate
  2. FROM TestTable INNER JOIN PayRateCodes
  3. ON TestTable.ID = PayRateCodes.EmployeeID AND 
  4. ON PayRateCodes.BeginDate < TestTable.Date < PayRateCodes.EndDate) OR
  5. ON TestTable.Date > PayRateCodes.BeginDate AND
  6. ON PayRateCodes.EndDate = "")];
  7.  
But where the first one just gives me the same rate for the person no matter what, this one won't rever run, because it gives me an error:

Syntax error, missing operator in expression ".


Then it highlights the following section of the SQL statement: tTable.ID =

If I attempt to logically parenthize it, it tells me my parentheses are screwed up.

-R
Jan 22 '07 #7

NeoPa
Expert Mod 15k+
P: 31,494
Angwe,
Sorry to take so long to respond (except that it has encouraged you to get so much done by yourself which is definitely good). I'm afraid I'm still short of proper time this week (to go through everything in detail) but I hope to catch up later somewhen.
In the mean time, you don't need to repeat the ON in the FROM clause. The ANDs & ORs are sufficient on their own.
You don't want a ']' at the end.
You should parenthesise () the ANDs & ORs where necessary. It is possible to do without but harder to read and prone to error.
Lastly, if you want [StartDate] <= [YourDate] <= [EndDate] (a more usual usage) you can use the form :
Expand|Select|Wrap|Line Numbers
  1. [YourDate] Between [StartDate] And [EndDate]
A slightly fixed version :
Expand|Select|Wrap|Line Numbers
  1. SELECT TestTable.*, PayRateCodes.Rate
  2. FROM TestTable INNER JOIN PayRateCodes ON 
  3. TestTable.ID = PayRateCodes.EmployeeID AND 
  4. PayRateCodes.BeginDate < TestTable.Date < PayRateCodes.EndDate) OR
  5. TestTable.Date > PayRateCodes.BeginDate AND
  6. PayRateCodes.EndDate = "");
Jan 23 '07 #8

P: 9
Hey NeoPa,

Thanks for all your suggestions. Using your code I got as far as still having the same problem, but with no more syntax errors.

I finally found the way to get what I want. Blank columns of the "Date/Time" data type have to be tested with IsNull(), not the blank string ("").

After doing that, the following code does what I want!

Expand|Select|Wrap|Line Numbers
  1. SELECT TestTable.*, PayRateCodes.Rate
  2. FROM TestTable INNER JOIN PayRateCodes ON 
  3. (TestTable.ID = PayRateCodes.EmployeeID AND 
  4. ((PayRateCodes.BeginDate <= TestTable.Date <= PayRateCodes.EndDate) OR
  5. ((TestTable.Date >= PayRateCodes.BeginDate) AND
  6. (IsNull(PayRateCodes.EndDate)))));
  7.  
It may be ridiculously over-parenthized, but it works, and should be understandable. (If you take something like this into gVim, it will happily highlight the close parens that matches the open parens the cursor is over, nice, eh?)

Thanks for your help!

-R
Jan 24 '07 #9

P: 9
I may have been hasty. Damn.

I now get two entries for dates after the change, one for dates before the rate change.

In other words, if TestEmployee put in hours on 9/9/2005, I only get one entry at the correct rate. If TestEmployee put in hours on 2/13/2006, I get two entries, one for the first rate, and one for the second rate. Any thoughts?
Jan 24 '07 #10

NeoPa
Expert Mod 15k+
P: 31,494
I'll have to look at this again somewhen.
Immediately I see things in the SQL which shouldn't be there - I would have expected errors to be reported.
Try looking in the FROM clause and remember the fields should all be connected with '='.
<; >; Between etc are all ok in a WHERE clause but not in the FROM.
Let me know if you make any progress and I will revisit when I have more time available.
Jan 25 '07 #11

NeoPa
Expert Mod 15k+
P: 31,494
I've no idea how that code ever worked (as you reported) :confused:
This should though and is the way to specify what you've asked for.
I handled a possible Null in P.BeginDate as well for good measure.
Expand|Select|Wrap|Line Numbers
  1. SELECT T.*, P.Rate
  2. FROM TestTable AS T INNER JOIN PayRateCodes AS P
  3.   ON T.ID = P.EmployeeID
  4. WHERE T.Date Between Nz(P.BeginDate,#1/1/1900#)
  5.                  And Nz(P.EndDate,#12/31/9999#);
Jan 25 '07 #12

P: 9
I've no idea how that code ever worked (as you reported) :confused:
This should though and is the way to specify what you've asked for.
I handled a possible Null in P.BeginDate as well for good measure.
Expand|Select|Wrap|Line Numbers
  1. SELECT T.*, P.Rate
  2. FROM TestTable AS T INNER JOIN PayRateCodes AS P
  3.   ON T.ID = P.EmployeeID
  4. WHERE T.Date Between Nz(P.BeginDate,#1/1/1900#)
  5.                  And Nz(P.EndDate,#12/31/9999#);
So, the JOIN should never be conditional? That seems odd. I guess I'm just confused by the fact that the WHERE works when it wouldn't before.

Instead of creating the intermediary test table, I tried just adding the JOIN and WHERE qualifications before, and it just wouldn't run.

This does, indeed, work as I expected, and now I have to go find out if I can add it to the orignial query or if I need to keep the intermediate step.
Feb 1 '07 #13

NeoPa
Expert Mod 15k+
P: 31,494
So, the JOIN should never be conditional? That seems odd. I guess I'm just confused by the fact that the WHERE works when it wouldn't before.
If, by conditional, you mean a more flexible condition than a simple match, then yes - it can never be conditional.
That sort of thing can be done in a WHERE clause which, by the way, is completely separate from the FROM clause wherein you may find the JOIN statements.
Feb 1 '07 #14

Post your reply

Sign in to post your reply or Sign up for a free account.