Having trouble getting an inline SQL Query to compare

[angwe23]

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.

[NeoPa]

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.

[NeoPa]

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.

[angwe23]

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.

[angwe23]

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

TestQuery1 builds a new table TestTable by executing the following SQL:

SELECT [Project Hours].Project, EmployeeIDs1.[Expense Category], EmployeeIDs1.FirstName, [Project Hours].Date, [Project Hours].Hours, EmployeeIDs1.ID INTO TestTable
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]
WHERE ((([Project Hours].Project)="61"));
 

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:

SELECT TestTable.*, PayRateCodes.Rate
FROM TestTable INNER JOIN PayRateCodes ON TestTable.ID = PayRateCodes.EmployeeID
WHERE (((([PayRateCodes].[BeginDate]<[TestTable].[Date]<[PayRateCodes].[EndDate]) Or (([TestTable].[Date]>[PayRateCodes].[BeginDate]) And ([PayRateCodes].[EndDate]="")))));
 

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

[angwe23]

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.

SELECT TestTable.*, PayRateCodes.Rate
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=""))));
 

It still doesn't work, but now I think the Join is properly formatted.

[angwe23]

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

SELECT TestTable.*, PayRateCodes.Rate
FROM TestTable INNER JOIN PayRateCodes
ON TestTable.ID = PayRateCodes.EmployeeID AND 
ON PayRateCodes.BeginDate < TestTable.Date < PayRateCodes.EndDate) OR
ON TestTable.Date > PayRateCodes.BeginDate AND
ON PayRateCodes.EndDate = "")];
 

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

[NeoPa]

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 :

[YourDate] Between [StartDate] And [EndDate]

A slightly fixed version :

SELECT TestTable.*, PayRateCodes.Rate
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 = "");

[angwe23]

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!

SELECT TestTable.*, PayRateCodes.Rate
FROM TestTable INNER JOIN PayRateCodes ON 
(TestTable.ID = PayRateCodes.EmployeeID AND 
((PayRateCodes.BeginDate <= TestTable.Date <= PayRateCodes.EndDate) OR
((TestTable.Date >= PayRateCodes.BeginDate) AND
(IsNull(PayRateCodes.EndDate)))));
 

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

[angwe23]

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?

[NeoPa]

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.

[NeoPa]

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.

SELECT T.*, P.Rate
FROM TestTable AS T INNER JOIN PayRateCodes AS P
  ON T.ID = P.EmployeeID
WHERE T.Date Between Nz(P.BeginDate,#1/1/1900#)
                 And Nz(P.EndDate,#12/31/9999#);

[angwe23]

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.

SELECT T.*, P.Rate
FROM TestTable AS T INNER JOIN PayRateCodes AS P
  ON T.ID = P.EmployeeID
WHERE T.Date Between Nz(P.BeginDate,#1/1/1900#)
                 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.

[NeoPa]

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.