How to limit a copy to one record; not the entire table

[EagleOne]

2003

I want to copy one record of TableA which meets conditionX to TableB

PLAN A

Originally I thought that the following VBA code would work:
....
....
Set rs = dBs.OpenRecordset("StarsRev", dbOpenDynaset)
....

Do Until ABSAmt <> Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
With appAccess.DoCmd
.RunCommand acCmdSelectRecord
.RunSQL "INSERT INTO STARSOffsets * SELECT * FROM STARSRev"
End With
End if
Loop

Unfortunately the code failed with the error: "SelectRecord is not available here"

PLAN B

When PLAN A failed, I altered the VBA code above to:

Do Until ABSAmt <> Abs(rs!Amt) Or rs.EOF
If ActualAmt + rs!Amt = 0 And (DovNmbr = rs!Dov_NUMBER Or DocNmbr = rs!Doc_NUMBER) Then
dBs.Execute "INSERT INTO STARSOffsets SELECT * FROM STARSRev"
End if
Loop

As I expected, all records in STARSRev are copied in to STARSOffsets using the VBA code in PLAN B

What VBA code can I use to limit the copy to the ONE record meeting the IF condition?

Any thoughts appreciated!

EagleOne

 

[Ken Sheridan]

Try this:

Dim dbs As DAO.Database
Dim strSQL As String

Set dbs = CurrentDb

strSQL = "INSERT INTO StarsOffsets" & _
" SELECT * FROM StarsRev" & _
" WHERE Amt + " & ActualAmt & " = 0" & _
" AND (Dov_NUMBER = " & DovNmbr & _
" OR Doc_NUMBER = " & DocNmbr & ")"

dbs.Execute strSQL

If its only possible for one row in StarsRev to meet this condition this
should work, but if more than one row can meet the condition you can select
one arbitrarily by using the SELECT TOP 1 option.

Ken Sheridan
Stafford, England

 

[EagleOne]

Thank you.

Unfortunately, there can be more than one "solution" (I am stuck with the data recorded)

"SELECT TOP 1" I have not used before. Does it possible mean (effectively or in fact) the current
record only? If true, this should work.

I have been attempting to "play" with rs.AbsolutePosition by attempting a WHERE clause that picks
only the record that has the "record number."

Bottom line, I want only the current record which meets the condition in the IF clause, as I toggle
through rs.

Thoughs?

 

[Ken Sheridan]

SELECT TOP n….. returns the first n rows from a query's result set, so TOP 1
returns the first row. Normally the query would have an ORDER BY clause to
sort the rows returned so the TOP rows would be first in the sort order, e.g.
you might want to return the three runners with the fastest times in a race:

SELECT TOP 3 Runner, RunTime
FROM Race
ORDER BY RunTime DESC;

Without an ORDER BY clause the 'first' row is an arbitrary one as tables are
sets, and concepts such as 'first' and 'last' are completely foreign to sets,
which have no intrinsic order.

As to whether it returns the 'current' record depends on what do you mean by
'current' record. When you say ' current record which meets the condition in
the IF clause, as I toggle through rs' this doesn't really make a lot of
sense. If many records can match the condition each of them will be the
'current record' at some time in the iteration through the recordset. So
what determines which of the many you want inserted into the other table?

If criteria can determine which record is the one you want as you iterate
through a recordset then the same criterion can determine which row a query
returns, so there is no point in using a recordset at all, just build the
WHERE clause of the 'append' query to return the row in question.

There is a more fundamental question of course. Why duplicate a row from
one table into another in the first place? Its an important principle of the
database relational model that each 'fact' is stored once and once only.

Ken Sheridan
Stafford, England

 

[EagleOne]

Since your 1st comment, I came to realize how significant that comment was/is. The last few months
represent my initial "trip" into the world of VBA Access programming. Therefore, issues that are
second-nature to many are not yet easily popping into my head.

You have quite an excellent track-record in the Access world. Thanks for sharing you expertise.

The reason for the copy is strictly due to the need to provide an "audit trail" documenting which
records were considered a match.

The audit-trail is mandated by the Sarbanes-Oxley law in the US.

 

[Douglas J. Steele]

Remember, though, that you have to be careful with SELECT TOP n. TOP n does
NOT necessarily only return the first n rows.

In your example, should the top runner have a RunTime of 1.03, two more
runners have times of 1.05 and three more runners have times of 1.06, your
query would actually return 6 rows, not 3.

 

[EagleOne]

Well I learned more concepts. I had no idea the conceptual depth of TOP and/or "top runner."

My interpretation, correct if appropriate, "TOP" is a metric that is an indication of a measurement
of the (run) time it took to "find" a record?

Also, that TOP 1 is not the TOP one-record-only but all records with a run time between 1 and 2 ?

EagleOne

 

[Douglas J. Steele]

I used "runner" because that's what Ken's example was. (presumably he was
storing the results of races in a table named Runners)

"Runner" has no relevance to Access, and no, "TOP" has nothing to do with
how long it took to find a record.

From the Help file:

TOP n [PERCENT]

Returns a certain number of records that fall at the top or the bottom of a
range specified by an ORDER BY clause. Suppose you want the names of the top
25 students from the class of 1994:

SELECT TOP 25
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage DESC;

If you don't include the ORDER BY clause, the query will return an arbitrary
set of 25 records from the Students table that satisfy the WHERE clause.The
TOP predicate doesn't choose between equal values. In the preceding example,
if the twenty-fifth and twenty-sixth highest grade point averages are the
same, the query will return 26 records.

You can also use the PERCENT reserved word to return a certain percentage of
records that fall at the top or the bottom of a range specified by an ORDER
BY clause. Suppose that, instead of the top 25 students, you want the bottom
10 percent of the class:

SELECT TOP 10 PERCENT
FirstName, LastName
FROM Students
WHERE GraduationYear = 1994
ORDER BY GradePointAverage ASC;

The ASC predicate specifies a return of bottom values. The value that
follows TOP must be an unsigned Integer.

TOP doesn't affect whether or not the query is updatable.

 

[EagleOne]

Boy! did I buy the farm!

Still learning though. Thanks for your time & knowledge.

 

[EagleOne]

What got me was Run time vs Runtime and the urge to over-think! Always a challenge; when to think;
when to go home.