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...
angwe23
Last Activity: Feb 1 '07, 08:10 PM
Joined: Jan 3 '07
Location:
-
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?Leave a comment:
-
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!
...Code:SELECT TestTable.*, PayRateCodes.Rate
Leave a comment:
-
Attempting to follow MS's suggestions for conditional Joining (which I finally found in the damn help) I now have the following:
...Code: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
Leave a comment:
-
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.
...Code: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=""))));
Leave a comment:
-
Here's how far I've been able to get it to work.
TestQuery1 builds a new table TestTable by executing the following SQL:
...Code: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
Leave a comment:
-
A join would be the way to go, but the column "Date" in the table "ProjectHou rs" 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.B eginDate < ProjectHours.Da te < PayRateCodes.En dDate)
OR
2) at least greater than the BeginDate if the EndDate is blank. (PayRateCodes.B eginDate...Leave a comment:
-
Having trouble getting an inline SQL Query to compare
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... -
I have a similar situation, and have done, I think, exactly what you suggest, but it doesn't seem to want to work. The source data for the combo box in the main form is:
SELECT [Current Employees].LastName, [Current Employees].FirstName, [Current Employees].ID FROM EmployeeIDs INNER JOIN [Current Employees] ON EmployeeIDs.ID=[Current Employees].ID ORDER BY [Current Employees].LastName;
The code, which is just what you...Leave a comment:
No activity results to display
Leave a comment: