dlookup help

[Guest]

Hello all.

I have two tables with the following fields:

Table1: Rate_Table
Field1: Resource ID
Field2: Rate
Field3: Date_Start
Field4: Date_End

Table2: Data_DUMP
Field1: Resource ID
Field2: Start Date
Field3: End Date
Field4: Hours

I'm building a query that I need to pull all fields from Data_DUMP and only
the rate from Rate_Table. However, in Rate_Table, I will have a Resource ID
listed more than once with different start/end dates and different rates.
Essentially, a Resource may have their rates change from one year to another.

For example, this would be the data in Rate_table:

Resource ID Date_Start Date_End Rate
JSmith 01/01/2006 12/31/2006 20
JSmith 01/01/2007 12/31/2007 30

Here is an example of the Data_DUMP:
Resrouce ID Start Date End Date Hours
JSmith 02/14/06 02/21/06 10
JSmith 02/22/06 02/28/06 8
....
JSmith 04/07/07 04/14/07 9
LJones 04/07/07 04/14/07 10


When I build my query, I need to make sure I pull the correct rate, based on
the start and end dates of the Data_DUMP table. So, in order for me to pull
the correct rate, three comparisons need to happen:

1. Data_Dump.Resource ID = Rate_Table.Resource ID
2. Data_Dump.[Start Date] >= Rate_table.Date_Start
3. Data_Dump.[End Date] <= Rate_Table.Date_End

When the two tables are compared, the correct rate should be pulled into the
query based on the above criteria. It should look like the following:

Resrouce ID Start Date End Date Hours Rate
JSmith 02/14/06 02/21/06 10 20
JSmith 02/22/06 02/28/06 8 20
JSmith 04/07/07 04/14/07 9 30
LJones 04/07/07 04/14/07 10 <null>


I included the LJones example to show that I need to know if a record
doesn't have a rate included. If it comes back as blank or null, then I know
I need to update the Rate_Table.

Cacn someone help me build the correct lookup expression? Is a lookup
expression not the correct approach? I've tried dLookUp() but I can't seem
to get it to pull the correct rate correctly. I hope my example is clear and
I due appreciate all your help.

Thanks in advance!
The Dude

 

[Chris2]

Hello all.

I have two tables with the following fields:

Table1: Rate_Table
Field1: Resource ID
Field2: Rate
Field3: Date_Start
Field4: Date_End

Table2: Data_DUMP
Field1: Resource ID
Field2: Start Date
Field3: End Date
Field4: Hours

I'm building a query that I need to pull all fields from Data_DUMP and only
the rate from Rate_Table. However, in Rate_Table, I will have a Resource ID
listed more than once with different start/end dates and different rates.
Essentially, a Resource may have their rates change from one year to another.

For example, this would be the data in Rate_table:

Resource ID Date_Start Date_End Rate
JSmith 01/01/2006 12/31/2006 20
JSmith 01/01/2007 12/31/2007 30

Here is an example of the Data_DUMP:
Resrouce ID Start Date End Date Hours
JSmith 02/14/06 02/21/06 10
JSmith 02/22/06 02/28/06 8
...
JSmith 04/07/07 04/14/07 9
LJones 04/07/07 04/14/07 10


When I build my query, I need to make sure I pull the correct rate, based on
the start and end dates of the Data_DUMP table. So, in order for me to pull
the correct rate, three comparisons need to happen:

1. Data_Dump.Resource ID = Rate_Table.Resource ID
2. Data_Dump.[Start Date] >= Rate_table.Date_Start
3. Data_Dump.[End Date] <= Rate_Table.Date_End

When the two tables are compared, the correct rate should be pulled into the
query based on the above criteria. It should look like the following:

Resrouce ID Start Date End Date Hours Rate
JSmith 02/14/06 02/21/06 10 20
JSmith 02/22/06 02/28/06 8 20
JSmith 04/07/07 04/14/07 9 30
LJones 04/07/07 04/14/07 10

I included the LJones example to show that I need to know if a record
doesn't have a rate included. If it comes back as blank or null, then I know
I need to update the Rate_Table.

Cacn someone help me build the correct lookup expression? Is a lookup
expression not the correct approach? I've tried dLookUp() but I can't seem
to get it to pull the correct rate correctly. I hope my example is clear and
I due appreciate all your help.

Thanks in advance!
The Dude

TheDude,

Create Table Queries:

CREATE TABLE Rate_Table
([Resource ID] TEXT(12)
,Rate CURRENCY
,Date_Start DATETIME
,Date_End DATETIME
,CONSTRAINT pk_Rate_Table
PRIMARY KEY ([Resource ID]
,Rate
,Date_Start
,Date_End)
)

CREATE TABLE Data_DUMP
([Resource ID] TEXT(12)
,[Start Date] DATETIME
,[End Date] DATETIME
,Hours INTEGER
,CONSTRAINT pk_Data_DUMP
PRIMARY KEY ([Resource ID]
,[Start Date]
,[End Date]
,Hours)
)

Technically, [Resource ID] in Data_DUMP should be Foreign Keyed over
to Rate_Table, or the columns in both tables should be Foreign Keyed
out to whatever parent table contains the origin of that column, but I
have no idea what that is, so I'm skipping it.


Sample Data:

As given above


Query:

