Lookup in Query

[Guest]

Table1 stores issues and a Rec_Date and Clo_Date.
Table2 has stores fiscal weeks and the dates Satruday-Friday associated with
the fiscal week for the entire year (example below):
FW | Sat | Sun | Mon | Tues |
Wed | Thur | Fri
200601 | 2/4/2006 | 2/5/2006 | 2/6/2006 | 2/7/2006 | 2/8/2006 |
2/9/2006 | 2/10/2006

I want to create a query that will find the Rec_Date and Clo_Date and then
return the fiscal week. What formula can I use to get this data?

 

[Tom Ellison]

Dear JB:

Your details are skimpy. I'll give you a best guess:

SELECT Table1.Rec_Date, Table1.Clo_Date, Table2.FW
FROM Table1, Table2
WHERE Table1.Rec_Date BETWEEN Table2.Sat AND Table2.Fri

Is this anywhere close?

Tom Ellison

 

[Guest]

Sorry to be so vague.
Here is my SQL Statement

UPDATE table1
SET table1.Rec_FW = ( SELECT table2.Fiscal_Week
FROM table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday)
WHERE EXISTS
( SELECT table2.Fiscal_Week
FROM table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday);

This is returning the right amount of Records when I run it, but it is not
actually updating that field. They are just left blank.

 

[Tom Ellison]

Dear JB:

Let's make it simpler. Test just a SELECT query:

SELECT table1.Rec_FW, table2.Fiscal_Week
FROM table1, table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday
AND EXISTS
(SELECT table2.Fiscal_Week
FROM table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday)

Next, does this do anything different:

SELECT table1.Rec_FW, table2.Fiscal_Week
FROM table1, table2
WHERE table1.Rec_Date BETWEEN table2.Saturday AND table2.Friday

If the specific row does not exist, I don't think the latter query would
return that anyway.

I hope this looks like progress. Please let me know what you find.

Tom Ellison

 

[Guest]

Tom,

This is pulling the right record, but I need the query to actually update
Rec_Date to the fiscal week.
Does that make sense?

 

[Tom Ellison]

Dear JB:

Sorry, no, it doesn't make sense to me right now. How can you update a
date/time column (Rec_Date) to be a week?

Tom Ellison

 

[Guest]

UPDATE table1
SET table1.Rec_FW = ( SELECT table2.Fiscal_Week

I don't want the Rec_Date field to be updated. I want the Query to use the
Rec_Date field and compare to the other table and then put the fiscal week
into a field called Rec_FW. I thought the "SET" line in my first query would
have done that, but it's not actually changing the value to the fiscal week.

 

[Tom Ellison]

Dear JB:

It is an important rule in database design not to record a value like this,
but to derive it whenever needed. I now see that, given any date, you could
obtain the FW for that date from Table2. I have done this in the query in
my first post. I wrote:

SELECT Table1.Rec_Date, Table1.Clo_Date, Table2.FW
FROM Table1, Table2
WHERE Table1.Rec_Date BETWEEN Table2.Sat AND Table2.Fri

You can see the correct FW in this query. You do not need more.

The problem with recording the derived FW is that, if a correction is made
to the Rec_Date, then the recorded FW may be wrong. In any instance in
which Rec_Date might be changed, you would need to code for the change of
the FW. What would happen if someone just made a change to Rec_Date by
opening the table? There would be no opportunity to automatically change
the recorded FW.

If, instead, you derive all derivable values each and every time you need
them, you will eliminate all such problems. That is why this is a rather
firm rule of database design. I strongly recommend you not store the FW.

Tom Ellison

 

[Guest]

Thank you for your response and clairification. I appreciate your help.