mysql重复数据只取一行数据,内联SQL语句只返回重复记录中的第一条记录

最新推荐文章于 2024-07-14 03:46:14 发布

转载最新推荐文章于 2024-07-14 03:46:14 发布 · 378 阅读

标签

在VB中，一个查询返回了预期的已终止员工记录数，但只显示了主表中的第一条匹配记录的重复项。问题在于代码没有正确遍历记录集。解决方案是使用循环来遍历记录集，确保每条记录都被处理。修正后的代码将遍历并打印所有匹配记录的详细信息。

I have a table named Employees with a field named EmployeeID. In a separate table, I have a separate master table of employees named Master with a field named EmployeeStatus. I am trying to validate all employees who have been terminated from the company are not listed in the Employees table.

However, current code I am using below is returning duplicate of the first record in the master table of employees. The value of record count property of the recordset object matches what I expect, the total number of terminated employees. rs.Fields(0) however only displays duplicate of the first matching record in the Master table. as seen from debug.print in the immediate window. I have already check for the following:

Trailing and leading spaces in field names

Proper quoting of strings

SQL and VBA syntax

How can I fix my code to display all matching records?

Public Function validEmployee(EmpID as String)

Dim dbs As DAO.database

Dim rs As DAO.recordset

Dim sqlString as String

set dbs = CurrentDb

sqlString = "SELECT [EmployeeID] FROM [MASTER] WHERE [EmployeeStatus] = 'Terminated'"

set rs = dbs.openrecordset(sqlString)

rs.moveLast

debug.print rs.recordcount

debug.print rs.fields(0)

解决方案

You want to loop through the recordset, something like this;

Public Function validEmployee(EmpID as String)

Dim dbs As DAO.database

Dim rs As DAO.recordset

Dim sqlString as String

set dbs = CurrentDb

sqlString = "SELECT [EmployeeID] FROM [MASTER] WHERE [EmployeeStatus] = 'Terminated'"

set rs = dbs.openrecordset(sqlString)

with rs

if .recordcount > 0 Then 'make sure the query returns records

.moveLast 'move last then back to first to make sure rs knows the record count

.movefirst

do until .eof 'loop through until the end of the recordset

debug.print rs.recordcount 'debug print our info

debug.print rs.fields(0)

loop

end if

end with

rs.close 'close off

set rs = nothing

Typed the above from aircode but it should put you on the right track. Your current code doesn't loop through the records, it is just debug printing the last records value of field 0.