SELECT D1.[Resource ID]
,D1.[Start Date]
,D1.[End Date]
,D1.Hours
,R1.Rate
FROM Rate_Table AS R1
RIGHT JOIN
Data_DUMP AS D1
ON R1.[Resource ID] = D1.[Resource ID]
WHERE (Nz(R1.Date_Start, #01/01/1900#) <= D1.[Start Date]
AND Nz(R1.Date_End, #01/01/2100#) >= D1.[End Date])


Results:

Resrouce ID Start Date End Date Hours Rate
JSmith 02/14/06 02/21/06 10 20
JSmith 02/22/06 02/28/06 8 20
JSmith 04/07/07 04/14/07 9 30
LJones 04/07/07 04/14/07 10


This query appears to generate the results in question.

As always, further design/testing may be necessary.


Sincerely,

Chris O.

 

[Guest]

Chris, Thank you very much for you response. Your example worked perfectly
but I realized that I screwed something up in my question.

What I really need to know if is JSmith didn't have a rate for the 2007
Data_Dump entry. So for example:

Rate_Table:
Resource ID Date_Start Date_End Rate
JSmith 01/01/2007 12/31/2007 20

Data_Dump:
Resource ID Start Date End Date Hours
JSmith 02/14/06 02/21/06 10
JSmith 02/22/06 02/28/06 8
....
JSmith 04/07/07 04/14/07 9

The Rate_Table does not have a rate listed for JSmith for his 2007 entry.
Therefore the query should return:

Resource ID Start Date End Date Hours
Rate
JSmith 02/14/06 02/21/06 10
20
JSmith 02/22/06 02/28/06 8
20
JSmith 04/07/07 04/14/07 9
<null>

Right now, the query does not pull over the last entry. Since it does not
meet the criteria, the records are left out. I need that record to come over
now matter what and show a blank field under rate.

I hope that makes sense and again, thank you so much for your assistance!

Regards,
Arthur aka TheDude

Hello all.

I have two tables with the following fields:

Table1: Rate_Table
Field1: Resource ID
Field2: Rate
Field3: Date_Start
Field4: Date_End

Table2: Data_DUMP
Field1: Resource ID
Field2: Start Date
Field3: End Date
Field4: Hours

I'm building a query that I need to pull all fields from Data_DUMP and only
the rate from Rate_Table. However, in Rate_Table, I will have a Resource ID
listed more than once with different start/end dates and different rates.
Essentially, a Resource may have their rates change from one year to another.

For example, this would be the data in Rate_table:

Resource ID Date_Start Date_End Rate
JSmith 01/01/2006 12/31/2006 20
JSmith 01/01/2007 12/31/2007 30

Here is an example of the Data_DUMP:
Resrouce ID Start Date End Date Hours
JSmith 02/14/06 02/21/06 10
JSmith 02/22/06 02/28/06 8
...
JSmith 04/07/07 04/14/07 9
LJones 04/07/07 04/14/07 10


When I build my query, I need to make sure I pull the correct rate, based on
the start and end dates of the Data_DUMP table. So, in order for me to pull
the correct rate, three comparisons need to happen:

1. Data_Dump.Resource ID = Rate_Table.Resource ID
2. Data_Dump.[Start Date] >= Rate_table.Date_Start
3. Data_Dump.[End Date] <= Rate_Table.Date_End

When the two tables are compared, the correct rate should be pulled into the
query based on the above criteria. It should look like the following:

Resrouce ID Start Date End Date Hours Rate
JSmith 02/14/06 02/21/06 10 20
JSmith 02/22/06 02/28/06 8 20
JSmith 04/07/07 04/14/07 9 30
LJones 04/07/07 04/14/07 10

I included the LJones example to show that I need to know if a record
doesn't have a rate included. If it comes back as blank or null, then I know
I need to update the Rate_Table.

Cacn someone help me build the correct lookup expression? Is a lookup
expression not the correct approach? I've tried dLookUp() but I can't seem
to get it to pull the correct rate correctly. I hope my example is clear and
I due appreciate all your help.

Thanks in advance!
The Dude

TheDude,

Create Table Queries:

CREATE TABLE Rate_Table
([Resource ID] TEXT(12)
,Rate CURRENCY
,Date_Start DATETIME
,Date_End DATETIME
,CONSTRAINT pk_Rate_Table
PRIMARY KEY ([Resource ID]
,Rate
,Date_Start
,Date_End)
)

CREATE TABLE Data_DUMP
([Resource ID] TEXT(12)
,[Start Date] DATETIME
,[End Date] DATETIME
,Hours INTEGER
,CONSTRAINT pk_Data_DUMP
PRIMARY KEY ([Resource ID]
,[Start Date]
,[End Date]
,Hours)
)

Technically, [Resource ID] in Data_DUMP should be Foreign Keyed over
to Rate_Table, or the columns in both tables should be Foreign Keyed
out to whatever parent table contains the origin of that column, but I
have no idea what that is, so I'm skipping it.


Sample Data:

As given above


Query:

SELECT D1.[Resource ID]
,D1.[Start Date]
,D1.[End Date]
,D1.Hours
,R1.Rate
FROM Rate_Table AS R1
RIGHT JOIN
Data_DUMP AS D1
ON R1.[Resource ID] = D1.[Resource ID]
WHERE (Nz(R1.Date_Start, #01/01/1900#) <= D1.[Start Date]
AND Nz(R1.Date_End, #01/01/2100#) >= D1.[End Date])


Results:

Resrouce ID Start Date End Date Hours Rate
JSmith 02/14/06 02/21/06 10 20
JSmith 02/22/06 02/28/06 8 20
JSmith 04/07/07 04/14/07 9 30
LJones 04/07/07 04/14/07 10


This query appears to generate the results in question.

As always, further design/testing may be necessary.


Sincerely,

Chris O.