parameters, update results

  • Thread starter Thread starter liz b
  • Start date Start date
L

liz b

I want to match two sets of fields from two tables, then
update the result set (verifying the matches, transfering
info from one tab to another, or rejecting the match so
it never occurs again). I want to avoid visual basic,
since I don't know it.

As I understand it, if I use a stored proc I can't update
the result set. If I use a view I can't pass
parameters.

The following sp statement works well, and it matches a
text string from either table's name field with the
other, and the totals within a range:

(@start_string_number smallint,
@length_string_number smallint,
@dollar_difference numeric(10))
AS
SELECT @start_string_number AS 'start at letter #',
@length_string_number AS '# of letters',
@dollar_difference AS 'max $ difference',
f.Name AS 'Table A Name',
f.total AS 'Table A Tot',
ct.Name AS 'Table B Name',
ct.Total AS 'Table B Tot'
FROM dbo.tableA f
CROSS JOIN
dbo.TableB ct
WHERE (ct.Name LIKE '%' + SUBSTRING(f.Name,
@start_string_number, @length_string_number) + '%')
AND
(f.Total BETWEEN ct.Total - @dollar_difference
AND ct.Total + @dollar_difference)
OR
(f.Total BETWEEN ct.Total - @dollar_difference
AND ct.Total + @dollar_difference)
AND
(f.Name LIKE '%' + SUBSTRING(ct.name,
@start_string_number, @length_string_number) + '%')
______________
....with params: @start_string_number = 1,
@length_string_number = 4,
@dollar_difference = 15
we'd get:

Tab A Name Tab A Tot Tab B Name Tab B Tot
---------- --------- ---------- ---------
Bradford 500 Bradley 489
Smithsin 2000 Smithson 2014

What I want is a "module" of the app where
1) the parameters are entered, or defaults are accepted,
2) the result set is brought up,
3) I use some mechanism to mark the sets that should
stay together, bringing all of the information from one
table into the other, or mark the record set to never be
matched again.

Thanks for any ideas you might have on how to begin.

Liz
 
Off hand, I think you are going to have to use some VBA. Hou could probably
set it up to use very minimal by using your first Stored procedure to
populate a "Temp" table that will by your forms data source that will be
editable. Then when your edits are complete (selections), you would click a
button that would run another stored procedure that would update your main
tables with your edits in your "Temp" table.

Just a thought.....
J. Clay
 
Back
Top