Doozy - help please

[Guest]

I need to update table [MERGE] from table [REPORT_CODE]. The following fails,
but I don't know why. Please note that some of the hi/lo ranges overlap, so I
need the query to update the first one it finds.

What is the best way to accomplish what I'm trying to do?
Would it be better to loop thru a recordset?
Can someone please show me how to improve this SQL?

UPDATE [MERGE]
SET [MERGE].[RPT_CODE] = [REPORT_CODE].[RPT_CODE]
WHERE
(([MERGE].[FUND] >= [REPORT_CODE].[FUND_LO] AND
[MERGE].[FUND] <= [REPORT_CODE].[FUND_HI]) AND
([MERGE].[ACCOUNT] >= [REPORT_CODE].[ACCT_LO] AND
[MERGE].[ACCOUNT] <= [REPORT_CODE].[ACCT_HI]) AND
([MERGE].[FERC] >= [REPORT_CODE].[FERC_LO] AND
[MERGE].[FERC] <= [REPORT_CODE].[FERC_HI]));

Thanks in advance for your kind and gentle assistance.

 

[Michel Walsh]

Hi

Unfortunately, we need an inner join (not a where clause) to have an
updateable query:


UPDATE table1 INNER JOIN table2 ON table1.f1=table2.f2
SET table1.f2=table2.g2


but the inner join must also be an equi join (one that implies a = as
comparison). Your is not.

You will have to use a recordset.



Hoping it may help,
Vanderghast, Access MVP

 

[Guest]

Thanks Michel.

What is the best way to obtain a full table recordset with Access?

ADO, I would guess. Could you please post example code on the fastest and
most efficient method how to obtain an entire table in an ADO recordset?

Thanks again.

Hi

Unfortunately, we need an inner join (not a where clause) to have an
updateable query:


UPDATE table1 INNER JOIN table2 ON table1.f1=table2.f2
SET table1.f2=table2.g2


but the inner join must also be an equi join (one that implies a = as
comparison). Your is not.

You will have to use a recordset.



Hoping it may help,
Vanderghast, Access MVP

I need to update table [MERGE] from table [REPORT_CODE]. The following
fails,
but I don't know why. Please note that some of the hi/lo ranges overlap,
so I
need the query to update the first one it finds.

What is the best way to accomplish what I'm trying to do?
Would it be better to loop thru a recordset?
Can someone please show me how to improve this SQL?

UPDATE [MERGE]
SET [MERGE].[RPT_CODE] = [REPORT_CODE].[RPT_CODE]
WHERE
(([MERGE].[FUND] >= [REPORT_CODE].[FUND_LO] AND
[MERGE].[FUND] <= [REPORT_CODE].[FUND_HI]) AND
([MERGE].[ACCOUNT] >= [REPORT_CODE].[ACCT_LO] AND
[MERGE].[ACCOUNT] <= [REPORT_CODE].[ACCT_HI]) AND
([MERGE].[FERC] >= [REPORT_CODE].[FERC_LO] AND
[MERGE].[FERC] <= [REPORT_CODE].[FERC_HI]));

Thanks in advance for your kind and gentle assistance.

 

[Michel Walsh]

Hi,

Forward only (read only too, if possible, but in this case, it seems we
need the ability to write, so ... ):


Dim rst As ADODB.Recordset
rst.Open "Tablename", CurrentProject.Connection, adOpenForwardOnly,
adLockOptimistic, adCmdTable


Note that if you supply the SELECT statement, with a WHERE clause to
eliminate most of the records, and to specify only the columns you really
need, use adCmdText rather than adCmdTable.



Hoping it may help,
Vanderghast, Access MVP

Thanks Michel.

What is the best way to obtain a full table recordset with Access?

ADO, I would guess. Could you please post example code on the fastest and
most efficient method how to obtain an entire table in an ADO recordset?

Thanks again.

Hi

Unfortunately, we need an inner join (not a where clause) to have an
updateable query:


UPDATE table1 INNER JOIN table2 ON table1.f1=table2.f2
SET table1.f2=table2.g2


but the inner join must also be an equi join (one that implies a = as
comparison). Your is not.

You will have to use a recordset.



Hoping it may help,
Vanderghast, Access MVP

I need to update table [MERGE] from table [REPORT_CODE]. The following
fails,
but I don't know why. Please note that some of the hi/lo ranges
overlap,
so I
need the query to update the first one it finds.

What is the best way to accomplish what I'm trying to do?
Would it be better to loop thru a recordset?
Can someone please show me how to improve this SQL?

UPDATE [MERGE]
SET [MERGE].[RPT_CODE] = [REPORT_CODE].[RPT_CODE]
WHERE
(([MERGE].[FUND] >= [REPORT_CODE].[FUND_LO] AND
[MERGE].[FUND] <= [REPORT_CODE].[FUND_HI]) AND
([MERGE].[ACCOUNT] >= [REPORT_CODE].[ACCT_LO] AND
[MERGE].[ACCOUNT] <= [REPORT_CODE].[ACCT_HI]) AND
([MERGE].[FERC] >= [REPORT_CODE].[FERC_LO] AND
[MERGE].[FERC] <= [REPORT_CODE].[FERC_HI]));

Thanks in advance for your kind and gentle assistance.

 

[PC Datasheet]

Hi

Unfortunately, we need an inner join (not a where clause) to have an
updateable query:


UPDATE table1 INNER JOIN table2 ON table1.f1=table2.f2
SET table1.f2=table2.g2


but the inner join must also be an equi join (one that implies a = as
comparison). Your is not.

You will have to use a recordset.



Hoping it may help,
Vanderghast, Access MVP

I need to update table [MERGE] from table [REPORT_CODE]. The following
fails,
but I don't know why. Please note that some of the hi/lo ranges overlap,
so I
need the query to update the first one it finds.

What is the best way to accomplish what I'm trying to do?
Would it be better to loop thru a recordset?
Can someone please show me how to improve this SQL?

UPDATE [MERGE]
SET [MERGE].[RPT_CODE] = [REPORT_CODE].[RPT_CODE]
WHERE
(([MERGE].[FUND] >= [REPORT_CODE].[FUND_LO] AND
[MERGE].[FUND] <= [REPORT_CODE].[FUND_HI]) AND
([MERGE].[ACCOUNT] >= [REPORT_CODE].[ACCT_LO] AND
[MERGE].[ACCOUNT] <= [REPORT_CODE].[ACCT_HI]) AND
([MERGE].[FERC] >= [REPORT_CODE].[FERC_LO] AND
[MERGE].[FERC] <= [REPORT_CODE].[FERC_HI]));

Thanks in advance for your kind and gentle assistance.