My table 'Reports' is like so
record_number card_number amount Check
1 1 2
8 2 3
9 2 3
And the other 'MSA':
record_number card_number amount foundIt
a 1 2
b 2 3
c 4 5
d 2 3
I can loop through the first table records. I am finding matches in the second table. When found I mark the second table foundIt to Yes. My trouble, notice record_number 8 and 9 match b and d. Well when running the initial loop the Reports table skips 9 (because it matches 8?) therefore skips d.
Here is my code:
<html>
<head>
<!--- Get record count from Reports to loop through--->
<cfquery datasource="LinkPoint" name="getcount">
select record_number
from Reports
</cfquery>
<!--- Set loop and start--->
<cfloop index="Idx" From=1 TO=#getcount.RecordCount#>
<!---Get a row from the Reports table--->
<cfquery name="getReports" datasource="LinkPoint">
SELECT *
FROM Reports
WHERE record_number=#Idx#
</cfquery>
<cfoutput query="getReports">
<cfset card=#card_number#>
<cfset money=#amount#>
<cfset row=#record_number#>
<!--- Now we have a row lets find it in MSA--->
<cfquery name="compare" datasource="LinkPoint">
SELECT *
FROM MSA
Where card_number='#getReports.card_number#' AND amount='#getReports.amount#'
</cfquery>
</cfoutput>
<!--- If a record was returned let's mark it as found--->
<cfoutput query="compare" maxrows="1">
<cfif #compare.RecordCount# GT 0 AND foundIt EQ "">
<cfquery name="FoundIt" datasource="LinkPoint">
update MSA
set foundIt = #getReports.record_number#
where record_number=#compare.record_number#
</cfquery>
<cfquery name="foundReport" dataSource="LinkPoint">
update Reports
set Check = 'Yes'
WHERE record_number=#Idx#
</cfquery>
<cfelse>
</cfif>
</cfoutput>
</cfloop>
<head>
<body>
<cfoutput query="getcount" maxRows="1">
<p>Rows To Loop Through: #getcount.recordCount#
</cfoutput>
<!---
<cfquery name="found_count" datasource="LinkPoint">
SELECT *
FROM MSA
WHERE foundIt="Match"
</cfquery>
<cfoutput query="found_count" maxrows="1">
<p>Matches Found: #found_count.RecordCount#
</cfoutput>
--->
<cfquery name="results" datasource="LinkPoint">
SELECT *
FROM MSA
</cfquery>
<ul>
<cfoutput query="results">
<li>#record_number# - #foundIt#
</cfoutput>
</ul>
<cfquery name="ReportsMatch" datasource="LinkPoint">
SELECT *
FROM Reports
</cfquery>
<ul>
<cfoutput query="ReportsMatch">
<li>#record_number# - #Check#
</cfoutput>
</ul>
</body>
</html>
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic
record_number card_number amount Check
1 1 2
8 2 3
9 2 3
And the other 'MSA':
record_number card_number amount foundIt
a 1 2
b 2 3
c 4 5
d 2 3
I can loop through the first table records. I am finding matches in the second table. When found I mark the second table foundIt to Yes. My trouble, notice record_number 8 and 9 match b and d. Well when running the initial loop the Reports table skips 9 (because it matches 8?) therefore skips d.
Here is my code:
<html>
<head>
<!--- Get record count from Reports to loop through--->
<cfquery datasource="LinkPoint" name="getcount">
select record_number
from Reports
</cfquery>
<!--- Set loop and start--->
<cfloop index="Idx" From=1 TO=#getcount.RecordCount#>
<!---Get a row from the Reports table--->
<cfquery name="getReports" datasource="LinkPoint">
SELECT *
FROM Reports
WHERE record_number=#Idx#
</cfquery>
<cfoutput query="getReports">
<cfset card=#card_number#>
<cfset money=#amount#>
<cfset row=#record_number#>
<!--- Now we have a row lets find it in MSA--->
<cfquery name="compare" datasource="LinkPoint">
SELECT *
FROM MSA
Where card_number='#getReports.card_number#' AND amount='#getReports.amount#'
</cfquery>
</cfoutput>
<!--- If a record was returned let's mark it as found--->
<cfoutput query="compare" maxrows="1">
<cfif #compare.RecordCount# GT 0 AND foundIt EQ "">
<cfquery name="FoundIt" datasource="LinkPoint">
update MSA
set foundIt = #getReports.record_number#
where record_number=#compare.record_number#
</cfquery>
<cfquery name="foundReport" dataSource="LinkPoint">
update Reports
set Check = 'Yes'
WHERE record_number=#Idx#
</cfquery>
<cfelse>
</cfif>
</cfoutput>
</cfloop>
<head>
<body>
<cfoutput query="getcount" maxRows="1">
<p>Rows To Loop Through: #getcount.recordCount#
</cfoutput>
<!---
<cfquery name="found_count" datasource="LinkPoint">
SELECT *
FROM MSA
WHERE foundIt="Match"
</cfquery>
<cfoutput query="found_count" maxrows="1">
<p>Matches Found: #found_count.RecordCount#
</cfoutput>
--->
<cfquery name="results" datasource="LinkPoint">
SELECT *
FROM MSA
</cfquery>
<ul>
<cfoutput query="results">
<li>#record_number# - #foundIt#
</cfoutput>
</ul>
<cfquery name="ReportsMatch" datasource="LinkPoint">
SELECT *
FROM Reports
</cfquery>
<ul>
<cfoutput query="ReportsMatch">
<li>#record_number# - #Check#
</cfoutput>
</ul>
</body>
</html>
DeZiner
Never be afraid to try something new.
Remember that amateurs built the Ark.
Professionals built the Titanic