Re: Lookup in Query

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

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


"jbgarrett1" <jbgarrett1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:44AE3824-17EA-4EE0-957A-589E791425FB@xxxxxxxxxxxxxxxx
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" wrote:

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


"jbgarrett1" <jbgarrett1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B2434ACB-0887-420F-9F0B-C97C94F9B843@xxxxxxxxxxxxxxxx
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" wrote:

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


"jbgarrett1" <jbgarrett1@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C6B14F8C-F2BF-439A-ABDB-5C5468334318@xxxxxxxxxxxxxxxx
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.








.

Relevant Pages