Q
quartz
I have the following query which runs okay, but it doesn't always select the
correct match. There is sometimes more than one possible match for each item
in the LEVEL table. I need this query to ALWAYS select the first item in the
"LEVEL" table:
UPDATE [MERGE], [LEVEL]
SET [MERGE].[RPT_CODE] = [LEVEL].[RPT_CODE] WHERE
([MERGE].[FUND] >= [LEVEL].[FUND_LO] AND
[MERGE].[FUND] <= [LEVEL].[FUND_HI]) AND
([MERGE].[ACCOUNT] >= [LEVEL].[ACCT_LO] AND
([MERGE].[ACCOUNT] <= [LEVEL].[ACCT_HI]) AND
([MERGE].[FERC] >= [LEVEL].[FERC_LO] AND
[MERGE].[FERC] <= [LEVEL].[FERC_HI]);
Does Access update the table from the top down, if so, could I not reverse
sort the [LEVEL] table (perhaps using an index) so that the last item to be
updated is the one I need (if so, how the heck do I get it to do this?) ?
I need a creative solution here, I tried updating by looping through a
recordset and it took over 1 hour. This is not acceptable. The above method
takes about 5-6 minutes, but alas, is not 100% correct due to the overlapping
ranges in "LEVEL". How can I tweak this SQL to make it update each field only
once? Could I add, if is null somehow (if so, how?) ?
Think outside the box. Some people told me it was not possible to place two
tables in the "UPDATE" clause, but it works. Please help if you can. Thanks
much in advance!
correct match. There is sometimes more than one possible match for each item
in the LEVEL table. I need this query to ALWAYS select the first item in the
"LEVEL" table:
UPDATE [MERGE], [LEVEL]
SET [MERGE].[RPT_CODE] = [LEVEL].[RPT_CODE] WHERE
([MERGE].[FUND] >= [LEVEL].[FUND_LO] AND
[MERGE].[FUND] <= [LEVEL].[FUND_HI]) AND
([MERGE].[ACCOUNT] >= [LEVEL].[ACCT_LO] AND
([MERGE].[ACCOUNT] <= [LEVEL].[ACCT_HI]) AND
([MERGE].[FERC] >= [LEVEL].[FERC_LO] AND
[MERGE].[FERC] <= [LEVEL].[FERC_HI]);
Does Access update the table from the top down, if so, could I not reverse
sort the [LEVEL] table (perhaps using an index) so that the last item to be
updated is the one I need (if so, how the heck do I get it to do this?) ?
I need a creative solution here, I tried updating by looping through a
recordset and it took over 1 hour. This is not acceptable. The above method
takes about 5-6 minutes, but alas, is not 100% correct due to the overlapping
ranges in "LEVEL". How can I tweak this SQL to make it update each field only
once? Could I add, if is null somehow (if so, how?) ?
Think outside the box. Some people told me it was not possible to place two
tables in the "UPDATE" clause, but it works. Please help if you can. Thanks
much in advance!